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