Excel won't quit from VBA in Access

I am trying to refresh Pivot tables from VBA in Access - This code works great except it won't close the Excel App window - it closes the workbook but not the app.  As you can see I am setting the XLS to quit - so why doesn't it?

Here is my current code:

Sub OpenXL_Pivot(pstrWorkbook As String)

    Dim xlWorkbook As Excel.Workbook, xlsApp As Excel.Application
    Dim xlPivotCache As Excel.PivotCache

On Error GoTo Error_Label
    ' get (or open) workbook
    Set xlWorkbook = GetObject(pstrWorkbook)
    ' refresh all pivot tables
    For Each xlPivotCache In xlWorkbook.PivotCaches
        xlPivotCache.Refresh
    Next xlPivotCache
   
Exit_Label:
    On Error Resume Next
    ' make sure everything is visible...
    xlWorkbook.Activate
    xlWorkbook.Windows(1).Visible = True
    xlWorkbook.Application.Visible = True
    xlWorkbook.Save
    xlWorkbook.Close True
    xlsApp.Quit
    Set xlPivotCache = Nothing
    Set xlWorkbook = Nothing
    Set xlsApp = Nothing

   Exit Sub
   
Error_Label:
    MsgBox Err.Description
    Resume Exit_Label

End Sub

Thanks,

Karen
Karen SchaeferBI ANALYSTAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
rockiroadsConnect With a Mentor Commented:
comment out  

On Error Resume Next

then try running it

it may be failing and by putting that statement, u will never know



Try an alternative approach

    Set xlsApp = CreateObject("Excel.Application")
    set xlWorkbook  = xlsApp.Workbooks.Open filename:=pstrWorkbook

then do your stuff, then to close

     xlWorkbook.Save
     xlWorkbook.Close
     xlsApp .Quit
     Set xlWorkbook = Nothing
     Set xlsApp = Nothing



0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Try this right before your exit code:

For Each xlWorkbook IN xlsApp.Workbooks
   xlw.Save
Next

xlsApp.Quit
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
what is the dim xlw for?
K
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
(Slight correction, had to copy-paste from existing code and I missed one...)

For Each xlWorkbook IN xlsApp.Workbooks
   xlWorkbook.Save
Next

xlsApp.Quit
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
it closes the wkbooks not the application though.
K
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
thanks that did the job.  I got rid of the

Exit_Label:
    On Error Resume Next
    ' make sure everything is visible...
    xlWorkbook.Activate
    xlWorkbook.Windows(1).Visible = True
    xlWorkbook.Application.Visible = True
    xlWorkbook.Save
    xlWorkbook.Close True
    xlsApp.Quit
    Set xlPivotCache = Nothing
    Set xlWorkbook = Nothing
    Set xlsApp = Nothing

   Exit Sub
portion of my code and it seems to be work great without actually opening the excel application.

Thanks,
Karen
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.