Vbscript compiling error when running VBA macro-CodePudding

So my problem is that I can't get the VBScript to work. I want to automate this macro using the VBScript written in this post, which should execute the VBA code below. The error message I receive is in line 2, where the error is that the file couldn't be found. The VBScript and the test.xlsm file is in the same folder. How can I access the test.xlsm file without an error?

My VBA code so far:

Sub copySchedule()

    Worksheets("Sheet2").Range("B2:F10").Copy Worksheets("Sheet1").Range("B2:F10")
    ActiveWorkbook.Save

End Sub

My VBScript code so far:

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("test.xlsm")

objExcel.Application.Run ""test.xlsm!copySchedule""

objExcel.Application.Quit
WScript.Quit

CodePudding user response：

You only needed one pair of quotes round the macro name (if the workbook name contained spaces, you would need apostrophes round the name, and you can use them anyway if you like) and you should include the full file path when opening the workbook. So, for example:

Set objWorkbook = objExcel.Workbooks.Open("C:\some folder\test.xlsm")

objExcel.Application.Run "'test.xlsm!copySchedule'"