VBA Code Not Closing Excel Application

JohanGduToit

Board Regular
Joined
Nov 12, 2021
Messages
89
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
VBA Code:
.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.

VBA Code:
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
 
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.
 
Upvote 0

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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.
VBA Code:
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
 
Upvote 0
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.
VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top