Error Closing Excel

[David]

Hello,

Anybody ever see the instance where Excel closes but the process still runs
in Windows Task Manager?

The following code explains this further:


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 visible=true is usually visible=false, but I want to see what
is going on... Excel is closing...
Final Note: If I comment out the FOR EACH block, the Excel process DOES close.

Thanks in advance.

 

[Klatuu]

Since you did not include your Dim statements, this may not be the problem,
but Access has a bad habit of getting confused about Xl objects. What can
happen is that if it can't figure out what the object belongs to, it will
create an additional instance of Excel on its own. You quit the one you
opened, but the one Access opened doesn't get closed. Try adding this line:

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

Set xlSheet = xlBook.Worksheets(1)

 

[David]

Are you saying add the line:

Set xlSheet = xlBook.Worksheets(1)

By the way, here are the DIM statements:

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

 

[David]

Tried that... No luck...

Code is now:

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

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

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

Set xlSheet = xlBook.Worksheets(1)

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

 

[Nugimac]

Hi David,
Check access vba help on automation. (Access 2003) From memory there is
something about how to explicitly close any additional instances of Excel
that are opened by CreateObject.
HTH
Nugimac

 

[David]

Does it matter that I am not using CREATEOBJECT?

 

[Nugimac]

Hi David, I looked for the info I was reading the other day, but sorry that I
couldn't find it again. The info was about closing hidden instances of excel
that were opened using create object. The advice was to either make the
hidden instance visible or to use the quit method to close excel.

HTH
Nugimac

 

[RoyVidar]

Hello,

Anybody ever see the instance where Excel closes but the process
still runs in Windows Task Manager?

The following code explains this further:


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 visible=true is usually visible=false, but I want to
see what is going on... Excel is closing...
Final Note: If I comment out the FOR EACH block, the Excel process
DOES close.

Thanks in advance.

I think the problem stems from you opening the same file both through
automation and the transfer thingie.

I think you should separate the processes, first fetch the names of
the sheets. Stuff them in an array or something, then close the excel
workbook, and perhaps add a DoEvents to be sure.

Then do the transfer thingie.

 

[Van T. Dinh]

With automation, I think you need to specify whether you want to save the
Workbook or not in the Close statement like:

xlBook.Close SaveChanges:= False

If you don't specify, Excel will post the "Save" prompt ... and the problem
is that this prompt will be either invisible (if the Excel object is not
visible) or (in my experience) behind the Excel application window if Excel
object is visible). Either way, the user can see the prompt and Excel waits
for the user to respond and hence deadlock occurs.