473,289 Members | 1,840 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

Excel stays in RAM after DoCmd.TransferSpreadsheet

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
13 5913
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
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
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
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
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
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: mcnewsxp | last post by:
how can i specify a particular worksheet using the docmd transferspreadsheet? thanks, mcnewsxp
3
by: user_5701 | last post by:
Hello, I'm getting an error with a Docmd.Transferspreadsheet line of code: DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel2000, "tblTest", pathAndFilename, True The above line...
4
by: Tom Louchbaum | last post by:
I try to use this command to import a spreadsheet "DoCmd.TransferSpreadsheet acImport, 8, tablename & " -tbl", DIR & "Book2.xls", True" and it errors out. If I try to import that same...
4
by: Gregc. | last post by:
Hi I'm trying to download a query into excel and opening it up as a pivot table. Firstly, is this possible? This is my code thus far: Function Export() Dim excel As String excel =...
3
by: sranilp | last post by:
Hey All, Actually I need to export the data from Access to Excel particular spreadsheet(ie.Raw Data),so I was using Docmd.Transferspreadsheet but in this syntax where i can give the spreadsheet...
2
by: amir369 | last post by:
Hi All, I'm trying to import an Excel sheet, but with more the one rang, couse the columns aint sequence. for example: DoCmd.TransferSpreadsheet acImport, , "DailyRprzntvRpt", _ ...
5
by: jerelp | last post by:
I have a form with one button the code for that button is On Error Resume Next DoCmd.TransferSpreadsheet acImport, 8, "FuelConsumption NewData", Me.tb_FileName, 1, "sheet2$" On Error Resume...
4
by: blammo04 | last post by:
My problem is that Access freezes up whenever I try to use the docmd.transferspreadsheet method, I can use the docmd.outputto and it works fine but I need to be able to use the transferspreadsheet...
0
by: Van Fitz | last post by:
I am importing an excel worksheet into an existing access 2000 table using TransferSpreadsheet. Im using a timestamp field as well in this table. This is going well, however, once the data is...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.