Run an Excel macro from QuickTest Professional

Changes in Office 2007 could affect macros and other application code written for earlier versions of the Office suite. Office 2007 changes the user interface, security, and other features, and these changes could require tweaking of macros or other customizations in order to migrate them to Office 2007.

The basic format for this would look something like the following:

   ExcelObj.Run "<MacroName>"

If the macro or function in Excel takes arguments, you can pass optional parameters to the Run method to correspond with those parameters. See the MSDN link for further details on the format of the Run method.

Example 1:
set obj = CreateObject("Excel.Application")
obj.Workbooks.Open "C:\macrotry.xls"

' Call the run command to execute a macro or function in Excel
obj.Run "Sample"

obj.ActiveWorkbook.save
obj.Quit
set obj = Nothing

For further information on the Excel Object Model, refer to http://msdn2.microsoft.com/en-us/library/Aa220733.

Another alternative is to use the Visible property to bring up the actual Excel UI, then use Low Level Recording to press the control keyboard sequence that has been specified to start the macro. For the following example, CTRL-L has been specified as the control sequence to start the macro that was used. Low Level Recording was used to record this operation.

Example 2:
set obj = CreateObject("Excel.Application")
obj.Workbooks.Open "C:\macrotry.xls"

' Make the Excel UI visible
obj.Visible = TRUE

' Using Low Level Recording, activate the window and press the keyboard
' sequence you have specified for the macro. In this example, we used
' the keyboard sequence Ctrl-L to activate the macro
Window("macrotry.xls").Activate
Window("macrotry.xls").WinObject("macrotry.xls").Type micCtrlDwn + "l" + micCtrlUp

obj.ActiveWorkbook.save
obj.Quit
set obj = Nothing