Excel Object Will Not Close (I did read other threads)

[AdamY]

Hi all,

I have tried so many different ways of coding this I can't even count them
anymore. No matter what, there is always an instance of EXCEL.exe running in
my task manager processes. This instance not closing makes the application
not able to run more than one time, unless EXCEL.exe is manually terminated.
My code is below. If I comment out the TransferSpreadsheet line it closes
just fine. I have thread threads on tons of sites on this and no one's
suggestions work. I am very frustrated, any help would be greatly
appreciated! Thanks...


Public Sub ImportData(ByVal strBook As String)

'create variables
Dim objExcel As Excel.Application
Dim objBook As Excel.Workbook
Dim strSheet, strEdit, strX As String
Dim intFind, intFind2, x, intRows, y As Integer
Dim bolCheck As Boolean

Set objExcel = CreateObject("Excel.Application")
Set objBook = objExcel.Workbooks.Open(strBook)

'loop through all worksheets to get data
For x = 1 To objBook.Worksheets.Count

strEdit = objBook.Worksheets(x).Range("I2")
bolCheck = strEdit Like "Select*"
If (bolCheck = True) Then
intFind2 = InStr(1, strEdit, "E", vbBinaryCompare)
strEdit = Mid(strEdit, intFind2)
intFind = InStr(1, strEdit, " ")
strEdit = Left(strEdit, intFind - 1)
strX = CStr(x)

'set worksheet name, sheet variable, and column headers
objBook.Worksheets(x).Name = strEdit & strX
strSheet = strEdit & strX & "!"
objBook.Worksheets(x).Range("J1").Value = "Table Name"
objBook.Worksheets(x).Range("K1").Value = "Workbook Name"

'count number of populated rows in sheet
intRows = 2
Do Until objBook.Worksheets(x).Cells(intRows, 1).Value = ""
intRows = intRows + 1
Loop
intRows = intRows - 1

'populate worksheet/table name field
For y = 2 To intRows
objBook.Worksheets(x).Cells(y, 10).Value = strEdit
Next y

'populate workbook field
For y = 2 To intRows
objBook.Worksheets(x).Cells(y, 11).Value = strBook
Next y

'pull spreadsheet data into access table
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"Members Table", strBook, True, strSheet
End If
Next x

objBook.Close False
Set objBook = Nothing
With objExcel.Workbooks
Do While .Count > 0
.Item(.Count).Close False
Loop
End With
objExcel.Quit
Set objExcel = Nothing
End Sub

 

[Mark M]

It may or may not help, but try:
Dim objExcel As Object
Dim objBook As Object
instead of dimming them as Excel objects.

 

[Klatuu]

Instead of Set objExcel = CreateObject("Excel.Application")

Try Set xlApp = New Excel.Application

I was having the same problem, and this seemed to make the difference. It
creates a new instance of Excel. So that even if you have another instance
of Excel open on your desk top, the 2 will not interfer with each other.
Please let me know if this worked for you.

 

[AdamY]

Mark and Klatuu,

Thank you for your suggestions, but I did try both to no avail. This has
something to do with Access VBA or Excel objects not liking the
DoCmd.TransferSpreadsheet. I made a seperate module that did the exact same
thing except not pull data back into Access using TransferSpreadsheet and
that app closes Excel just fine with the exact same code. Any other ideas?

Thanks Again,

Adam

 

[AdamY]

Thank you Mark and Klatuu for your posts. I did try both, but to no avail.
I have another module that is the exact same code except for the
DoCmd.TransferSpreadsheet line and that closes Excel perfectly. It must have
something to do with that line. Any other ideas?

Thanks Again,

Adam

 

[Klatuu]

Mark,

I believe it will. I have it running in production using exacltly that. I
spent a week experimenting with it because I was having the same problem.
Unless there are some system or office configurations that would affect it,
it does work. It works on about 20 users computers here. We have a mix of
W2k and XP. The only problem I encountered was that a few of our users did
not have the Office Object Library on their computers which caused a crash (
Including the bosses, of course).

 

[John Nurick]

Hi Adam,

This does seem as if it shouldn't be happening. What versions (and
service packs) of Windows and Office are you running?

There's just one thing in your code that makes me speculate, and that's
the way you edit the worksheet (adding the book and sheet names) and
then import from the unsaved workbook. On the surface this shouldn't
matter, but it might be eliciting some hidden flakiness. In your many
different ways of coding, have you tried either of these:

A) Open the workbook, iterate through the worksheets adding the book and
sheet names in columns K and J, then save the workbook and iterate
through it again importing the data.

B) Don't edit the workbook at all. Instead, build and run a series of
SQL statements that import the existing data and use calculated fields
to add the sheet and book names. They'd each look like this:

INSERT INTO [Members Table]
SELECT Field1, Field2, ...
'foo.xls' AS [Workbook Name],
'bar' AS [Table Name] " _
FROM [Excel 8.0;HDR=Yes;database=D:\TEMP\foo.xls;].[bar]

 

[Tim Ferguson]

'pull spreadsheet data into access table
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"Members Table", strBook, True, strSheet

Not sure of the details, but I am sure that I have read hereabouts about
TransferSpreadsheet not releasing handles to excel objects. Does the app
instance disappear if you comment this line out?

HTH


Tim F