• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

Excel Automation From Access - Excel won't quit

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
0
NELIXINC
Asked:
NELIXINC
1 Solution
 
SiddharthRoutCommented:
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
0
 
NELIXINCAuthor 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.

0
 
NELIXINCAuthor Commented:
Also, it seems like even using that it didn't clear Excel out of my task manager when Excel exited.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
SiddharthRoutCommented:
In that case comment the lines

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

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

Sid
0
 
NELIXINCAuthor 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.
0
 
Nick67Commented:
Maybe this line starts one instance of Excel
DoCmd.OutputTo acOutputQuery, szQueryName, acFormatXLS, szFullTempPath, False

and this starts another

Dim xlApp As Object 'Excel.Application


Put some breakpoints in and see if more than one instance gets created!
Switch the order anyway, and watch Excel and the task manager as it goes down.
I don't see it, but you need it -- xApp.UserControl = True      
       
        Dim xlApp As Object 'Excel.Application
        xlApp.Visible = True
        xApp.UserControl = True
        DoCmd.OutputTo acOutputQuery, szQueryName, acFormatXLS, szFullTempPath, False
        Dim xlWB As Object 'Workbook
        Dim xlWS As Object 'Worksheet
0
 
NELIXINCAuthor 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.
0
 
NELIXINCAuthor Commented:
The suggestions here led me to the solution.  Thanks for your help!
0
 
shambaladCommented:
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

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now