[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel remains in memory after using transferspreadsheet in Access

Posted on 2006-05-07
11
Medium Priority
?
1,069 Views
Last Modified: 2012-06-27
I am having problems with an Excel tool I created. Some of the VBA code in the Excel file opens an instance of Access, calls an Access procedure that uses transferspreadsheet to import data from the same excel file, and then closes the instance of Access.

The problem is that after completing the process and closing the Excel file, there is still an instance of Excel visible in the Windows task manager. I can also still see the VBAProject for the Excel tool in the VBE when ANY Excel file is open (until I kill the process in the task manager).

After playing around a little I tracked the problem down to the procedure in Access that imports the Excel data. If I run the procedure from within Access when the Excel tool is already open, the problem occurs. If I run the procedure from Access when the Excel tool is NOT open, the problem doesn't occur and Excel does not remain in memory; however I need to call the procedure from within the Excel tool, because I dont want the user to ever have to deal with the Access database. It seems like if transferspreadsheet is used to import Excel data when the Excel file isn't open, Access creates an instance of the Excel file, imports, then closes the instance of Excel. But if the Excel file is already open, transferspreadsheet still creates an instance of the Excel file, imports, but then never closes the instance.

Here is the Access procedure:

---------------------------------------------------------------------------------

Public Sub ImportExcelData(strFullPath As String, strAcTable As String, _ strXlRange As String)

'Error Handling
On Error GoTo ImportExcelData_Error

'Delete the access table if it already exists
On Error Resume Next
DoCmd.RunSQL "DROP TABLE " & strAcTable
On Error GoTo ImportExcelData_Error

'Import the data
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _ strAcTable, strFullPath, True, strXlRange

'Error Handling
On Error GoTo 0
Exit Sub

ImportExcelData_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ")"

End Sub

---------------------------------------------------------------------------------

Any ideas??

Thanks.

Mike
0
Comment
Question by:mf720x
  • 4
  • 4
  • 3
11 Comments
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16625442
It does?
Interesting and weird.
(It wasn't already there was it? lol)

If it's the only running instance of Excel you could perhaps kill it thusly

Dim objKillit as Object

Set objKillit = GetObject(,"Excel.Application")
objKillit.Quit
Set objKillit = Nothing


'Tis a curious one though...
I wonder if the file is loaded in it - in which case GetObject might be yet more specific.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16625462
(Hmm OK reading question mroe thoroughly "I dont want the user to ever have to deal with the Access database" means you'll definately have another instance open).

So do you work from Excel only.
In code launch an instance of Access - and call the procedure to import the data from that database reference in Excel?

And that round trip then leaves a second hidden instance loaded?

The instance of Access you create - is that killed successfully?
0
 

Author Comment

by:mf720x
ID: 16625596
LPurvis,

The instance of Excel that the access transferspreadsheet code creates is not the only one open because the transferspreadsheet code in Access is actually called from the Excel file that contains the data to be imported.  Using the code you supplied to kill the Excel application closes the Excel file that I want to stay open, but leaves the unwanted Excel instance.  I need the real Excel file to stay open after Access pulls data from it and closes because the Excel file still runs more code after that.

Another weird thing that I noticed while playing around with it... running the import procedure in access multiple times while the Excel file is open continues to create multiple instances of the Excel file.  When I open the Excel file and look in the VBE, there is one instance of its VBAProject (as there should be).  If I then open the access database and run the import procedure (or call it from Excel), another instance of the Excel file's VBAProject shows up in the Excel VBE (but only the original workbook is visible) .  If I run the import again, another instance shows up.... and so on.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:mf720x
ID: 16625622
Lpurvis,

Yes, you are correct in that everything is called from Excel.  The instance of access that I create is killed successfully.

All,

I have played around with the problem and managed to simplify it.  It seems to be a generic problem with transferspreadsheet (at least the way I am using it).  To test this I created an Excel file called Test.xls and an Access database.  I put some data in the first few cells of Test.xls and added the following code to a module in the blank access db:

Sub ImportData()
'Import the data
DoCmd.TransferSpreadsheet acImport, , "tbl_TestData", _
    "C:\Documents and Settings\mahfss\Desktop\Test.xls", _
    True, "Sheet1!A1:B3"
End Sub

Running that code when Test.xls is not open succesfully imports the data and no instances of Excel are in the task manager afterwards.  If I run the code in Access while Test.xls is open, the data is successfully imported... BUT after closing Test.xls, Excel is still visible in the task manager.

Is there anyway to keep Excel from getting stuck in memory when importing data from an open Excel file using transferspreadsheet in Access??
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16625725
If concurrency is the issue then could you perhaps just make a copy of the file and import that copy?
0
 
LVL 58

Accepted Solution

by:
harfang earned 1400 total points
ID: 16625824
Hello,

You are hitting one of the DDE bugs.

When Access needs data from Excel, it tries to open the file through an internal driver. If the file is already open, it would have to be in read-only mode. Since Access is _meant_ to be able to edit the data (this has been recently disabled, though), it switches to plan B: DDE.

I can almost see the bug. The same method "TransferSpreadsheet" is used to to import, export and link. Well, link needs DDE -- let's use that in all cases, and the user will open the linked table next thing, so why close the DDE link?

It's not a link, it's only an import. Too bad, the DDE link remains open. That's it. A bug.

To work around it, you need to grand full file access. Either use DoCmd.TransferSpreadsheet on a closed file, or allow workbook sharing for that file. If the workbook is sharable, the bug does not occur.

Cheers!
(°v°)
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16626497
Sounds to me like a simple menu choice and problem solved is better than having the code create a copy of your file etc.
:-)
0
 

Author Comment

by:mf720x
ID: 16627502
Harfang,

It seems like you are correct about the source of the problem... but unfortunately, the problem becomes reversed when the workbook is shared.  

If an Excel file is not shared, then using transferspreadsheet to import from the Excel file when it is open will create an additional instance of Excel that remains in memory.

But if an Excel file is shared, then using transferspreadsheet to import from the Excel file when it is open automatically closes the Excel file after the import.  I need the file to stay open because there is more code that needs to be run.

Any ideas on why Excel closes when it is shared?  Or other suggestions?
0
 
LVL 58

Expert Comment

by:harfang
ID: 16627611
This is nagging me. I tried to find an official bug report, but couldn't find anything. Except somebody complaining  about the same behavior at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=386005&SiteID=1

Also, I'm not sure anymore about DDE. Excel links and data transfers used to be handled like that, but they could have switched to OLE anytime after A95... It's still an "unreleased object handle" type of bug, though, and we have no control over it.

-- reloaded page and saw your comment --

I could not reproduce this new twist. With a shared workbook, Access does manage to "dirty" the Excel file just by importing the data, but besides that, everything works as expected. I do use A2k+E2k however. There might be a problem with later versions, especially since the patch that prevents Access from linking in read/write mode to Excel documents.

I did get a laugh out of this one:
* ghost instance of Excel -- with non-shareable test.xls still open.
* double-click test.xls -- Excel "opens" without screen updating
* switch to VB and see:
        ______________________
        [Microsoft Visual Basic  [x]
        |¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
        | /!\  Automation error
        | ---  Catastrophic failure
        |
        |  [   OK   ]  [  Help  ]
        -----------------------------------

Never  had that one.

It seems there  is something seriously wrong with TransferSpreadsheet. I would consider writing a CSV from Excel (since it's open anyway) and import that from Access.

Sorry, can't test with later versions. Gool luck!
(°v°)
0
 

Author Comment

by:mf720x
ID: 16627762
Update:

Everything seems to be working fine now.  Harfang, your suggestion to share the workbook seems to have worked.  I had some other code in Access I had been playing around with that was causing Excel to close after finishing (oops... its getting late here).  The Excel file contains some functionality that doesn't work when shared so I just share/unshare in code for the import.  Here is some of the relevant code in case anyone with a similar problem stumbles across this:

'Excel VBA Code:
'Share the workbook
Application.DisplayAlerts = False
Application.ThisWorkbook.SaveAs , , , , , , xlShared
Application.DisplayAlerts = True

'Access VBA Code:
'Import the data (some variables were defined earlier in code)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strAcTable, _
    strFullPath, True, strXlRange

Excel VBA Code (After the Access import):
'Unshare the workbook
Application.DisplayAlerts = False
ThisWorkbook.ExclusiveAccess
Application.DisplayAlerts = True

Thanks for the help!
0
 
LVL 58

Expert Comment

by:harfang
ID: 16627805
I'm glad it worked! Depending on what you do, it might make sense to save the workbook anyway, so that Access gets the latest data. You probably know how to program for Access from Excel and vice-versa? The three code fragments above could be run from the same VB instance...

Anyway, good luck with your project!
(°v°)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question