0

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
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
kiltro
  • 57
  • 3
  • Please post the VBA macro code. What is installed Excel version and bitness? Check [MCVE](https://stackoverflow.com/help/mcve). – omegastripes May 05 '18 at 07:30
  • Are you talking about closing the FIRST Excel, meaning the one that contains the macro? – FDavidov May 05 '18 at 07:36
  • I'm talking about closing the Excel Application (the workbook is already closed by the macro) – kiltro May 05 '18 at 08:27
  • Possible duplicate of [Closing Excel Application using VBA](https://stackoverflow.com/questions/3628252/closing-excel-application-using-vba) – Regis Desrosiers May 05 '18 at 12:54
  • 1
    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 May 05 '18 at 16:43

2 Answers2

1

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
Storax
  • 11,158
  • 3
  • 16
  • 33
0

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.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
ashleedawg
  • 20,365
  • 9
  • 72
  • 105
  • No, I've waited... also I've tried removing that line Excel remains open – kiltro May 05 '18 at 08:44
  • I waited and it closed just fine. However the other thing I did was remove the line that calls the macro, since I don't have the macro. – ashleedawg May 05 '18 at 12:08