Releasing Excel Instance - Automation from Access

[Guest]

I have a problem with the following code, which leaves an instance of Excel
visible in Task Manager.

By a process of elimination I have got it down to the fact that something in
the DoCmd.Transfer Spreadsheet line is holding on to an Excel reference
somewhere - can anybody assist (if I comment this line out, the instance is
released, and not visible in Task Manager).

Private Sub btnLoadData_Click()
Dim xlApp2 As Object
Dim xlBook As Workbook
Dim xlSheet As Worksheet
Dim strFilename as string
Set strfilename = "c:\test.xls"
Set xlApp2 = CreateObject("Excel.Application")
xlApp2.Visible = True
Select Case Me.Data_File_From
Case "West Herts"
xlApp2.Workbooks.Open FileName:=strFileName,
Password:="barbados"
Case "Luton"
xlApp2.Workbooks.Open FileName:=strFileName
Case "Stoke Mand"
xlApp2.Workbooks.Open FileName:=strFileName,
Password:="amersham"
Case "South Bucks"
xlApp2.Workbooks.Open FileName:=strFileName,
Password:="amersham"
Case "South Bucks Susp"
xlApp2.Workbooks.Open FileName:=strFileName,
Password:="amersham"
End Select

DoCmd.TransferSpreadsheet , , strImportTableName, strFileName, True

xlApp2.Workbooks.Close
fCloseApp ("XLMain")

Set xlBook = Nothing

xlApp2.Quit
Set xlApp2 = Nothing

End Sub


Function fCloseApp(lpClassName As String) As Boolean

Dim lngRet As Long, hWnd As Long, pID As Long

hWnd = apiFindWindow(lpClassName, vbNullString)
If (hWnd) Then
lngRet = apiPostMessage(hWnd, WM_CLOSE, 0, ByVal 0&)
Call apiGetWindowThreadProcessId(hWnd, pID)
Call apiWaitForSingleObject(pID, INFINITE)
fCloseApp = Not (apiIsWindow(hWnd) = 0)
End If
End Function

Thanks

Gary

 

[Alex Dybenko]

Hi Gary,
agree that DoCmd.Transfer Spreadsheet could hold excel. i had once something
similar, i think i solved it by making one more DoCmd.Transfer Spreadsheet
of one-record table to temporary file, just to remove this "hold"

and why do you have this fCloseApp ("XLMain") before quiting Excel?
try to move it at the end, so if excel did not close after quit - you
shutdown its process.