Unified Functional Testing (formerly QTP) News and Support Forum
Showing results for 
Search instead for 
Do you mean 

Excel Macro does not work on runtime

SOLVED
Go to Solution
Super Advisor

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

1 ACCEPTED SOLUTIONS
Super Advisor

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
 

 

 

3 REPLIES
Highlighted
Occasional Visitor

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

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
 

 

 

Occasional Visitor

Re: Excel Macro does not work on runtime

Hi csm,

Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open("C:\<filename>.xlsm")
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  .