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

 

[Anthony47]

Try using

.Quit

(instead of .Application.Quit)

 

[RoryA]

You missed a dot in front of Range:

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

 

[Micron]

If xlApp is the application object you don't need Application as in
.Application.DisplayAlerts, so?

With xlApp
__.DisplayAlerts = False

Would it not be better to reference sheets at the workbook level rather than the application level? Reduces the chance that someday that reference will result in the wrong workbook being affected? Consider me an Excel vba novice.

 

[Micron]

.Application.DisplayAlerts, so?

was supposed to be .Application.DisplayAlerts, no?
In other words, it's a question. Sorry for how that might have come across otherwise.

 

[Micron]

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.

 

[JohanGduToit]

Try using

.Quit

(instead of .Application.Quit)

Hi Anthony,

Applied your suggestion; forms part of final working code (see below).

Thank you!

 

[JohanGduToit]

You missed a dot in front of Range:

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

Hi Rory,

That was it!

Added the "." before "Range" ...solved!

Please see final working code below.

Thank you!

 

[JohanGduToit]

If xlApp is the application object you don't need Application as in
.Application.DisplayAlerts, so?

With xlApp
__.DisplayAlerts = False

Would it not be better to reference sheets at the workbook level rather than the application level? Reduces the chance that someday that reference will result in the wrong workbook being affected? Consider me an Excel vba novice.

Hi Micron,

Applied your suggestion and removed ".Application" from various lines.

See working code below...

PS : Not sure what you mean by referencing sheets at the workbook level rather than the application level?

If you do have time, please elaborate and maybe amend my code accordingly?

Thank you once again.

 

[JohanGduToit]

Final working code...

Hats off to all who replied; much appreciated:

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
        .DisplayAlerts = False
        .Sheets("Sum").Delete
        .DisplayAlerts = True
        .Sheets(sSheet).Select
        .Rows(.Range("A" & .Rows.Count).End(xlUp).Offset(1).Row & ":" & .Rows.Count).Delete
        .Range("A1").Select
        .ActiveWorkbook.Save
        .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