Excel Automation creates a hidden instance of Excel using the Append Method

[ScardyBob]

Hello All!

I've run into an annoying problem automating Excel from Access. My
program creates an instance of Excel where users can insert specified
data. Once there finished they can push a button on the Excel worksheet
that imports the data back into Access into the appropriate tables. The
importing process consists of first creating a linked table to the
Excel worksheet and then appending this data to the correct tables.
However, this creates a hidden instance of Excel running in Task
Manager that I have to close manually. I've narrowed down the problem
to the Append method in the following code:

Const ExcelFile As String = "Data"

FileLoc = CurrentProject.Path & "\"

'Create a new Excel Worksheet and make it the visible worksheet
Set xlb = GetObject(FileLoc & ExcelFile & ".xls")
Set xlx = xlb.Parent
Set xlbs = xlx.Workbooks

'Close the open excel workbook
If xlbs.Count = 1 Then
xlb.Close
xlx.Quit
Else
xlb.Close
End If

'Create a new table based on the data located in Excel spreadsheet
Set tdf = db.CreateTableDef(ExcelFile)
strConnect = "Excel 5.0;HDR=YES;IMEX=2;DATABASE=" & FileLoc & ExcelFile
& ".xls"
tdf.Connect = strConnect
tdf.SourceTableName = "Sheet1$"
db.TableDefs.Append tdf <--------- Causes hidden instance of Excel

If I put the xlb.Close and xlx.Quit after the db.TableDefs.Append tdf
then it creates a hidden instance of Excel. Why does it do this?

Thanks in advance!
Mike

 

[Guest]

The problem is how you are manipulating your Excel objects. I see some
problems there. First, you don't close Excel, you Close open workbooks. You
also Quit the application object, not it's parent.

 

[ScardyBob]

Klatuu,

Isn't the parent of an open workbook the application object? I tried to
reference the application object as the parent because I wanted to
close only the specific instance of Excel I had opened for
manipulation. I first tried Set xlx = GetObject(, "Microsoft Excel")
which does reference an open instance of Excel, but I couldn't figure
out how to get the specific one I needed. This is a problem since it is
very likely that the user will either have other Excel workbooks opened
either before or after the automated one is created. Is there a better
way to do this?

Thanks!
Mike

 

[Guest]

Here is what I have put together based on a lot of experience with an
environment similar to yours. My users would like to be able to open Excel
without having to boot their computers . Almost all my reports are done in
Excel.

This first part is what I use to get or create an instance of Excel.

Private xlApp As Object 'Application Object
Private xlBook As Object 'Workbook Object
Private xlSheet As Object 'Worksheet Object
Private blnExcelWasNotRunning As Boolean 'Used to determine whether to
close or quit

'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
DoEvents
xlApp.DisplayAlerts = False
xlApp.Interactive = False
xlApp.ScreenUpdating = False
Set xlBook = xlApp.Workbooks.Add
--------------------------------------------------------
Here is what I use for cleaning up. Be sure you include the code to release
all your references to Excel in your error handling or you can leave behind
an instance of Excel if your code encounters an error.
xlBook.Close
If blnExcelWasNotRunning = True Then
xlApp.Quit
Else
xlApp.DisplayAlerts = True
xlApp.Interactive = True
xlApp.ScreenUpdating = True
End If
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

---------------------------------------------
This is the sub called during the opening part. I didn't write this part, I
got it from VBA Help (I think)

Sub DetectExcel()
' Procedure dectects a running Excel and registers it.
Const WM_USER = 1024
Dim hWnd As Long
' If Excel is running this API call returns its handle.
hWnd = FindWindow("XLMAIN", 0)
If hWnd = 0 Then ' 0 means Excel not running.
Exit Sub
Else
' Excel is running so use the SendMessage API
' function to enter it in the Running Object Table.
SendMessage hWnd, WM_USER + 18, 0, 0
End If
End Sub

 

[ScardyBob]

I'll try it out!

Thanks for all the help!

Mike

Here is what I have put together based on a lot of experience with an
environment similar to yours. My users would like to be able to open Excel
without having to boot their computers . Almost all my reports are done in
Excel.

This first part is what I use to get or create an instance of Excel.

Private xlApp As Object 'Application Object
Private xlBook As Object 'Workbook Object
Private xlSheet As Object 'Worksheet Object
Private blnExcelWasNotRunning As Boolean 'Used to determine whether to
close or quit

'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
DoEvents
xlApp.DisplayAlerts = False
xlApp.Interactive = False
xlApp.ScreenUpdating = False
Set xlBook = xlApp.Workbooks.Add
--------------------------------------------------------
Here is what I use for cleaning up. Be sure you include the code to release
all your references to Excel in your error handling or you can leave behind
an instance of Excel if your code encounters an error.
xlBook.Close
If blnExcelWasNotRunning = True Then
xlApp.Quit
Else
xlApp.DisplayAlerts = True
xlApp.Interactive = True
xlApp.ScreenUpdating = True
End If
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

---------------------------------------------
This is the sub called during the opening part. I didn't write this part, I
got it from VBA Help (I think)

Sub DetectExcel()
' Procedure dectects a running Excel and registers it.
Const WM_USER = 1024
Dim hWnd As Long
' If Excel is running this API call returns its handle.
hWnd = FindWindow("XLMAIN", 0)
If hWnd = 0 Then ' 0 means Excel not running.
Exit Sub
Else
' Excel is running so use the SendMessage API
' function to enter it in the Running Object Table.
SendMessage hWnd, WM_USER + 18, 0, 0
End If
End Sub