Excel Process Left Over After Connection Closed From Access

Question

Hello:

I am using Access 2016 to populate an Excel 2016  workbook.  It works fine the first time I run it, but an Excel Process remains stuck.  I can clear the process by closing Access, so it appears that Access isn't letting go of the Excel connection completely.

The actual Excel application is closed, but the process remains, and can cause unexpected issues.

Here's a snippet of the code.  Any suggestions?  I can shut down Access every time I run this report, but that's not an optimal solution.

UPDATED INFORMATION:  This leftover process only occurs when I save the Excel file.  If I populate it, print it, and don't save it, the leftover process is not created.

VBA Snippet:
Private Sub cmdTop3ProdsByCustomer_Click()
' **************************************************************
' Establish Database Query Connection
' **************************************************************
Dim db As DAO.Database
Dim recIn As DAO.Recordset

' **************************************************************
' Establish Excel Communications from Access
' **************************************************************
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlShSales As Excel.Worksheet
Dim xlShPivot As Excel.Worksheet

' **************************************************************
' Open the Sales Compare Template
' **************************************************************
Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Open("C:\Users\rlocus\Documents\Templates\Top3ProductsByCustomerTemplate.xlsx")
Set xlShSales = xlWB.Sheets("SalesHistoryDetail3Years")
Set xlShPivot = xlWB.Sheets("SalesPivot")
' xlApp.Visible = True

' EXCEL IS POPULATED HERE.... CODE OMITTED

xlWB.SaveAs FileName:=strSaveAsName, FileFormat:=xlOpenXMLWorkbook

' ********************************************************
' Message the user and display the location of the
' saved report
' ********************************************************
MsgBox ("The Report Was Saved to " & strSaveAsName)

' ***********************************************************************
' Close The Workbook and the database
' ***********************************************************************
xlApp.DisplayAlerts = False
xlWB.Close SaveChanges:=False
xlApp.DisplayAlerts = True
xlApp.Quit
Set xlApp = Nothing
Set xlWB = Nothing
Set xlShSales = Nothing
Set xlShPivot = Nothing
recIn.Close
Set recIn = Nothing
Set db = Nothing
End Sub

Thanks in advance.

---

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com

Answer 1

When you say "stuck" I'm assuming that it is remaining in memory? When this occurs it's generally because an internal object was created that has no associated variable reference that can be destroyed. As long as the object instance remains the application will not terminate on its own.

I don't see anything obvious in the code you posted, so it could very well be code that you omitted. Perhaps there is a internal reference to a Range object in your code somewhere that has not been set to a variable and so cannot be set to Nothing.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 2

Hi RichLocus,

I try to test your above posted code on my side.

I find that Excel instance created when you create object of Excel and when you close the excel and destroy the object.

the Excel process in Task manager also get removed from the list.

so on my side it is working as expected and there is no any object of Excel remains open in Task manager after closing it from code.

it is possible that may be there is some issue with the code that you did not posted where you process the Excel data.

so if possible for you then you can also try to post it , we will try to make a test with it to check whether we can reproduce the issue.

you can also try to separately test the above code on your side and check the result and let us know about that.

also if possible then you can try to post your database with dummy data in it so we can also test that on our side and check the result.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 3

Hello Paul and Deepak:

I have included all the code for the procedure that leaves an Excel process still running.  Here it is:

Private Sub cmdTop3ProdsByCustomer_Click()
' **************************************************************
' Establish Database Query Connection
' **************************************************************
Dim db As DAO.Database
Dim recIn As DAO.Recordset

' **************************************************************
' Establish Excel Communications from Access
' **************************************************************
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlShSales As Excel.Worksheet
Dim xlShPivot As Excel.Worksheet

' **************************************************************
' Other Variables
' **************************************************************
Dim lngNumberOfRows As Long
Dim strRowsToInsert As String
Dim lngLastRowInTable As Long
Dim lngFillRow As Long
Dim strSaveAsName As String
Dim strSavePath As String

strSavePath = "C:\Users\rlocus\Documents\SavedReports\"
' strSavePath = "\\MPI-SERVER2\Users$\rlocus\Documents\SavedReports\"

' **************************************************************
' Create Sales History Table for 3 Years
' **************************************************************
Call CreateDetailFor3Years

' **************************************************************
' Verify That The Sales Compare Template Exists
' **************************************************************
If Not FileExists("C:\Users\rlocus\Documents\Templates\Top3ProductsByCustomerTemplate.xlsx") Then
    MsgBox ("Template C:\Users\rlocus\Documents\Templates\Top3ProductsByCustomerTemplate.xlsx Does Not Exist")
    Exit Sub
End If

' ***********************************************************************
' Open the Top 3 Products Sold To Each Customer
' ***********************************************************************
Set db = CurrentDb()
Set recIn = db.OpenRecordset("qryList3YearsTopProducts")

If recIn.EOF Then
    recIn.Close
    Set recIn = Nothing
    Set db = Nothing
    Exit Sub
End If

' ***********************************************************************
' Count the rows that need to be inserted into the Template
' ***********************************************************************
recIn.MoveLast
lngNumberOfRows = recIn.RecordCount
recIn.MoveFirst

' **************************************************************
' Open the Sales Compare Template
' **************************************************************
Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Open("C:\Users\rlocus\Documents\Templates\Top3ProductsByCustomerTemplate.xlsx")
Set xlShSales = xlWB.Sheets("SalesHistoryDetail3Years")
Set xlShPivot = xlWB.Sheets("SalesPivot")
' xlApp.Visible = True

' **************************************************************
' Open up space in the Excel Workbook for inserting all the
' rows from the 3 year top seller table
' **************************************************************
strRowsToInsert = "3:" & lngNumberOfRows + 2
xlShSales.Rows(strRowsToInsert).Insert Shift:=xlDown

lngFillRow = 2

' ***************************************************************
' Loop through all the query records and insert them one row
' at a time into the template
' ***************************************************************
Do
lngFillRow = lngFillRow + 1
If lngFillRow > lngNumberOfRows + 2 Then
    MsgBox ("Row Number " & lngFillRow & " Exceeds Space Allocation")
    recIn.Close
    Set recIn = Nothing
    Set db = Nothing
    xlApp.DisplayAlerts = False
    xlWB.Close SaveChanges:=False
    xlApp.DisplayAlerts = True
    xlApp.Quit
    Set xlApp = Nothing
    Exit Sub
End If
' ***************************************************************
' Poulate the workbook with sales dollars and returns
' ***************************************************************
xlShSales.Cells(lngFillRow, "A") = recIn!Name
xlShSales.Cells(lngFillRow, "B") = recIn!TransYear
xlShSales.Cells(lngFillRow, "C") = recIn!SumOfSaleAmt
xlShSales.Cells(lngFillRow, "D") = recIn!Description
xlShSales.Cells(lngFillRow, "E") = recIn!Item
xlShSales.Cells(lngFillRow, "F") = recIn!Customer

NextRecord:
recIn.MoveNext
Loop Until recIn.EOF

' ********************************************************
' Determine the last row of the updated table
' ********************************************************
lngLastRowInTable = xlShSales.Cells(Rows.Count, "A").End(xlUp).Row

' ********************************************************
' Delete the last and first setup rows
' ********************************************************
xlShSales.Rows(lngLastRowInTable).EntireRow.Delete Shift:=xlUp
xlShSales.Rows(2).EntireRow.Delete Shift:=xlUp

' ********************************************************
' Update the Pivot Table
' ********************************************************
xlShPivot.PivotTables("3YearPivot").PivotCache.Refresh

' ********************************************************
' Save the File With A Unique Date and Time Stamp
' ********************************************************
strSaveAsName = strSavePath & "Top3ProductsByCustomer_" & Format(Date, "YYYY_MM_DD") & "_" & Format(Hour(Now), "00") & Format(Minute(Now), "00")
xlWB.SaveAs FileName:=strSaveAsName, FileFormat:=xlOpenXMLWorkbook

' ********************************************************
' Message the user and display the location of the
' saved report
' ********************************************************
MsgBox ("The Report Was Saved to " & strSaveAsName)

' ***********************************************************************
' Close The Workbook and the database
' ***********************************************************************
xlApp.DisplayAlerts = False
xlWB.Close SaveChanges:=False
xlApp.DisplayAlerts = True
xlApp.Quit
Set xlWB = Nothing
Set xlShSales = Nothing
Set xlShPivot = Nothing
Set xlApp = Nothing
recIn.Close
Set recIn = Nothing
Set db = Nothing
MsgBox ("Closing Access - Please Restart")
Access.Quit
End Sub

---

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com

Answer 4

Deepak:

I posted the entire procedure.  I didn't find any objects I didn't destroy.  Maybe you can see if I missed something.

Thanks,

Rich Locus

---

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com

Answer 5

I see you setting a whole series of objects, but you don't always clean up after them.  For instance, in the

' ***************************************************************
' Loop through all the query records and insert them one row
' at a time into the template
' ***************************************************************
Do
lngFillRow = lngFillRow + 1
If lngFillRow > lngNumberOfRows + 2 Then
    MsgBox ("Row Number " & lngFillRow & " Exceeds Space Allocation")
    recIn.Close
    Set recIn = Nothing
    Set db = Nothing
    xlApp.DisplayAlerts = False
    xlWB.Close SaveChanges:=False
    xlApp.DisplayAlerts = True
    xlApp.Quit
    Set xlApp = Nothing
    Exit Sub
End If

you don't cleanup xlShPivot, ...

Also I see Call CreateDetailFor3Years, what is that exactly?

---

Daniel Pineault, 2010-2017 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 6

Usually if you have more than one dot in your reference then an new object instance is being created. For example, you very likely created a PivotTable and PivotCache object instance in the below reference:

xlShPivot.PivotTables("3YearPivot").PivotCache.Refresh

I would set them to object variables first before calling the Refresh method of PivotCache, and then set it to Nothing when you de-reference the other object variables.

Set xlPivotTable = xlShPivot.PivotTables("3YearPivot")
Set xlPivotCache = xlPivotTable.PivotCache
xlPivotCache.Refresh
'...
Set xlPivotCache = Nothing
Set xlPivotTable = Nothing

You might need to do that will your Cell references as well, but I would try one thing at a time to see if it eliminates the issue.

 

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 7

I'd probably try something more along the lines of

Private Sub cmdTop3ProdsByCustomer_Click()
    On Error GoTo Error_Handler
    ' **************************************************************
    ' Establish Database Query Connection
    ' **************************************************************
    Dim db                    As DAO.Database
    Dim recIn                 As DAO.Recordset

    ' **************************************************************
    ' Establish Excel Communications from Access
    ' **************************************************************
    Dim xlApp                 As Excel.Application
    Dim xlWB                  As Excel.Workbook
    Dim xlShSales             As Excel.Worksheet
    Dim xlShPivot             As Excel.Worksheet

    ' **************************************************************
    ' Other Variables
    ' **************************************************************
    Dim lngNumberOfRows       As Long
    Dim strRowsToInsert       As String
    Dim lngLastRowInTable     As Long
    Dim lngFillRow            As Long
    Dim strSaveAsName         As String
    Dim strSavePath           As String

    strSavePath = "C:\Users\rlocus\Documents\SavedReports\"
    ' strSavePath = "\\MPI-SERVER2\Users$\rlocus\Documents\SavedReports\"

    ' **************************************************************
    ' Create Sales History Table for 3 Years
    ' **************************************************************
    Call CreateDetailFor3Years

    ' **************************************************************
    ' Verify That The Sales Compare Template Exists
    ' **************************************************************
    If Not FileExists("C:\Users\rlocus\Documents\Templates\Top3ProductsByCustomerTemplate.xlsx") Then
        MsgBox ("Template C:\Users\rlocus\Documents\Templates\Top3ProductsByCustomerTemplate.xlsx Does Not Exist")
        GoTo Error_Handler_Exit
    End If

    ' ***********************************************************************
    ' Open the Top 3 Products Sold To Each Customer
    ' ***********************************************************************
    Set db = CurrentDb()
    Set recIn = db.OpenRecordset("qryList3YearsTopProducts", dbOpenSnapshot)

    If recIn.RecordCount = 0 Then
        GoTo Error_Handler_Exit
    End If

    ' ***********************************************************************
    ' Count the rows that need to be inserted into the Template
    ' ***********************************************************************
    recIn.MoveLast
    lngNumberOfRows = recIn.RecordCount
    recIn.MoveFirst

    ' **************************************************************
    ' Open the Sales Compare Template
    ' **************************************************************
    Set xlApp = CreateObject("Excel.Application") 'New Excel.Application
    Set xlWB = xlApp.Workbooks.Open("C:\Users\rlocus\Documents\Templates\Top3ProductsByCustomerTemplate.xlsx")
    Set xlShSales = xlWB.Sheets("SalesHistoryDetail3Years")
    Set xlShPivot = xlWB.Sheets("SalesPivot")
    ' xlApp.Visible = True

    ' **************************************************************
    ' Open up space in the Excel Workbook for inserting all the
    ' rows from the 3 year top seller table
    ' **************************************************************
    strRowsToInsert = "3:" & lngNumberOfRows + 2
    xlShSales.Rows(strRowsToInsert).Insert Shift:=xlDown

    lngFillRow = 2

    ' ***************************************************************
    ' Loop through all the query records and insert them one row
    ' at a time into the template
    ' ***************************************************************
    Do
        lngFillRow = lngFillRow + 1
        If lngFillRow > lngNumberOfRows + 2 Then
            MsgBox ("Row Number " & lngFillRow & " Exceeds Space Allocation")
            GoTo Error_Handler_Exit
        End If
        ' ***************************************************************
        ' Poulate the workbook with sales dollars and returns
        ' ***************************************************************
        xlShSales.Cells(lngFillRow, "A") = recIn!Name
        xlShSales.Cells(lngFillRow, "B") = recIn!TransYear
        xlShSales.Cells(lngFillRow, "C") = recIn!SumOfSaleAmt
        xlShSales.Cells(lngFillRow, "D") = recIn!Description
        xlShSales.Cells(lngFillRow, "E") = recIn!item
        xlShSales.Cells(lngFillRow, "F") = recIn!Customer
        recIn.MoveNext
    Loop Until recIn.EOF

    ' ********************************************************
    ' Determine the last row of the updated table
    ' ********************************************************
    lngLastRowInTable = xlShSales.Cells(Rows.Count, "A").end(xlUp).Row

    ' ********************************************************
    ' Delete the last and first setup rows
    ' ********************************************************
    xlShSales.Rows(lngLastRowInTable).EntireRow.Delete Shift:=xlUp
    xlShSales.Rows(2).EntireRow.Delete Shift:=xlUp

    ' ********************************************************
    ' Update the Pivot Table
    ' ********************************************************
    xlShPivot.PivotTables("3YearPivot").PivotCache.Refresh

    ' ********************************************************
    ' Save the File With A Unique Date and Time Stamp
    ' ********************************************************
    strSaveAsName = strSavePath & "Top3ProductsByCustomer_" & Format(Date, "YYYY_MM_DD") & "_" & Format(Hour(Now), "00") & Format(Minute(Now), "00")
    xlWB.SaveAs FileName:=strSaveAsName, FileFormat:=xlOpenXMLWorkbook

    ' ********************************************************
    ' Message the user and display the location of the
    ' saved report
    ' ********************************************************
    MsgBox ("The Report Was Saved to " & strSaveAsName)

Error_Handler_Exit:
    On Error Resume Next
    If Not xlWB Is Nothing Then
        xlApp.DisplayAlerts = False
        xlWB.Close SaveChanges:=False
        xlApp.DisplayAlerts = True
        Set xlWB = Nothing
    End If
    If Not xlShSales Is Nothing Then xlShSales db = Nothing
    If Not xlShPivot Is Nothing Then Set xlShPivot = Nothing
    If Not xlApp Is Nothing Then
        xlApp.Quit
        Set xlApp = Nothing
    End If
    If Not recIn Is Nothing Then
        recIn.Close
        Set recIn = Nothing
    End If
    If Not db Is Nothing Then Set db = Nothing
    MsgBox ("Closing Access - Please Restart")
    Access.Quit
    Exit Sub

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: " & sModName & "\cmdTop3ProdsByCustomer_Click" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

That said, the one thing that I specifically would try if I were you would be to replace

Set xlApp = New Excel.Application

with

Set xlApp = CreateObject("Excel.Application")

for some reason I seem to remember a discussion in which the New ... cause problems, but I don't remember the details.  All I can say is I always use the CreateObject approach (mainly because I prefer Late Binding, but regardless ...) and it always works!  Never, ever, had any issues with it.

---

Daniel Pineault, 2010-2017 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 8

Daniel:

I will check this on Monday.

Thanks

---

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com

Answer 9

Paul:

I will try this on Monday.

Thanks

---

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com

Answer 10

Hello Deepak, Paul and Daniel:

Thank you all for contributing your time to my lingering Excel process issue.  I incorporated ALL of your suggestions in the code that follows, but still that pesky Excel process still remains.  The only way I can clear it is to get into the task manager and cancel it, or just shut down Access.

Unless you have other suggestions, the only way I can have you see the issue is to post the actual Access database and the Excel template.  I would have to remove lots of data, because it is a large DB, and I would need to change the dollar amounts.  As part of my testing, I removed the "Save As" code, and also the Pivot refresh.  Still the error persists.  Strange.

Attached is the code that includes all your suggestions.

Private Sub cmdTop3ProdsByCustomer_Click()
    On Error GoTo Error_Handler
    ' **************************************************************
    ' Establish Database Query Connection
    ' **************************************************************
    Dim db                    As DAO.Database
    Dim recIn                 As DAO.Recordset

    ' **************************************************************
    ' Establish Excel Communications from Access
    ' **************************************************************
    Dim xlApp                 As Excel.Application
    Dim xlWB                  As Excel.Workbook
    Dim xlShSales             As Excel.Worksheet
    Dim xlShPivot             As Excel.Worksheet
    Dim xlPivotTable          As PivotTable
    Dim xlPivotCache          As PivotCache
    
    ' **************************************************************
    ' Other Variables
    ' **************************************************************
    Dim lngNumberOfRows       As Long
    Dim strRowsToInsert       As String
    Dim lngLastRowInTable     As Long
    Dim lngFillRow            As Long
    Dim strSaveAsName         As String
    Dim strSavePath           As String

    strSavePath = "C:\Users\richl_000.VOSWINDOWS8\Documents\SavedReports\"
    ' strSavePath = "\\MPI-SERVER2\Users$\rlocus\Documents\SavedReports\"

    ' **************************************************************
    ' Create Sales History Table for 3 Years
    ' **************************************************************
    Call CreateDetailFor3Years

    ' **************************************************************
    ' Verify That The Sales Compare Template Exists
    ' **************************************************************
    If Not FileExists("C:\Users\richl_000.VOSWINDOWS8\Documents\Templates\Top3ProductsByCustomerTemplate.xlsx") Then
        MsgBox ("Template C:\Users\richl_000.VOSWINDOWS8\Documents\Templates\Top3ProductsByCustomerTemplate.xlsx Does Not Exist")
        Exit Sub
    End If

    ' ***********************************************************************
    ' Open the Top 3 Products Sold To Each Customer
    ' ***********************************************************************
    Set db = CurrentDb()
    Set recIn = db.OpenRecordset("qryList3YearsTopProducts", dbOpenSnapshot)

    If recIn.RecordCount = 0 Then
        GoTo Error_Handler_Exit
    End If

    ' ***********************************************************************
    ' Count the rows that need to be inserted into the Template
    ' ***********************************************************************
    recIn.MoveLast
    lngNumberOfRows = recIn.RecordCount
    recIn.MoveFirst

    ' **************************************************************
    ' Open the Sales Compare Template
    ' **************************************************************
    Set xlApp = CreateObject("Excel.Application") 'New Excel.Application
    Set xlWB = xlApp.Workbooks.Open("C:\Users\richl_000.VOSWINDOWS8\Documents\Templates\Top3ProductsByCustomerTemplate.xlsx")
    Set xlShSales = xlWB.Sheets("SalesHistoryDetail3Years")
    Set xlShPivot = xlWB.Sheets("SalesPivot")
    ' xlApp.Visible = True

    ' **************************************************************
    ' Open up space in the Excel Workbook for inserting all the
    ' rows from the 3 year top seller table
    ' **************************************************************
    strRowsToInsert = "3:" & lngNumberOfRows + 2
    xlShSales.Rows(strRowsToInsert).Insert Shift:=xlDown

    lngFillRow = 2

    ' ***************************************************************
    ' Loop through all the query records and insert them one row
    ' at a time into the template
    ' ***************************************************************
    Do
        lngFillRow = lngFillRow + 1
        If lngFillRow > lngNumberOfRows + 2 Then
            MsgBox ("Row Number " & lngFillRow & " Exceeds Space Allocation")
            GoTo Error_Handler_Exit
        End If
        ' ***************************************************************
        ' Poulate the workbook with sales dollars and returns
        ' ***************************************************************
        xlShSales.Cells(lngFillRow, "A") = recIn!Name
        xlShSales.Cells(lngFillRow, "B") = recIn!TransYear
        xlShSales.Cells(lngFillRow, "C") = recIn!SumOfSaleAmt
        xlShSales.Cells(lngFillRow, "D") = recIn!Description
        xlShSales.Cells(lngFillRow, "E") = recIn!Item
        xlShSales.Cells(lngFillRow, "F") = recIn!Customer
        recIn.MoveNext
    Loop Until recIn.EOF

    ' ********************************************************
    ' Determine the last row of the updated table
    ' ********************************************************
    lngLastRowInTable = xlShSales.Cells(Rows.Count, "A").End(xlUp).Row

    ' ********************************************************
    ' Delete the last and first setup rows
    ' ********************************************************
    xlShSales.Rows(lngLastRowInTable).EntireRow.Delete Shift:=xlUp
    xlShSales.Rows(2).EntireRow.Delete Shift:=xlUp

    ' ********************************************************
    ' Update the Pivot Table
    ' ********************************************************
    Set xlPivotTable = xlShPivot.PivotTables("3YearPivot")
    Set xlPivotCache = xlPivotTable.PivotCache
    xlPivotCache.Refresh

    'xlShPivot.PivotTables("3YearPivot").PivotCache.Refresh

    ' ********************************************************
    ' Save the File With A Unique Date and Time Stamp
    ' ********************************************************
    strSaveAsName = strSavePath & "Top3ProductsByCustomer_" & Format(Date, "YYYY_MM_DD") & "_" & Format(Hour(Now), "00") & Format(Minute(Now), "00")
    xlWB.SaveAs FileName:=strSaveAsName, FileFormat:=xlOpenXMLWorkbook

    ' ********************************************************
    ' Message the user and display the location of the
    ' saved report
    ' ********************************************************
    MsgBox ("The Report Was Saved to " & strSaveAsName)

Error_Handler_Exit:
    On Error Resume Next
    If Not xlWB Is Nothing Then
        xlApp.DisplayAlerts = False
        xlWB.Close SaveChanges:=False
        xlApp.DisplayAlerts = True
        Set xlWB = Nothing
    End If
    If Not xlShSales Is Nothing Then Set xlShSales = Nothing
    If Not xlShPivot Is Nothing Then Set xlShPivot = Nothing
    If Not xlPivotTable Is Nothing Then Set xlPivotTable = Nothing
    If Not xlPivotCache Is Nothing Then Set xlPivotCache = Nothing
    If Not xlApp Is Nothing Then
        xlApp.Quit
        Set xlApp = Nothing
    End If
    If Not recIn Is Nothing Then
        recIn.Close
        Set recIn = Nothing
    End If
    If Not db Is Nothing Then Set db = Nothing
    ' MsgBox ("Closing Access - Please Restart")
    ' Access.Quit
    Exit Sub

Error_Handler:
    MsgBox "An Error Occurred In This Application" & vbCrLf & _
       "Please Contact The Developer" & vbCrLf & vbCrLf & _
       "Error Number = " & Err.Number & "  Error Description = " & _
        Err.Description, vbCritical
        
    Resume Error_Handler_Exit
End Sub

Thanks again,

---

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com

Answer 11

Hello Deepak, Paul and Daniel:

After banging my head on my keyboard for hours, I could not see anything wrong with the code.  So, I am providing a small extract of the data, with the names and dollars changed.

To run this, the instructions are on the form that comes up when you open the application.  You will need to create two directories and change three lines of VBA code to point to the correct location of the output and template input directories.  I made it as simple for you as possible.  This may be a bug???

I didn't see a place to attach a zipped file, so here's a link to download it:

https://1drv.ms/f/s!AnBQdsOMXpKKokJqS0t0-NlQ3eNF

Thanks,

---

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com

Answer 12

Sorry I didn't sit the problem earlier, but with the sample and the error handle I provided you earlier I was able to quickly identify the line causing the issue, so try to change your line

lngLastRowInTable = xlShSales.Cells(Rows.Count, "A").End(xlUp).Row 

To

lngLastRowInTable = xlShSales.Cells(xlShSales.Rows.Count, "A").End(xlUp).Row

No more problem.

---

Daniel Pineault, 2010-2017 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 13

Daniel:

Wow!  That came out of nowhere.  I know that if the ROWS command is not qualified, it must have defaulted to a different worksheet.  Your solution did fix the problem.  Perhaps without qualification the code created an issue for Excel that prevented it from going away?  Anyway, thanks a million!!

I'm not sure how you tracked that down, because even without qualification, it still came up with the correct count for me, but on an intermittent basis. I didn't have error trapping before you provided it.  I just ran it without qualification, and it worked.  I ran it again, and it failed with the error trap.  So it wasn't consistent.  STRANGE!!

Now I can get a good nights sleep instead of the "toss and turn" scenario :)

Regards,

---

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com

Answer 14

Daniel:

Thanks to you finding the smoking gun in my code, I am going to have to change my Website page that deals with the last row or column:

http://www.logicwurks.com/CodeExamplePages/EFindLastRowOrColumn.html

Also, I will have to put in an addendum on my Amazon book, "Power Up Using Excel Ranges".

I appreciate you pointing out that error.

---

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com

Answer 15

with my error hanlder

    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: cmdTop3ProdsByCustomer_Click" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"

I added line numbers to your procedure and ran it twice.  The first time, as you say, no issue, but the second time it raised and error and reported back Line 390 which corresponded to the line I flagged.  Then it was easy to correct.

That's why I added the Switch(Erl... to my error handler, it truly adds a new level of reporting assuming you add the line numbers.

---

Daniel Pineault, 2010-2017 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 16

Hello again Daniel:

Do you have a recommendation on what to use to add line numbers to my code?

Thanks

---

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com

Answer 17

Hi Rich,

Pardon me for jumping in but some VBA add-ins like MZ-Tools has this feature.

Just my 2 cents...

Answer 18

Thank you theDBGuy!!

Regards,

---

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com

Answer 19

Hi Rich,

You're welcome. We're happy to assist. Good luck with your project.