CODEWITHSUNDEEP
excelvba
mbnew
mbnew

Reputation: 39

VBA Excel just won't quit

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

Upvotes: 0

Views: 2173

Answers (1)

Trace
Trace

Reputation: 18859

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.

Upvotes: 1

Related Questions