Excel still running after quit command

[Duong Tran via AccessMonster.com]

Hi All,

This problem is similar to the one Brian posted, see http://www.accessmonster.com/Uwe/Forum.aspx/access-externaldata/2381/Excel-Still-running

However, I've tried the suggestion without success. Please can someone tell me what I'm doing wrong with the following code segment that keep leaving Excel running in memory.

BTW: I'm using Access & Excel 2000 on an XP home OS.
I've also tried objExcel.Worksheets(strSheet).Activate

Thanks in advance,
Duong.

-------------------------- codes start

Dim msExcel As Excel.Application

If AppIsActive("Excel") Then
Set objExcel = GetObject(Class:="Excel.Application")
Else
Set objExcel = CreateObject(Class:="Excel.Application")
End If

objExcel.Visible = False
objExcel.Workbooks.Open strSource
objExcel.Worksheets(strSheet).Select
DoCmd.TransferSpreadsheet acImport, 8, strTable, strSource, True, ""

Do While objExcel.Workbooks.Count > 0
objExcel.Workbooks(1).Close False
Loop

If boolXL Then objExcel.Quit

Set objExcel = Nothing

-------------------------- codes end

 

[Ken Snell [MVP]]

I don't see any place in your code where you explicitly set the variable
boolXL to any value. If you're not, then its default value will be False.
And as such you'll never close EXCEL in the second to last code step.

 

[Duong Tran via AccessMonster.com]

Sorry the edited code segment contained a typo...should be:

Thanks again.
-------------------------- codes start

Dim msExcel As Excel.Application

If AppIsActive("Excel") Then
Set objExcel = GetObject(Class:="Excel.Application")
boolXL = False
Else
Set objExcel = CreateObject(Class:="Excel.Application")
boolXL = True
End If

objExcel.Visible = False
objExcel.Workbooks.Open strSource
objExcel.Worksheets(strSheet).Select
DoCmd.TransferSpreadsheet acImport, 8, strTable, strSource, True, ""

Do While objExcel.Workbooks.Count > 0
objExcel.Workbooks(1).Close False
Loop

If boolXL Then objExcel.Quit

Set objExcel = Nothing

-------------------------- codes end

 

[Ken Snell [MVP]]

Your code will not close EXCEL if it was already open when your code began.
Worse yet, your code makes that already running session of EXCEL hidden, so
that you don't see that your code didn't close it.

Thus, you find this "EXCEL session" running when your code is done because
it did what you told it to do when EXCEL was already running: namely, hide
it and then leave it running.

 

[Duong Tran via AccessMonster.com]

Thanks for that advice Ken.

So if excel was not running then it should close down without leaving it in memory? However, during testing, with Excel not running, an instance of Excel is still in memory after running the code.

Is this correct or have I done something wrong?

Thanks,
Duong.

 

[Ken Snell [MVP]]

Based on what you've posted, then yes, I would not expect to find EXCEL
running after your code finishes, if EXCEL was opened by your code.

Further review of your code shows that you have this line at the beginning:

Dim msExcel As Excel.Application

But you never use this object in your code; instead, you're using objExcel.

Are you quite sure that you don't have more code running somewhere that is
using msExcel?

Also, I don't understand why you're opening EXCEL just to use
TransferSpreadsheet. You can use TransferSpreadsheet, *and* specify the
worksheet to be imported, without opening EXCEL at all. Where you have "" in
the last argument for the TransferSpreadsheet action, replace that with the
text string that identifies the worksheet name:

Dim strRange As String
strRange = IIf(InStr(strSheet, " ") > 0, "'", "")
DoCmd.TransferSpreadsheet acImport, 8, strTable, strSource, True, _
strRange & strSheet & strRange & "!"

I have added the strRange line in order to delimit the sheet name with '
characters if the worksheet name has a space in it.