Re: Excel Macro does not work on runtime (323 Views)
Super Advisor
Posts: 216
Registered: ‎02-08-2013
Message 1 of 3 (354 Views)
Accepted Solution

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,



Occasional Visitor
Posts: 1
Registered: ‎02-24-2014
Message 2 of 3 (323 Views)

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.

Super Advisor
Posts: 216
Registered: ‎02-08-2013
Message 3 of 3 (309 Views)

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



The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation.