Unified Functional Testing Practitioners Forum

Excel Macro does not work on runtime

Go to solution
Honored Contributor.

Excel Macro does not work on runtime

Hi all,


I have my steps and my input data into an Excel file.


This has a Macro (GetDataFromClosedWorkbook) so whenever it opens it copies some cells from another Excel (being closed in the same directory).


I do this by executing:


Private Sub Workbook_Open()

Run "GetDataFromClosedWorkbook"

End Sub


When I execute my test it does not do that exactly even if manually it does.

I can see that because I have the cells in question empty durin runtime.


So my question is, is this due to the Macto being slower than the QTP loading the excel?


Any ideas?


Thanks in advance,



New Member.

Re: Excel Macro does not work on runtime

Hi csm,


1)  Please share me the function "GetDataFromClosedWorkbook" code

2) Also share me your QTP script which is calling the Excel, which in turn call and run this "GetDataFromClosedWorkbook" on its open.


It should run perfectly as you mentioned that you able to pull the record on manually opening the excel. No such sync problem between QTP and Excel macro.


I want to check how you passing the 2nd excel path from which you pulling the record.

Honored Contributor.

Re: Excel Macro does not work on runtime

I think I solved it by doing the following - architecture problem basically:




- Inside QTP / I call the excel macro before I use it in my TC


' Get data from output into our input excel
Dim objExcel

Set objExcel = CreateObject("Excel.Application")

objExcel.Application.Run "'C:\temp\Input.xls'!GetDataFromClosedWorkbook"

Set objExcel = nothing



- Inside the excel / I provide the funcion so to be called from QTP


Sub GetDataFromClosedWorkbook()
Dim wb As Workbook

' open the source workbook, read only
Set wb = Workbooks.Open("C:\temp\Ιnput.xls", True, True)

' read data from the source workbook and copy it here
With ThisWorkbook.Worksheets("Sheet1")
.Range("G2").Formula = wb.Worksheets("Sheet1").Range("D2").Formula
End With

' close the source workbook without saving any changes
wb.Close False
Set wb = Nothing

End Sub



New Member.

Re: Excel Macro does not work on runtime

Hi csm,

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

'Execute_VER_Export isthe macro name 
objExcel.Run "Execute_VER_Export"     

'after clicking the macro the dialog box should appear 
Window("UAT_VERReport").Dialog("Report finished").Activate
Window("UAT_VERReport").Dialog("Report finished").WinButton("OK").Click

Set objExcel = nothing


Application: a macro  based excels 

when the macro is run ,it gets executed in the macro based excel and the dialog box appears  .UFt shows still running macro  .