Vbscript compiling error when running VBA macro

Question

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

Too many quotes: objExcel.Application.Run "test.xlsm!copySchedule" — Rory
– Rory, Commented Mar 2, 2022 at 9:15
Read another question where someone said to put multiple quotes around the run string. But will try that. It fixed the compiling error, but the vbscript now fails in line 2 where it cant find the test.xlsm file, although the vbscript is in the same folder as the test.xlsm file. See updated question — CodingUser
– CodingUser, Commented Mar 2, 2022 at 9:17
You put multiple quotes inside a string to tell that you want a quote character in that string. — FunThomas
– FunThomas, Commented Mar 2, 2022 at 9:20

Rory · Accepted Answer · 2022-03-02 09:51:56Z

1

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'"

answered Mar 2, 2022 at 9:51

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user692942 Over a year ago

The correct answer, just a shame the OP edited their code in the question as it invalidates this answer. Have revised the question to bring it back in line with the original code that was posted.

Collectives™ on Stack Overflow

Vbscript compiling error when running VBA macro

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related