Shopify Question posted in
The Shopify help center can be found here.

objExcel.Quit doesn't work for quitting Excel – Shopify

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 DriveShopify"
    ActiveWorkbook.SaveAs Filename:= "C:Users" & UserName & _
        "Google DriveShopifyShopify.xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

    Workbooks("Shopify.xlsx").Close
End Sub
Tags:

2

Answers


  1. 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.

    Login or Signup to reply.
  2. 0

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

    Application.DisplayAlerts = False

ChDir "C:Users" & UserName & "Google DriveShopify"
ActiveWorkbook.SaveAs Filename:= _
    "C:Users" & UserName & "Google DriveShopifyShopify.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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search