Solved

Excel won't quit from VBA in Access

Posted on 2006-07-17
6
363 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:Karen Schaefer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 17125449
Try this right before your exit code:

For Each xlWorkbook IN xlsApp.Workbooks
   xlw.Save
Next

xlsApp.Quit
0
 

Author Comment

by:Karen Schaefer
ID: 17125486
what is the dim xlw for?
K
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 17125487
(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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Karen Schaefer
ID: 17125491
it closes the wkbooks not the application though.
K
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 17125564
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
 

Author Comment

by:Karen Schaefer
ID: 17125679
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question