VBA Excel just won't quit

Question

The issue that I'm having is that the document will at times import just fine, and at other times it won't. From what it looks like, Excel is not always closing. Can anyone suggest a way to make sure that the instance of Excel that is opened is closed? Or point me to a good reference for this? I'm using this VBA in MS Access. Below is my code:

Public Function ImportPayment()
Dim fd As FileDialog
Dim wb As Workbook
Dim ws As String

Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
    .AllowMultiSelect = False
    .Title = "Please select a file."
    .Show
    On Error GoTo ErrorHandler:
        ws = fd.SelectedItems(1)
        Workbooks.Open (ws)
        Rows("1:9").Select
        Selection.Delete Shift:=xlUp
        Cells(Rows.count, "L").End(xlUp).EntireRow.Delete
        Excel.Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs FileName:= _
        "\\servername\serverfolder\serversubfolder\subfolder\sub\file.txt",
        FileFormat:=xlText _ , CreateBackup:=False
        End With

    ActiveWorkbook.Close
    Excel.Application.DisplayAlerts = True
    Excel.Application.Quit

DoCmd.TransferText acImport, "Specification", "table",
"\\server\serverfolder\serversubfolder\subfolder\sub\file.txt", False
MsgBox ("Your data was imported successfully.")
ImportPayment = Yep
Exit Function

ErrorHandler:
    ImportPayment = Nope
    MsgBox ("The upload was canceled.")

End Function

Since I know that the file is always going to be an Excel file, would it be ideal to create an Excel object before the file picker? I'm just trying to think some ideas through here. I've been using VBA for about 4 months now, so I'm still pretty inexperienced in this. — mbnew
– mbnew, Commented Oct 20, 2013 at 15:39
Yes it is: keep control of your Excel object by instantiating an Excel Application object, and adding workbook objects within this application object. — Trace
– Trace, Commented Oct 20, 2013 at 15:42

Jean-François Corbett · Accepted Answer · 2013-10-20 19:36:30Z

1

Try this way:

Sub create_excel_instance()

Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Set oExcel = CreateObject("excel.application")

oExcel.Visible = True
set oBook = oExcel.Workbooks.Add

'Close
oBook.close
set oBook = nothing 
oExcel.Quit 

End Sub

Note: instead of using ActiveWorkbook, you'll notice that this piece of code sets a reference to each object. This is good practice; it will save you many bugs and keep a good overview of your created objects.

edited Oct 20, 2013 at 19:36

Jean-François Corbett

38.7k30 gold badges146 silver badges192 bronze badges

answered Oct 20, 2013 at 15:38

Trace

18.9k21 gold badges98 silver badges174 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mbnew Over a year ago

Thanks for the advice Kim! I've tried adding this in, however I'm getting an error that the MS Access database engine cannot access the file as it is currently in use. I'm using the file locally, so I'm not sure where this is coming from. Any thoughts?

Trace Over a year ago

Are you using the code that I posted? If so, on what line do you get the error?

mbnew Over a year ago

Hey Kim, your code worked wonderfully. Unfortunately, I was having issues with VPN and went in the next day and it worked flawlessly. Thanks for the help!

JasonR Over a year ago

This is going to seem odd, but for me I always have to put that line twice back to back: Application.Quit Application.Quit Doing this closes Excel.

Collectives™ on Stack Overflow

VBA Excel just won't quit

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related