Can't close excel from Access

[Mikey B]

I am importing multiple Excel 2003 worksheets into my Access 2003 database
with VBA. I want most of the sheets imported in but not the Sheet named
"System" which is always the last tab.
My problem is when I am done I cannot seem to completely close out Excel.
I still see it in the Task Manager.

The first time i run the routine it leaves one instance of Excel when it is
done.
The second time I run the routine it opens a second instance of Excel but
then closes it leaving a bigger first instance of Excel.
The third time I run the routine it locks up Access completely.

I have been trying the ideas I have found on this site and I expect I am not
properly closing out my objects, but have not been able to make anything
work. I am sure it is something obvious I am overlooking, but my eyes are
glazing over and I could use some help.

I am a self taught coder and any help will be greatly appreciated.
this is my code:

Dim objExcel As Object 'Excel.Application
Dim objWorkbook As Object 'Excel.Workbook
Dim objWorksheet As Object 'Worksheet
Dim strFileName As String
Dim strWorksheetName As String

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
strFileName = "C\Test.xls"
Set objWorkbook = objExcel.Workbooks.Open(strFileName)
For Each objWorksheet In objWorkbook.Worksheets
strWorksheetName = objWorksheet.Name
If strWorksheetName = "System" Then
Set objWorksheet = Nothing
objExcel.Workbooks(1).CLose False, , False
Set objWorkbook = Nothing
objExcel.Quit
Set objAccess = Nothing
MsgBox ("Done!")
Exit Sub
End If
DoCmd.TransferSpreadsheet acImport, 8, "tblImportedData",
strFileName, False, strWorksheetName & "!A2:AQ"
Me.Requery
Next

 

[pietlinden]

this doesn't look right.

If strWorksheetName = "System" Then
Set objWorksheet = Nothing
objExcel.Workbooks(1).CLose False, , False

you need to close the worksheets/workbook first, and then set the
references to nothing. (basically, close and dereference in reverse
order of opening/setting.) Then you should free all the file handles,
and Excel should close.

 

[Mikey B]

ok, I moved the Set objWorksheet and also changed the line:
'Set objAccess = Nothing' to 'Set objExcel = Nothing'.

I have also been trying different syntaxs for closing the workbook but am
still leaving a small Excel instance open. (smaller than when the routine
was running).

Am I not closing the sheets out fully? There are 4 that I am transfering
but 8 total in workbook.

Also, I do not know what you mean when you say "free all file handles."

here is the code as of now:
Dim objExcel As Object 'Excel.Application
Dim objWorkbook As Object 'Excel.Workbook
Dim objWorksheet As Object 'Worksheet
Dim strFileName As String
Dim strWorksheetName As String


Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
strFileName = "C\test.xls"
Set objWorkbook = objExcel.Workbooks.Open(strFileName)
For Each objWorksheet In objWorkbook.Worksheets
strWorksheetName = objWorksheet.Name
If strWorksheetName = "System" Then
objWorkbook.CLose
Set objWorksheet = Nothing
Set objWorkbook = Nothing
objExcel.Quit
Set objExcel = Nothing
MsgBox ("Done!")
Exit Sub
End If
DoCmd.TransferSpreadsheet acImport, 8, "tblImportedData",
strFileName, False, strWorksheetName & "!A2:AQ"
Me.Requery
Next

 

[Ken Snell \(MVP\)]

You're setting the Worksheet object to Nothing after it's already "out of
scope" because you closed the workbook first. It's always best if you set an
object to Nothing before you "close" its parent object and/or set its parent
object to Nothing.

Try this code:

Dim objExcel As Object 'Excel.Application
Dim objWorkbook As Object 'Excel.Workbook
Dim objWorksheet As Object 'Worksheet
Dim strFileName As String
Dim strWorksheetName As String


Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
strFileName = "C\test.xls"
Set objWorkbook = objExcel.Workbooks.Open(strFileName)
For Each objWorksheet In objWorkbook.Worksheets
strWorksheetName = objWorksheet.Name
If strWorksheetName = "System" Then
' set this object to Nothing before you close the workbook
Set objWorksheet = Nothing
objWorkbook.CLose
Set objWorkbook = Nothing
objExcel.Quit
Set objExcel = Nothing
MsgBox ("Done!")
Exit Sub
End If
DoCmd.TransferSpreadsheet acImport, 8, "tblImportedData",
strFileName, False, strWorksheetName & "!A2:AQ"
Me.Requery
Next

 

[Mikey B]

Thanks for your help Ken. The suggestion you had actually reflects what I
thought it should be and had origionally before the previous poster suggested
i change it. But I am happy that my thinking was heading the right way.
However, even after closing the sheet before setting workbook to nothing, it
still left an instance of Excel.( I replicated your example exactly)

I am becoming convinced that it is the workbook that is hanging me up but am
unsure how to resolve it.
I am using For Each...Next to loop thru the first 4 of 8 sheets of my
workbook stopping at "System"(always 4th from last sheet). (in production
there will be 45 sheets to loop thru)
If I remove the workbook object and not loop at all but transfer each sheet
one by one, the routine close excel nicely.
I suspect that my Workbook.close is not properly closing because of the way
For Each...Next is using its index to call the sheets. But it is just a
guess and one I don't know how to fix. Any suggestions would be greatly
appreciated.

:most recent code:
Dim obExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objWorksheet As Excel.Worksheet
Dim strWorksheetName As String
Dim strFileName As String
strFileName = "C\test.xls"

Set obExcel = CreateObject("Excel.Application")
obExcel.Visible = False
Set objWorkbook = obExcel.Workbooks.Open(strFileName)
For Each objWorksheet In objWorkbook.Worksheets
strWorksheetName = objWorksheet.Name
If strWorksheetName = "System Then
Set objWorksheet = Nothing
objWorkbook.CLose
Set objWorkbook = Nothing
obExcel.Quit
Set obExcel = Nothing
MsgBox ("Done!")
Exit Sub
End If
DoCmd.TransferSpreadsheet acImport, 8, "tblImportedData",
strFileName, False, strWorksheetName & "!A2:AQ"
Me.Requery
Next

 

[Ken Snell \(MVP\)]

Ok. The problem then may be with your closing the workbook inside the For
Each ... Next loop. (You've probably realized by now that eliminating these
"ghost" instances can be a bit of trial and error -- and frustration). So
let's try this code:


Dim obExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objWorksheet As Excel.Worksheet
Dim strWorksheetName As String
Dim strFileName As String
strFileName = "C\test.xls"

Set obExcel = CreateObject("Excel.Application")
obExcel.Visible = False
Set objWorkbook = obExcel.Workbooks.Open(strFileName)
For Each objWorksheet In objWorkbook.Worksheets
strWorksheetName = objWorksheet.Name
If strWorksheetName = "System" Then Exit For
DoCmd.TransferSpreadsheet acImport, 8, "tblImportedData",
strFileName, False, strWorksheetName & "!A2:AQ"
Me.Requery
Next
Set objWorksheet = Nothing
objWorkbook.CLose
Set objWorkbook = Nothing
obExcel.Quit
Set obExcel = Nothing
MsgBox ("Done!")

 

[Mikey B]

I made your changes but it did not close out the instance of Excel.
I like the more elegant way of leaving the For loop. I did not know you
could just Exit it like that. In one early variation I had a GoTo to exit
but I dislike using Goto so changed it.

I also tried creating another For Each...Loop with the "Set objWorksheet =
Nothing" in it right before closing the Workbook. It however had issues with
not referencing the object properly so I abandoned it.

I have looked at your replies to other posters and see quite often that you
help them identify their problems on the first try. I am sorry this is
turning into such a pain but feel better about myself that it was not just a
simple fix too.

Any other ideas? I am going to take a break from it for the night and try
with fresh eyes tomorrow. That sometimes helps me.

Thanks again,
Mike

 

[Ken Snell \(MVP\)]

Then the problem may be that you're "accessing" the workbook through the
TransferSpreadsheet method while the loop is "in scope".

Let's try a completely different approach to this issue -- let's open the
EXCEL file and store the worksheet names in a Collection, close the workbook
file, and then loop through the Collection for doing the TransferSpreadsheet
method.


Dim obExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim strWorksheetName As String
Dim strFileName As String
Dim colWorksheets As Collection
Dim lngWorksheet As Long
Dim varWorksheet As Variant
Set colWorksheets = New Collection
strFileName = "C\test.xls"
Set obExcel = CreateObject("Excel.Application")
obExcel.Visible = False
Set objWorkbook = obExcel.Workbooks.Open(strFileName)
For lngWorksheet = 1 To objWorkbook.Worksheets.Count
strWorksheetName = objWorkbook.Worksheets(lngWorksheet).Name
If strWorksheetName = "System" Then Exit For
colWorksheets.Add strWorksheetName
Next lngWorksheet
objWorkbook.Close
Set objWorkbook = Nothing
obExcel.Quit
Set obExcel = Nothing
For Each varWorksheet In colWorksheets
DoCmd.TransferSpreadsheet acImport, 8, "tblImportedData", _
strFileName, False, varWorksheet & "!A2:AQ"
Me.Requery
Next varWorksheet
Set colWorksheets = Nothing
MsgBox ("Done!")

 

[Mikey B]

Ok, that works like a charm.
I had assumed that the excel object would need to be opened while transfering.

Thank you for your continuing help.
Mike B.

 

[Ken Snell \(MVP\)]

Ok, that works like a charm.
I had assumed that the excel object would need to be opened while
transfering.

Thank you for your continuing help.
Mike B.

You're welcome.