none
Excel Process Left Over After Connection Closed From Access RRS feed

  • 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



    • Edited by RichLocus Thursday, September 7, 2017 6:05 PM
    Thursday, September 7, 2017 5:53 PM

Answers

  • 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



    Sunday, September 10, 2017 1:22 AM

All replies

  • 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)

    Thursday, September 7, 2017 7:02 PM
  • 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 MSDNFSF@microsoft.com.

    Friday, September 8, 2017 2:25 AM
    Moderator
  • 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

    Friday, September 8, 2017 11:07 PM
  • 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

    Friday, September 8, 2017 11:08 PM
  • 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


    Saturday, September 9, 2017 2:07 AM
  • 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)


    Saturday, September 9, 2017 2:10 AM
  • 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

    Saturday, September 9, 2017 2:20 AM
  • Daniel:

    I will check this on Monday.

    Thanks


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Saturday, September 9, 2017 5:18 AM
  • Paul:

    I will try this on Monday.

    Thanks


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Saturday, September 9, 2017 5:19 AM
  • 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

    Saturday, September 9, 2017 8:01 PM
  • 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

    Saturday, September 9, 2017 10:06 PM
  • 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



    Sunday, September 10, 2017 1:22 AM
  • 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

    Sunday, September 10, 2017 4:04 AM
  • 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

    Sunday, September 10, 2017 4:11 AM
  • 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

    Sunday, September 10, 2017 11:40 AM
  • 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


    • Edited by RichLocus Sunday, September 10, 2017 3:57 PM
    Sunday, September 10, 2017 3:45 PM
  • Hi Rich,

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

    Just my 2 cents...

    Sunday, September 10, 2017 9:27 PM
  • Thank you theDBGuy!!

    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Monday, September 11, 2017 3:27 PM
  • Hi Rich,

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

    Monday, September 11, 2017 3:30 PM