XL Still Hanging Around After Automation

[Dugantrain]

I have the following code in Access which successfully has an Excel Workbook Refresh its queries from another Data Source (a ProTrack database) and then imports that data from Excel into Access:

Public Function XL_CAD_Routine()
On Error GoTo XL_Cad_Routine_Err
Dim strFile As String
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim xlApp As New Excel.Application
strFile = "S:\WAMU-XP\Scheduling_Database\BRIO_CADs\Protrack_CADs.xls"
Forms!FrmScheduling.Form!LblCad_Import_Status.Caption = "Locating Excel File..."
DoEvents
Set xlWB = xlApp.Workbooks.Open(strFile)
Forms!FrmScheduling.Form!LblCad_Import_Status.Caption = "Requerying Data Source..."
DoEvents
xlApp.Visible = True
xlApp.ActiveWorkbook.RefreshAll
xlApp.Visible = False
DoEvents
Forms!FrmScheduling.Form!LblCad_Import_Status.Caption = "Importing Data..."
DoEvents
For Each xlWS In xlWB.Worksheets
    xlWS.Select
    xlWS.Range("S1").Select
    ActiveCell = "STATUS"
    DoCmd.TransferSpreadsheet acImport, , "tblCAD_Import", strFile, True, xlWS.Name & "!"
Next
Forms!FrmScheduling.Form!LblCad_Import_Status.Caption = "Closing Excel..."
DoEvents
xlWB.Close False, strFile
xlApp.Quit
Set xlWS = Nothing
Set xlWB = Nothing
Set xlApp = Nothing
Exit Function
XL_Cad_Routine_Err:
    MsgBox "XL_CAD_Routine:  " & Err.Description
Exit Function
End Function

The problem is that the Excel workbook seems to still be hanging around in the background, even though I have closed the Workbook as well as quit the Excel application and released all variables. Also, when I try to open any other workbook, I get a ghost frame of that workbook and can only get Excel to function properly again when I do a Ctl-Alt_Del and manually end Excel. Could there be something in the above code that is doing this, it certainly seems fine enough.

 

[Russell Hauf]

Interesting. It looks fine to me except for the .Close False, strFile part. Why have strFile if you are not saving changes? Don't know if this will help, but you never know with Excel/VBA.

-Russell
__________
wearing short pants today

 

[Dugantrain]

Right, that was just something that I added as an after-thought thinking that maybe if I explicitly named which file I wanted closed, then it would close that file. No good, of course.

 

[Russell Hauf]

The only other thing that I can think of to try is to set xlWB to Nothing before the xlApp.Quit line.

-Russell
_____________
wearing short pants today

 

[Dugantrain]

Man, good try but I've put that line of code just about everywhere you can imagine. I even pasted it to the side of my cubicle. No help. I think that this actually may be an executable error; since I installed Access 2002, I've had problems with Excel; I often get "ghost-frames" when I try to open Excel and I have to do a ctl-alt-del and manually kill Excel in order to get it running again. This is the same thing that's going on when I execute the above procedure; Excel won't function properly until I kill it and open the application again.

 

[bat17]

One observation is that your error code does not loop you back to set your variables to nothing, so if you are getting an error towards the end of the code it would skip the clearing of the variables. try commenting out the "On Error" statement and then running it.

Clutching at straws maybe, but worth a try

Peter