Re: Excel Macro does not work on runtime (173 Views)
Reply
Super Advisor
csm2mk
Posts: 216
Registered: ‎02-08-2013
Message 1 of 3 (204 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,

 

csm2mk

Please use plain text.
Occasional Visitor
Kalyan1985
Posts: 1
Registered: ‎02-24-2014
Message 2 of 3 (173 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.

Please use plain text.
Super Advisor
csm2mk
Posts: 216
Registered: ‎02-08-2013
Message 3 of 3 (159 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"
objExcel.Application.SaveWorkspace
objExcel.Application.Quit

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
 

 

 

Please use plain text.
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