Excel stays in RAM after DoCmd.TransferSpreadsheet

[aleksandra_83]

Hello,

I have searched all over google groups and internet, but I have not
found a solution to this problem that has worked. The solutions I found
helped me single out the line that is causing the problem, though.

I programmatically open a Microsoft Excel file from Access, do some
data checks etc., define a named range, perform
DoCmd.TransferSpreadsheet, then delete the range, set all referenced
objects to Nothing, etc. However, Excel stays in memory.

There is other code that deals with Excel application too, and if I
comment out DoCmd.TransferSpreadsheet the Excel is cleared from memory.

Is there something that TransferSpreadsheet method needs after its call
to disconnect the tie between Access/Excel?

Thank you so much!

[Chance]

I am totally guessing here, but I am thinking that you need to force a
relase of the MS Excel object. The issue is, I am not sure how it is
being set.

Set MSExcel = Nothing

You need to find out what the "MSExcel" above needs to be.

Then again, I could be totally wrong.

HTH

[Chance]

Here is where someone else is having the same issue.
http://www.utteraccess.com/forums/sh...8861&bodyprev=

Here is what was suggested.
set xlApp = nothing
I think I may have been right!

[aleksandra_83]

Thanks for the reply.

Actually I know that, and I am already doing it.

The release works for everything that I am doing with Excel, except
when I add that one line of DoCmd.TransferSpreadsheet. Is there
something inside this method that ends up tied to Excel somehow?

[pietlinden]

are you opening Excel by default and not closing it and destroying all
references? Help others help you out - post your code! Otherwise this
is a bit like pin the tail on the donkey.

[Br]

aleksandra_83 wrote:

Thanks for the reply.

Actually I know that, and I am already doing it.

The release works for everything that I am doing with Excel, except
when I add that one line of DoCmd.TransferSpreadsheet. Is there
something inside this method that ends up tied to Excel somehow?

Are you trying to run the Docmd.TransferSpreadsheet while you have an
open reference to the Spreadsheet/Excel?
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[david epsom dot com dot au]

Please post the actual line of code that is causing problems.
"Br@dley" <br*****@usenet.com> wrote in message
news:JJ*****************@news-server.bigpond.net.au...

aleksandra_83 wrote:

Thanks for the reply.

Actually I know that, and I am already doing it.

The release works for everything that I am doing with Excel, except
when I add that one line of DoCmd.TransferSpreadsheet. Is there
something inside this method that ends up tied to Excel somehow?

Are you trying to run the Docmd.TransferSpreadsheet while you have an open
reference to the Spreadsheet/Excel?
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[aleksandra_83]

I will post my code later. I cannot open it right now at this computer
for some reason. I apologize, but I did not get any notifications that
there were new messages, so I just thought noone's responded yet.

Anyway, as I've stated - ALL the references are destroyed, and the code
works perfectly (stepped through it step by step with uncommenting
blocks of code).

Bradely, I do not close the spreadsheet before the
DoCmd.TransferSpreadsheet though. I close it after all the transfers
have been done (they are in the same workbook, but different
worksheets). Should I close the workbook before I use
DoCmd.TransferSpreadsheet. This is definitely something I am not doing.
I am however destroying all references.

I will fetch the code tonight.

Thank you all

[aleksandra_83]

Hi Bradely,

yes I am trying to do exactly that. With open reference to Excel sheet.
Should I close it? And how far down the "closing" line do I need to go
- just the workbook, or the whole Excel?
I will get you do the code later.

Thanks!

[polite person]

On 4 Oct 2005 15:10:36 -0700, "aleksandra_83" <al*************@gmail.com> wrote:

Hello,

I have searched all over google groups and internet, but I have not
found a solution to this problem that has worked. The solutions I found
helped me single out the line that is causing the problem, though.

I programmatically open a Microsoft Excel file from Access, do some
data checks etc., define a named range, perform
DoCmd.TransferSpreadsheet, then delete the range, set all referenced
objects to Nothing, etc. However, Excel stays in memory.

There is other code that deals with Excel application too, and if I
comment out DoCmd.TransferSpreadsheet the Excel is cleared from memory.

Is there something that TransferSpreadsheet method needs after its call
to disconnect the tie between Access/Excel?

Thank you so much!

Hi
There is a long history of threads on this topic most of which end inconclusively.
It even happened to me!

David Fenton (who usually has the last word) suggests that all will be well
if you close everything AND always use late binding.

There are a number of KB articles which touch on this for A2k and maybe
other versions which don' t have all the service packs, so that is a good idea too.

[Br]

aleksandra_83 wrote:
<>

Bradely, I do not close the spreadsheet before the
DoCmd.TransferSpreadsheet though. I close it after all the transfers
have been done (they are in the same workbook, but different
worksheets). Should I close the workbook before I use
DoCmd.TransferSpreadsheet. This is definitely something I am not
doing. I am however destroying all references.

Yeah, worth a try.
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[Br]

aleksandra_83 wrote:

Hi Bradely,

yes I am trying to do exactly that. With open reference to Excel
sheet. Should I close it? And how far down the "closing" line do I
need to go - just the workbook, or the whole Excel?
I will get you do the code later.

Thanks!

All you can do is try different things :)
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[aaron.kempf]

i reccomend uninstalling excel from every machine in the world.

excel is a disease and can't be trusted to hold your valuable DATA.

use a DATABASE to hold your DATA and use Excel.. uh.. for NOTHING

[aleksandrakukic]

Thank you all for the replies.

Indeed the instance of Excel opened from Access, needs to be
destroyed/closed before DoCmd.TransferSpreadsheet executes from Access.