How to call Excel VBA function from QTP
Parent page: Service Functions – Excel (QTP, VBScript)
Answers
Description
Connect to Excel through COM – Open workbook – Invoke the function – Catch error – close and save the workbook.
Notes.
Macro (Sub or Function) must be public and belong to a Macro Module.
Set XLHandle = CreateObject("Excel.Application")
XLHandle.DisplayAlerts = False
Set XLBook = XLHandle.WorkBooks.Open("c:\temp\1.xls")
On Error Resume Next
Call XLHandle.Run("TestFunction")
boolRC = Err.Number <> 0
On Error GoTo 0
If boolRC Then
Reporter.ReportEvent micFail, "Run Excel Macro", "Failed to call macro | Incorrect or not existing macro"
Else
Reporter.ReportEvent micDonel, "Run Excel Macro", "Successfully invoked macro"
End If
XLBook.Save
XLBook.Close
XLHandle.Quit
Set XLBook = Nothing
Set XLHandle = Nothing
