VBA Code Not Closing Excel Application

[JohanGduToit]

Afternoon Experts,

I have an MS Access application that reformat an Excel spreadsheet that are imported into a MS Access table once reformatting has been completed. The only reformatting that takes place is a deletion of a worksheet named "Sum" and then deleting all 'empty' rows from the last cell in Column "A" that contains data to the end of the file. The code executes without any error; but the Excel Application remains open afterwards. So if I run the code again to format a 2nd Excel file, the program crash on line

.Application.Rows(Range("A" & .Rows.Count).End(xlUp).Offset(1).Row & ":" & .Rows.Count).Delete

For some reason Excel do not close once code has been executed.

Please assist? I think this may have something to do with late/early binding; but I am not sure though.

Thank you.

Public Sub FormatPargoInvoiceFile(sFile, sSheet As String)

'On Error GoTo Err_FormatPargoInvoiceFile

    Const xlUp As Long = -4162
    
    Dim xlApp As Object
    Dim xlSheet As Object
    
    Application.SetOption "Show Status Bar", True
    vStatusBar = SysCmd(acSysCmdSetStatus, "Formatting & Processing Pargo Invoice File... Please wait.")
    
    Set xlApp = CreateObject("Excel.Application")
    Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)
        
    With xlApp
        .Application.DisplayAlerts = False
        .Application.Sheets("Sum").Delete
        .Application.DisplayAlerts = True
        .Application.Sheets(sSheet).Select
        .Application.Rows(Range("A" & .Rows.Count).End(xlUp).Offset(1).Row & ":" & .Rows.Count).Delete
        .Application.Range("A1").Select
        .Application.ActiveWorkbook.Save
        .Application.ActiveWorkbook.Close
        .Application.Quit
    End With
    
    vStatusBar = SysCmd(acSysCmdClearStatus)
    
    Set xlSheet = Nothing
    Set xlApp = Nothing
    
'Exit_FormatPargoInvoiceFile:
'    Exit Sub
    
'Err_FormatPargoInvoiceFile:
'    vStatusBar = SysCmd(acSysCmdClearStatus)
'    MsgBox Err.Number & " - " & Err.Description
'    Set xlSheet = Nothing
'    Set xlApp = Nothing
'    Resume Exit_FormatPargoInvoiceFile
    
End Sub

 

[JohanGduToit]

Too late to edit now. Forgot to say I think the solution to the problem is to not close the workbook, just save then quit. However, I'd still edit the code along the lines of what I mentioned previously.

I removed the 'Close' statement as part of the solution, thank you.

 

[Micron]

Take those comments with a grain of salt because I'm much more experienced in Access vba and consider myself a novice when it comes to Excel. I've since looked at the application object in the documentation and am amazed at how much is in there and how much duplication there is. Considering just "Range", it is an Application object property that returns a range object, and at the same time it is a Worksheet property that returns an object. If there are more instances of the range object I didn't see any. I find it too confusing. Anyway, my comment re: referencing at the sheet level was based on the fact that in all the code I've ever seen up to now the range reference followed a worksheet reference. In response to your question of how I might have written it I'll post what I did to only test your Quit problem but you'll also be able to see how I referred to the range via the worksheet (ws). Not that I think this is better, just different. IIRC, I didn't use xlApp.Quit since I set it to Nothing before Quit, thinking it was better to terminate all object variables before invoking quit. Obviously I had to make a couple of changes as well as comment out lines.

Sub test2()
Dim xlApp As Object, wb As Object, ws As Object

Set xlApp = CreateObject("Excel.Application")
Set wb = ThisWorkbook
Set ws = ThisWorkbook.Sheets("10")

xlApp.DisplayAlerts = False
'wb.Sheets("Sum").Delete

With ws
     .Select
    '.Rows(Range("A" & .Rows.count).End(xlUp).Offset(1).Row & ":" & .Rows.count).Delete
    .Range("A1").Select
End With

wb.Save
xlApp.DisplayAlerts = True

Set wb = Nothing
Set ws = Nothing
Set xlApp = Nothing
Application.Quit

End Sub

 

[JohanGduToit]

Take those comments with a grain of salt because I'm much more experienced in Access vba and consider myself a novice when it comes to Excel. I've since looked at the application object in the documentation and am amazed at how much is in there and how much duplication there is. Considering just "Range", it is an Application object property that returns a range object, and at the same time it is a Worksheet property that returns an object. If there are more instances of the range object I didn't see any. I find it too confusing. Anyway, my comment re: referencing at the sheet level was based on the fact that in all the code I've ever seen up to now the range reference followed a worksheet reference. In response to your question of how I might have written it I'll post what I did to only test your Quit problem but you'll also be able to see how I referred to the range via the worksheet (ws). Not that I think this is better, just different. IIRC, I didn't use xlApp.Quit since I set it to Nothing before Quit, thinking it was better to terminate all object variables before invoking quit. Obviously I had to make a couple of changes as well as comment out lines.

Sub test2()
Dim xlApp As Object, wb As Object, ws As Object

Set xlApp = CreateObject("Excel.Application")
Set wb = ThisWorkbook
Set ws = ThisWorkbook.Sheets("10")

xlApp.DisplayAlerts = False
'wb.Sheets("Sum").Delete

With ws
     .Select
    '.Rows(Range("A" & .Rows.count).End(xlUp).Offset(1).Row & ":" & .Rows.count).Delete
    .Range("A1").Select
End With

wb.Save
xlApp.DisplayAlerts = True

Set wb = Nothing
Set ws = Nothing
Set xlApp = Nothing
Application.Quit

End Sub

Thank you for the detailed feedback; again appreciated... however a bit confusing for me (not being an expert at all when it comes to Excel objects, etc.) I'm just happy that the current code accomplish the requirements, so I'll let it be as is. Not going to scratch where it doesn't itch

Again, you're input appreciated as always.