Getting error on closing excel macro using vbscript-CodePudding

Dim ObjExcel, ObjWB

Set ObjExcel = CreateObject("Excel.Application")
Set ObjWB = ObjExcel.Workbooks.Open("C: \Net_Zero_Final.xlsm",0,True)

ObjExcel.Visible = True

ObjExcel.Run "Net_Zero"

ObjExcel.application.quit ' <--- error here

Set ObjWB = Nothing

Set ObjExcel = Nothing

Getting error on the BOLD Part Can anyone please suggest. tried everything to fix this.

CodePudding user response：

I think you are trying to run a VBA Macro from a .vbs file (or any other location).

I tested the following code inside test.vbs.

Set ObjExcel = CreateObject("Excel.Application")

Set ObjWB = ObjExcel.Workbooks.Open("C:\Users\PC\Downloads\TestMacro2.xlsm", False, False)

ObjExcel.Visible = True

ObjExcel.Application.Run "TestMacro2.xlsm!TheMacro"

ObjWB.Close

Set ObjWB= Nothing

Set ObjExcel = Nothing

Where the macro inside the Excel file TestMacro2.xlsm was pretty simple

Sub TheMacro()
    MsgBox ("Hello")
End Sub

It worked without any problems.

Notice that instead of trying to close Excel application (ObjExcel.Application.Quit), I just close the opened Workbook (ObjWB.Close). This will avoid the script to close Excel while the user is working in another project.

CodePudding user response：

Dim ObjExcel, ObjWB

Set ObjExcel = CreateObject("Excel.Application") Set ObjWB = ObjExcel.Workbooks.Open("C: \Net_Zero_Final.xlsm",0,True)

ObjExcel.Visible = True

ObjExcel.Run "Net_Zero"

ObjExcel.DisplayAlerts = False

ObjExcel.quit ' <--- error here

Set ObjWB = Nothing

Set ObjExcel = Nothing

It worked like this, I just passed DisplayAlerts = False.

Thank you guys for the responses