Excel Not Closing

[Guest]

Hello,

Anybody have the experience where you open an Excel spreadsheet, process the
worksheets, then close it, only to find the PROCESS still running in Windows
Task Manager. This causes a problem next time I run the process, as it locks
the spreadsheet I had opened originally.

The code below describes what I am doing:


Set xlApp = New Excel.Application
xlApp.Visible = True

Set xlBook = xlApp.Workbooks.Open(Forms!Import!txtExcel.Value)

For Each xlSheet In xlBook.Worksheets
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"tbl_Temp_RT_" & xlSheet.Name, Forms!Import!txtExcel.Value, True, ""
& xlSheet.Name & "!"

Next

xlBook.Close
xlApp.Quit

Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

Note: The line: " xlApp.Visible = True" is typically "xlApp.Visible=False"
but I wanted to see what was going on...

One final note: If I comment out the FOR...EACH Loop, it closes Excel fine...

Any ideas?

Thanks in advance.

 

[Stefan Hoffmann]

hi David,

For Each xlSheet In xlBook.Worksheets
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"tbl_Temp_RT_" & xlSheet.Name, Forms!Import!txtExcel.Value, True, ""
& xlSheet.Name & "!"

Next
One final note: If I comment out the FOR...EACH Loop, it closes Excel fine...

You should not mix two methods of accessing a spreadsheet.

Use your For-Each loop to get the sheet names, store them in an array.
Import the data _after_ closing Excel.


mfG
--> stefan <--

 

[Guest]

Stefan,

Thanks for your reply.

Were you suggesting something like the following:

Dim xlApp As New Excel.Application
Dim xlBook As New Excel.Workbook
Dim xlSheet As New Excel.Worksheet
Dim xlArray(20) As String
Dim i As Integer


Set xlApp = New Excel.Application
xlApp.Visible = True
i = 1

Set xlBook = xlApp.Workbooks.Open(Forms!Import!txtExcel.Value)

For Each xlSheet In xlBook.Worksheets
xlArray(i) = xlSheet.Name
i = i + 1
Next

xlBook.Close SaveChanges:=False
xlApp.Quit


i = 1
Do Until xlArray(i) = ""
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"tbl_Temp_RT_" & xlArray(i), Forms!Import!txtExcel.Value, True, "" &
xlArray(i) & "!"

i = i + 1

Loop

 

[Guest]

Stefan,

I think that may be a good place to start. I did try rearranging the code,
and even though Excel is not open from a visual standpoint, there still is an
Excel process running in Windows Task Manager through the result of the
following code. Any ideas?

Dim xlApp As New Excel.Application
Dim xlBook As New Excel.Workbook
Dim xlSheet As New Excel.Worksheet
Dim xlArray(20) As String
Dim i As Integer

i = 1

Set xlBook = xlApp.Workbooks.Open(Forms!Import!txtExcel.Value)
Set xlSheet = xlBook.Worksheets(1)
For Each xlSheet In xlBook.Worksheets
xlArray(i) = xlSheet.Name
i = i + 1
Next


xlBook.Close SaveChanges:=False
xlApp.Quit

 

[RoyVidar]

Stefan,

I think that may be a good place to start. I did try rearranging the
code, and even though Excel is not open from a visual standpoint,
there still is an Excel process running in Windows Task Manager
through the result of the following code. Any ideas?

Dim xlApp As New Excel.Application
Dim xlBook As New Excel.Workbook
Dim xlSheet As New Excel.Worksheet
Dim xlArray(20) As String
Dim i As Integer

i = 1

Set xlBook = xlApp.Workbooks.Open(Forms!Import!txtExcel.Value)
Set xlSheet = xlBook.Worksheets(1)
For Each xlSheet In xlBook.Worksheets
xlArray(i) = xlSheet.Name
i = i + 1
Next


xlBook.Close SaveChanges:=False
xlApp.Quit

1 Never ever do implicit instantiation when automating (dim as
New...), always be explicit.
2 Release all objects in the correct order

Air code

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Set xlApp = CreateObject("Excel.Application")

Set xlBook = xlApp.Workbooks.Open(Forms!Import!txtExcel.Value)
Set xlSheet = xlBook.Worksheets(1)
For Each xlSheet In xlBook.Worksheets
xlArray(i) = xlSheet.Name
i = i + 1
Next

Set xlSheet = Nothing
xlBook.Close SaveChanges:=False
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing

You might need a DoEvents after closing the workbook and after
quitting Excel.