We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Excel Automation From Access - Excel won't quit

Medium Priority
288 Views
Last Modified: 2012-05-11
I have an Access97 database (client won't upgrade to newer version), and we spin out an Excel document from within the Access code.  I output a query to excel, do a bunch of formatting, then set the visible property to true to display the completed spreadsheet.  The problem is that when a user exits out of Excel, Excel looks like it exits properly, but it keeps running in the task manager.  If they try to run the export process again, they get an RPC error, and unless they exit out of access (which causes excel to drop out of the task manager), and go back in, they can't run the report again.   Here is what my code looks like:

Function ExportFromAccess()
     
	 ' szFullTempPath is full path to spreadsheet
	 ' szQueryName is the query to export
	 
    With Application
        .Echo True

        DoCmd.OutputTo acOutputQuery, szQueryName, acFormatXLS, szFullTempPath, False
       
        Dim xlApp As Object 'Excel.Application
        Dim xlWB As Object 'Workbook
        Dim xlWS As Object 'Worksheet
        
        Set xlApp = CreateObject("Excel.Application")
        Set xlWB = xlApp.Workbooks.Open(szFullTempPath)
        Set xlWS = xlWB.Worksheets(szQueryName)
         
        xlApp.Range("A1").Select
        
		' bunch of formatting is done here
         
        xlApp.Visible = True
     
        Set xlWS = Nothing
        Set xlWB = Nothing
        Set xlApp = Nothing
         
        .Echo True
    End With
 
End Function

Open in new window


Any help is greatly appreciated!  Thanks!

Sincerely,
Matt Schwartz
Comment
Watch Question

Replace these lines

        Set xlWS = Nothing
        Set xlWB = Nothing
        Set xlApp = Nothing

With

        Set xlWS = Nothing
        Set xlWB = Nothing
        xlApp.Quit
        Set xlApp = Nothing

Sid

Author

Commented:
Thanks!  I tried that, but tha tends up exiting out of excel completely after the spreadsheet is created.  I want the spreadsheet to be displayed so the user can look at it, work with it, print it, etc.  Then when they exit excel manually, it should be dropped out of the task manager so they can export another spreadsheet.

Author

Commented:
Also, it seems like even using that it didn't clear Excel out of my task manager when Excel exited.
In that case comment the lines

        Set xlWS = Nothing
        Set xlWB = Nothing
        Set xlApp = Nothing

Sid
Before you this ensure that there is no excel in the task manager from your previous instances and then try the above.

Sid

Author

Commented:
Thanks again for all of your help.  I tried commenting those lines out, and excel came up with the spreadsheet as expected, but when I manually exited out of excel, it still continued running in the task manager.
CERTIFIED EXPERT
Most Valuable Expert 2014
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Thanks!  Stepping through helped me find it.  I left off an "xlapp." in front of one of my commands formatting the spreadsheet, which ended up creating a second instance.

Thanks for your help.

Author

Commented:
The suggestions here led me to the solution.  Thanks for your help!
I know you've already accepted an answer, which does, indeed, solve your problem, but I would like to suggest an alternative approach to your problem. If you find that you continue to have similar problems with Excel, you may find it useful:
I have found that when working with Excel from Access, it simplifies matters to scope the Excel application globally. I know this goes aginst the orthodoxy of scoping objects at the lowest level possible (i.e. within the procedure), but for my applications, I make an exception for the Excel application object.
Now, I know for certain this works with Access 2007, it may be that you will get different results with Access 97.
If you manage the Excel application object as illustrated below, what you will find is that if, after opening the workbook in Access, the client closes the Excel session, you will still see the Excel instance running in the task manager, but this is the one being managed by Access. if you go run another export, Access will simply use the Excel application object that has already been created.If you exit Access, the instance of Excel will be deleted from the stack.
I like to leave the Excel application visible and minimized while running any Excel procedures from within Access. This way, if something should go wrong, the client can still get at the instance of Excel. This guarantees you won't get any 'orphanned' instances of Excel sitting out on the stack.


Option Explicit
Option Compare Database
   
' Declare Excel application object for entire session
Public xlsApp As Excel.Application

Public Function StartExcel() As Boolean
    ' Application instance is defined globally.
    ' Only start this once for this session.
    ' End it when the session ends.
    If xlsApp Is Nothing Then
        Set xlsApp = New Excel.Application
        With xlsApp
             ' Start off visible and minimized
            .Visible = True
            .WindowState = xlMinimized
        End With    'With xlsApp
    End If          'If xlsApp Is Nothing
End Function

Function ExportFromAccess()
     
     ' szFullTempPath is full path to spreadsheet
     ' szQueryName is the query to export
     
With Application
    .Echo True

    DoCmd.OutputTo acOutputQuery, szQueryName, acFormatXLS, szFullTempPath, False
   
    Dim xlWB As Object 'Workbook
    Dim xlWS As Object 'Worksheet
    
    If StartExcel Then
        With xlsApp
            ' Start the workbook off visible but minimized. This way, if something
            ' goes wrong in the process, you can get at the excel application
            ' without having to delete the instance from the task manager
            .WindowState = xlMinimized
            .Visible = True
            
            Set xlWB = .Workbooks.Open(szFullTempPath)
            Set xlWS = xlWB.Worksheets(szQueryName)
         
            .Range("A1").Select
        
            ' bunch of formatting is done here
             
            
            ' maximize the workbook
            .WindowState = xlMaximized
            
        End With    'With xlsApp
    End If          'If StartExcel
    .Echo True
End With            'With Application
 
ExitFunction:
'force cleanup of excel objects
On Error Resume Next
Set xlWS = Nothing
Set xlWB = Nothing
'Always reset the Err object before exiting procedure
On Error GoTo 0
End Function

Open in new window

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.