TransferSpreadsheet leaves Excel.exe in memory

[Yaff]

Excell stays in memory after the executing the code below
and the .xls file stays locked, preventing me to redo the function

The reason I do the Open/Close is to force Excel to recalculate.
(Data has been updated offline and Excell do not recalculate offline)

Many have the problem, I didn't found any answers to solve it.

Thx...

/***
Dim objXL As Object
Set objXL = CreateObject("Excel.Application")
objXL.Workbooks.Open "filename.xls"

objXL.Workbooks("filename.xls").Save
objXL.Workbooks("filename.xls").Close
DoEvents

objXL.Visible = False

DoCmd.TransferSpreadsheet , , "Test", "filename.xls",True

objXL.Quit
Set objXL = Nothing

***/

 

[Ken Snell \(MVP\)]

Why are you creating the EXCEL file using Automation? TransferSpreadsheet
will create the file for you -- just use TransferSpreadsheet and get rid of
all the other code steps.

 

[Klatuu]

Ken is correct. Unless there is code you did not post, there is not reason
to open Excel. This could, in fact be the problem. When using Automation,
you have to be very careful about how you reference your objects. Leaving an
instance of Excel running in the Processess tab of Task Manager is an
indication there is referencing problem.

Without being there to test this, I can't be positive, but from my
experience with Excel automation, here is the sequence of events:
You open an instance of Excel.
You open a workbook.
You close the workbook.
(At this point Excel is still running)
The TransferSpreadsheet fires up its own copy of Excel (It doesn't know the
other is already running).
You issue a Quit, but the instance that is destroyed is not the one you
opened, it is the one opened by the Transferspreadsheet, so the one you
opened remains running.

In any case, if you are not using automation to manipulate the spreadsheet
before either importing or exporting (your code doesn't specify), remove the
code that does the automation. If you are manipulating the excel file, do
the quit before you do the transferspreadsheet.

 

[Douglas J. Steele]

And if there is a legitimate reason for that code, change

objXL.Quit

to

objXL.Application.Quit

 

[Klatuu]

Yep.
--
Dave Hargis, Microsoft Access MVP

And if there is a legitimate reason for that code, change

objXL.Quit

to

objXL.Application.Quit

 

[Yaff]

Why are you creating the EXCEL file using Automation?

As I said, it is to force Excel to recalculate/refresh with updated data
prior to import/export.
I'm open to suggestion if you can tell me how to refresh a Excel spreadsheet
offline.

Here`s the problem I'm having: file.xls has two sheets (table1 and table2)
table2 does calculations based on references to table1

When I export table1 in file.xls, the sheet table2 doesn`t get updated until
I open/close file.xls
So if I import table2, without the OPEN/CLOSE crap... I don`t import the
right data.

It seams that your application.quit suggestion worked.

Thank you guys.

 

[Ken Snell \(MVP\)]

I don't have a good mental picture of why you're needing to refresh the
EXCEL file prior to exporting, but... suffice that you say you need it.

See answer elsethread from Douglas Steele.

 

[Yaff]

I don't have a good mental picture of why you're needing to refresh the
EXCEL file prior to exporting, but... suffice that you say you need it.

You are right, I don't need it before the export.
But I need it the second after when I want to import the updated values from
table2.