Error when use vbs file to run Excel Macro

2
votes

I am trying to use the following vbs to run a Macro in the test.xlsm file. However, when I am running this, it throws an error indicating there is an error in line 9: This class does not support Automation "objWorkbook.Close".

I have checked the solutions online, for example, changing objWorkbook.Close False to objWorkbook.Close(False); changing the compatibility of Excel, however, none of those worked.

The opened Excel doesn't seem to be able to close...

Any idea how to fix it? It's really appreciated if it can be fixed!

Dim args, objExcel

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = 
objExcel.Workbooks.Open("C:\Users\Daisy\Desktop\VBA\test.xlsm")
objExcel.DisplayAlerts = False
objExcel.workbooks.Open "C:\Users\Daisy\Desktop\VBA\test.xlsm", 0, True
objExcel.visible = True
objExcel.Run "test.xlsm!ScheduleARunForTomorow" 
objWorkbook.Close False
objExcel.Application.Quit
WScript.Echo "Finished."
WScript.Quit
1
excelvbscriptvba
why are you opening the same workbook twice? – avb
I deleted the line objExcel.workbooks.Open "C:\Users\Daisy\Desktop\VBA\test.xlsm", 0, True, created test.xlsm with macro and it works for me - which version of office are you using? – avb
@avb YES you are right! I didn't notice that...I'm such a stupid lol. Thanks a lot for your helppp! – Daisy PENG
notice also that DisplayAlerts = False is rather dangerous, and here's no need to use it - in this case it probably did not let you see the real error message – avb

1 Answers

0
votes

After the objExcel.workbooks.Open command, you can use the Set objWorkbook = Activeworkbook to handle the opened workbook through the objWorkbook object