Excel instance will not close

  • Thread starter Thread starter RLN
  • Start date Start date
R

RLN

RE:Access 2003/Excel 2003

I have the following code that runs on the click event of one of my forms.
This code accepts an Excel file as a parm, opens an instance of Excel, does
a simple modification of column length, and saves the file.
The instance of Excel is closed and the Excel object is destroyed.

Problem: After this module runs, (& Excel instance is removed from the
taskbar), I go out to task manager, ...lo and behold an instance of Excel is
still in memory. I like to 'clean up' after working, so how can I get rid of
this instance of Excel that is still in memory?

Code follows:

<begin code>
Public Sub ExportCurrentData (strFile)
Dim objXL As Excel.Application
Set objXL = New Excel.Application
With objXL
'.Visible = False 'Use for Production
.Visible = True 'use this to see actual steps being processed in
XLS file
.Workbooks.Open strFile
'maximize all column lengths
.Columns("A:AG").EntireColumn.AutoFit
.Range("A1").Select 'place cursor in this cell before saving...
.ActiveWorkbook.Save
.Workbooks.close
.Quit
End With
Set objXL = Nothing 'destroy the object to remove from memory
End Sub
<end code>
 
It's working, but the syntax is actually not correct. Access is loosing
track of the reference to the Object you have established, so it is creating
one on its own. when you Quit the Excel Application, you are destroying the
one you created, but not the one Access created.

Here is a code snippet I use to open an Excel instance. Notice if first
looks for an instance that is running. If it finds one, it uses it;
otherwise it creates a new instance. Then when you are done, you only want
to quit the instance if it is the one you created; otherwise you may have a
very unhappy user who had an unsaved Excel spreadsheet open that you close
for them:

'Set up the necessary Excel Objcts
On Error Resume Next ' Defer error trapping.
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
Else
DetectExcel
End If
Err.Clear ' Clear Err object in case error occurred.
'Set error trapping back on
On Error GoTo Build_XL_Report_Error

And to Close it:

If blnExcelWasNotRunning = True Then
xlApp.Quit
End If
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

One other suggestion. Although
Dim objXL As Excel.Application

Provides you Intellisense when coding and is just a little bit faster
opening Excel, there is a serious downside.
What you are doing is called Early Binding. It causes your application to
bind to the specific version of Excel you have on your computer. If a user
opens your application and does not have the same version of Excel, you will
have some serious problems. I prefer to use Late Binding which doesn't bind
to the Excel model until Runtime. Now, what some people will do to get the
advantage of intellisense during coding is to user early binding in
development, then switch to Late Binding before deploying to production. To
use Late Binding, just change:

Dim objXL As Excel.Application

To
Dim objXL as Object.

I also suggest you establish references to your workbook and to your
worksheet. As written, your code is ambiguous and may also be the cause of
the extra instance of Excel. Here is an example:

Private xlApp As Object 'Application Object
Private xlBook As Object 'Workbook Object
Private xlSheet As Object 'Worksheet Object

Set xlBook = xlApp.Workbooks.Open(varGetFileName, 0, True)
Set xlSheet = xlBook.Worksheets("Actuals_res_export")
 
Back
Top