By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,334 Members | 1,080 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,334 IT Pros & Developers. It's quick & easy.

Excel stays in RAM after DoCmd.TransferSpreadsheet

P: n/a
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!

Nov 13 '05 #1
Share this Question
Share on Google+
13 Replies

P: n/a
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

Nov 13 '05 #2

P: n/a
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!

Nov 13 '05 #3

P: n/a
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?

Nov 13 '05 #4

P: n/a
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.

Nov 13 '05 #5

P: n/a
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
Nov 13 '05 #6

P: n/a
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

Nov 13 '05 #7

P: n/a
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

Nov 13 '05 #8

P: n/a
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!

Nov 13 '05 #9

P: n/a
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.
Nov 13 '05 #10

P: n/a
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
Nov 13 '05 #11

P: n/a
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
Nov 13 '05 #12

P: n/a
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

Nov 13 '05 #13

P: n/a
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.

Nov 13 '05 #14

This discussion thread is closed

Replies have been disabled for this discussion.