Running a Macro from a VBS File Giving Error

Question

I am using a vbs code to open and run an Excel macro automatically but getting the following error message:

Error: Cannot run the Macro "file location". The macro may not be available in this workbook or all macros may be disabled. Code: 800A03EC Source: Microsoft Excel

VBS Code:

Dim ObjExcel, ObjWB
Set ObjExcel = CreateObject("Excel.Application")
ObjExcel.Visible = True


Set ObjWB = ObjExcel.Application.Run("C:\Automation\TestCasesBackupScript.xlsm!Backup3")


ObjWB.Close False
ObjExcel.Quit
Set ObjExcel = Nothing

Macro is enabled and the "Backup3" is the Sub name in a module of TestCasesBackupScript.xlsm file.

Hoping for feedback.

Thank you.

Answer 1

The workbook that you're referencing in Application.Run has to be open in the context of your Application object. Just open it first, then call it:

Dim ObjExcel, ObjWB
Set ObjExcel = CreateObject("Excel.Application")
ObjExcel.Visible = True

Set ObjWB = ObjExcel.Workbooks.Open("C:\Automation\TestCasesBackupScript.xlsm")
ObjWB.Application.Run("Backup3")

ObjWB.Close False
ObjExcel.Quit
Set ObjExcel = Nothing

Note: If Backup3 actually returns a Workbook object (which is the only reason to use the syntax Set ObjWB = ObjExcel.Application.Run, you'll want to capture that reference in its own variable.