objExcel.Quit doesn't work for quitting Excel

0
votes

I'm trying a VBScript that should open Excel, open a workbook, run a macro and then close everything.

Dim objExcel, xlBook

Set objExcel = CreateObject("Excel.Application")
Set xlBook = objExcel.Workbooks.Open ("path to the xlsm file")

objExcel.Visible = True

objExcel.Run xlBook.name & "!Modulo1.Macro1"
WScript.Sleep 20000

objExcel.Quit

Everything works fine, Excel opens the .xlsm file and runs the macro, but then after closing the workbook (this is included in the macro) Excel remains opened.

It seems objExcel.Quit doesn't work correctly.

I'm working with Excel 2016 in Windows 7.

These are the last lines of the macro:

    Application.DisplayAlerts = False

    ChDir "C:\Users\" & UserName & "\Google Drive\Shopify"
    ActiveWorkbook.SaveAs Filename:= "C:\Users\" & UserName & _
        "\Google Drive\Shopify\Shopify.xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

    Workbooks("Shopify.xlsx").Close
End Sub
2
vbaexcelvbscript
Please post the VBA macro code. What is installed Excel version and bitness? Check MCVE. – omegastripes
Are you talking about closing the FIRST Excel, meaning the one that contains the macro? – FDavidov
I'm talking about closing the Excel Application (the workbook is already closed by the macro) – kiltro
Possible duplicate of Closing Excel Application using VBA – Regis Desrosiers
Trying changing the value of Application.DisplayAlerts = False to True - Excel might be popping up a message to save your changes or displaying an error dialog box – dbmitch

2 Answers

1
votes

I'd suggest to remove the close, i.e.

Application.DisplayAlerts = False

ChDir "C:\Users\" & UserName & "\Google Drive\Shopify"
ActiveWorkbook.SaveAs Filename:= _
    "C:\Users\" & UserName & "\Google Drive\Shopify\Shopify.xlsx", FileFormat:= _
    xlOpenXMLWorkbook, CreateBackup:=False
' As you are doing a saveas the workbook Shopify.xlsx becomes the active one
' Closing it the vb script menas you lose the connection you opened via
' objExcel.Run xlBook.name & "!Modulo1.Macro1"
'Workbooks("Shopify.xlsx").Close

End Sub
0
votes

I'm going to go out on a limb here and bet that Excel is closing.

You're just not waiting the full 20 seconds that you're telling Excel to wait. Perhaps you intended a 2 second pause (2000 ms).

I'm not sure if the timer is serving another purpose, but remove that line and try again. You'll notice your window close as expected.

One of the first steps of troubleshooting is to break the code into smaller pieces, taking parts out one by one until it becomes clear which command or section is the culprit.

There is some excellent advice on debugging VBA located here, from the legendary Chip Pearson.