Trouble importing Excel spreadsheet 1

[Orion45]

I am using VBA code in Access 2000 to import a user-defined spreadsheet into the database. This is working great except the Excel instance does not close. When the user then tries to move or delete that Excel file they receive a message that the file is still in use. Currently the only way for the user to get around this is to use Ctrl-Alt-Delete and then kill the Excel instance from the Processes list. I have tried several different properties and methods of the Excel object but have not been able to close the instance. My code is as follows:

On Error GoTo cmdImport_err
Dim strTable As String
Dim strPath As String
Dim xl As Excel.Application
Dim strSQL As String
Dim strType As String

strTable = "EssayGrades_temp"
Set xl = New Excel.Application    'instantiate Excel
xl.Dialogs(xlDialogFindFile).Show
strPath = xl.ActiveWorkbook.FullName
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strPath, -1
xl.Application.Quit
Set xl = Nothing
cmdImport_err:
If Err.Number = -2147217900 Then
    MsgBox "You have attempted to insert duplicate data.  Please make sure you have not imported the same file twice.", vbExclamation, "Duplicate Data"
    GoTo ErrRecover
Else
    MsgBox Err.Description & "An error occured while attempting to import the file."
    GoTo ErrRecover
End If

Any Ideas?? Thanks

 

[marina9]

Hi Orion45,

It looks like you may have an issue with the dialog box not closing properly. Try this:

...
strTable = "EssayGrades_temp"
Set xl = New Excel.Application 'instantiate Excel
xl.Dialogs(xlDialogFindFile).Show
strPath = xl.ActiveWorkbook.FullName
xl.Dialogs.Application.Quit
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, strTable, strPath, -1
...

Also, make sure you add "Exit Sub" right after the Set xl = nothing otherwise you will always run through your error handler code.


Hope this helps!

 

[Orion45]

Thanks for your reply.
I tried your suggestion but I am still seeing an instance of Excel in the Processes list after the code has executed. I use this same kind of logic to interface with Word and Outlook and it works fine. I'm stumped!
I do have an Exit Sub statement before my error handler, I guess I just didn't copy it over with the rest of the code. Good catch.
Thanks again,

 

[PHV]

Try to close the workbook and quit excel BEFORE the TransferSpreadsheet.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Orion45]

Wow, I can't explain why but that worked great. Thanks a lot.

 

[sammidiakreddy]

when I use
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, strTable, strPath, -1

after completing importing, the Excel Application ( In Task Manager,XP) is still running in background. How can I close the Excel Application which is running in background?
Thanks
Sammidi Anil