How do I close an open connection with Excel?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JT - 25 Jul 2005 20:23 GMT

I have a database with a linked table to an excel spreadsheet. This
spreadsheet will remain open, yet I want to be able to run queries off that
data. I am having a problem when closing excel that the process "Excel.exe"
in the task manager will not end. I found this only happends when I have the
database and the spreadsheet open at the same time and only when I run a
query against the linked spreadsheet. This excel spreadsheet needs to remain
open while running queries against the data, I just need to make sure it
closes the process on the close of excel. Right now there is a huge memory
leak while it just appends the memory usage the next time I open this linked
table.

Reply to this Message

Klatuu - 26 Jul 2005 14:09 GMT

Exactly what you are doing is unclear. If you have a spreadsheet linked as a
table, then used the DeleteObject method. If you have established an
instance of Excel by opening it, then you need to use the Quit method, and
set your object variable to Nothing.

Even that may not work as you expect. Access sometimes creates an instance
of Excel you don't know about if you don't fully qualify your reference to
it, so even though you destroy one instance, another may still be running.

I have had issues with this. To resolve it, I opened Task Manager and
clicked on hte Processes tab and steped through my code watching Task Manager
to see when the instances of Excel were created and destroyed.

> I have a database with a linked table to an excel spreadsheet. This
> spreadsheet will remain open, yet I want to be able to run queries off that
[quoted text clipped - 6 lines]
> leak while it just appends the memory usage the next time I open this linked
> table.

Reply to this Message

JT - 26 Jul 2005 16:22 GMT

Thanks Klatuu for replying so fast.
When your talking about the DeleteObject method do you mean to just delete
the linked table? I will paste the code I am using and maybe it will help
clear things up. I did try the DeleteObject for my linked table after
running the queries and that didn't seem to help. I am unsure on how to
check where each instance of Excel is being processed in the task manager.
Below is the code for a command button, just running this button is enough to
keep excel in my task manager if and only if the Excel Spreadsheet is open.
You can recreate this error by linking an excel spreadsheet in Access,
opening the spreadsheet in Excel, then running a query on the data in that
linked table while the spreadsheet is open in Excel. Here is my code, and I
am a newbie with VB so if you have suggestions an example code would be
appreciated.

THANKS!!!!

Private Sub compDND_Click()
On Error GoTo Err_compDND_Click
Dim dbs As ADODB.Connection
Set dbs = CurrentProject.AccessConnection

stDobName = "DNDdlt"
stDocName = "DND List"
DoCmd.SetWarnings False
DoCmd.OpenQuery stDobName, acNormal, acEdit
DoCmd.SetWarnings True

DoCmd.TransferSpreadsheet acImport, , stDocName, "S:\NPW Terminations\DO
NOT DELETE\NewDND\Do Not Delete Spread Sheet.xls", True - 1, "A:D"
lblclock.Value = Now()
dbs.Execute "Delete * from tmpMylogins"
lstbox1.Visible = True

Me.Requery
dbs.Execute "INSERT INTO tmpMyLogins ( [Current AWIDS], ShortName,
[Request #], F1, F2, F3, F4, F5, F7, F8, F9, F10, F11, F18, F19, F20 ) SELECT
[MyLogins Main].F6 AS [Current AWIDS], [MyLogins Main].F21 AS ShortName,
[MyLogins Main].F22 AS [Request #], [MyLogins Main].F1, [MyLogins Main].F2,
[MyLogins Main].F3, [MyLogins Main].F4, [MyLogins Main].F5, [MyLogins
Main].F7, [MyLogins Main].F8, [MyLogins Main].F9, [MyLogins Main].F10,
[MyLogins Main].F11, [MyLogins Main].F18, [MyLogins Main].F19, [MyLogins
Main].F20 FROM [MyLogins Main] WHERE ((([MyLogins Main].F6) > 0))"

DoCmd.SetWarnings False
DoCmd.OpenQuery "DNDUpdate", acNormal, acEdit
DoCmd.SetWarnings True
DoCmd.OpenQuery "DNDResults", acNormal, acEdit
lstbox1.Visible = False
dbs.Quit
Set dbs = Nothing

Exit_compDND_Click:
Exit Sub

Err_compDND_Click:
MsgBox Err.Description
Resume Exit_compDND_Click

End Sub

> Exactly what you are doing is unclear. If you have a spreadsheet linked as a
> table, then used the DeleteObject method. If you have established an
[quoted text clipped - 19 lines]
> > leak while it just appends the memory usage the next time I open this linked
> > table.

Reply to this Message

Klatuu - 26 Jul 2005 16:36 GMT

This doesn't make much sense. You code is fine. You are not linking to the
spreadsheet, you are immporting it. Once you have imported the spreadheet
into a table, there should be no instance of Excel running. I think I am a
little stumped here, too.

> Thanks Klatuu for replying so fast.
> When your talking about the DeleteObject method do you mean to just delete
[quoted text clipped - 79 lines]
> > > leak while it just appends the memory usage the next time I open this linked
> > > table.

Reply to this Message

JT - 26 Jul 2005 16:39 GMT

Sorry I should have mentioned, the Do Not Delete.xls spreadsheet is not the
linked table you are correct, but that is the data I am comparing the linked
table against. I have a linked table called "MyLogins Main" which the
dbs.execute is running the sql statement to extract the data from the linked
table into "tmpMyLogins" which is a local table in the database.

> This doesn't make much sense. You code is fine. You are not linking to the
> spreadsheet, you are immporting it. Once you have imported the spreadheet
[quoted text clipped - 84 lines]
> > > > leak while it just appends the memory usage the next time I open this linked
> > > > table.

Reply to this Message

Klatuu - 26 Jul 2005 16:46 GMT

That should still not cause that, I don't think. How about instead of
updating the Linked spreadsheet directly, create a temporary table, do the
updates there, then export the temporary table to a spreadsheet?

> Sorry I should have mentioned, the Do Not Delete.xls spreadsheet is not the
> linked table you are correct, but that is the data I am comparing the linked
[quoted text clipped - 90 lines]
> > > > > leak while it just appends the memory usage the next time I open this linked
> > > > > table.

Reply to this Message

JT - 26 Jul 2005 17:14 GMT

Well in theory thats what I'm trying to do. Although I have no way of
importing the data while the spreadsheet is opened. This spreadsheet
contains macros that will download data from an object browser. I need to
get the data off that spreadsheet without saving or closing the spreadsheet.
The only thing I could think of is to link the table, but then I get this
complication. There are a couple other things I might try....Creating an
Active Connection with Excel, that way we can terminate the connection.
Another idea would be to copy the Excel spreadsheet to a different location
and importing that copy. I already tried that but recieved an error "Access
is Denied". Now I'm not sure if that error is because the spreadsheet is
opened therefore locked, or if its because of the modules in that excel sheet
that is protected. I will do some more searching, but if you have any other
ideas on how to get this data in access, I am up for anything!
Thanks again for your help!

> That should still not cause that, I don't think. How about instead of
> updating the Linked spreadsheet directly, create a temporary table, do the
[quoted text clipped - 94 lines]
> > > > > > leak while it just appends the memory usage the next time I open this linked
> > > > > > table.

Reply to this Message

Klatuu - 26 Jul 2005 17:17 GMT

All I can do at this point is wish you good luck.

> Well in theory thats what I'm trying to do. Although I have no way of
> importing the data while the spreadsheet is opened. This spreadsheet
[quoted text clipped - 109 lines]
> > > > > > > leak while it just appends the memory usage the next time I open this linked
> > > > > > > table.

Reply to this Message