Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel process left open by VBA

Status
Not open for further replies.

HubbleO

Programmer
Apr 22, 2005
10
US
I'm using VBA in Access 2003 to open an Excel spreadsheet and dump data from two worksheets into two tables. The code does not work if Excel is already open, and once the code ends the Excel process is left running. To further complicate things even with just the process open the code does not work, so after each run I have to open the task manager and manually end the Excel process. Surely there is a way to close the process from within VBA, no?

Code:
Dim objExcel As Object, objWb As Object
...
    strFile = "U:\Jim.ONeil\Franklin\June Original.xls"
    strTable1 = "tblSummaryLanding"
    strSheet1 = "Summary!"
    strTable2 = "tblMonthlyLanding"
    strSheet2 = "June2008!"
    Set objExcel = CreateObject("Excel.Application")
    With objExcel
        Set objWb = .Workbooks.Open(FileName:=strFile, Password:="finance")
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable1, strFile, 0, strSheet1
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable2, strFile, 0, strSheet2
        objWb.Close SaveChanges:=False
        .Quit
        Set objExcel = Nothing
    End With
 
Have you tried just using

[tt] strFile = "U:\Jim.ONeil\Franklin\June Original.xls"
strTable1 = "tblSummaryLanding"
strSheet1 = "Summary!"
strTable2 = "tblMonthlyLanding"
strSheet2 = "June2008!"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable1, strFile, 0, strSheet1
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable2, strFile, 0, strSheet2[/tt]

See, if you're using the TransferSpreadsheet method of the Accesss.Application.DoCmd object you're using one method of opening Excel to export/import data. Opening the same workbook through automation will cause problems, as you're actually trying to open the same workbook twice. Same happens as if you try to open the same workbook more than once through the interface, too.

I'd suggest that you use automation first to ensure this workbook is not open. If it is open, save and close it, then .Quit the automation, then afterwards, after releasing the objects, and perhaps a DoEvents, then issue the above code with the TransferThingie.

Roy-Vidar
 
Without creating the Excel and workbook objects how do I pass along the password for the file? Running it as you posted I get an error stating it "Could not decrypt file."
 
I've seen some indication the method you're using might, or should work (see for instance Alex Dybenkos recommendation here though he seems to have some of the same problems), but mostly it wont. Reason is, I think, that opening the same file twice, through two different, interfaces, creates problems.

Down here thread707-1489870, is a method of first removing the password, closing the file, then doing the transferthingie, then opening the file again and assign password. Same method is mentioned here too.

Roy-Vidar
 
The recommendation from Alex Dybenkos is where I originally started. I modified the code from there to get to where I am now. The other references seem to all create an Excel object.

Thinking in another direction is there code I can use to extract the data from the Excel spreadsheet without using the DoCmd.TransferSpreadsheet method? Somehow using the workbook or a worksheet object?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top