Excel Automation from Access - releasing instance

[Gary Cobden]

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 ObjectDim xlBook As WorkbookDim xlSheet As
WorksheetDim strFilename as stringSet strfilename = "c:\test.xls"Set xlApp2
= CreateObject("Excel.Application")xlApp2.Visible = TrueSelect 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.ClosefCloseApp
("XLMain") Set xlBook = Nothing xlApp2.QuitSet 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 IfEnd Function Thanks Gary

[Eric Schittlipz]

"Gary Cobden" <ga********@nmrs.freeserve.co.uk> wrote in message
news:cq**********@news8.svr.pol.co.uk...

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 ObjectDim xlBook As WorkbookDim xlSheet
As
WorksheetDim strFilename as stringSet strfilename = "c:\test.xls"Set
xlApp2
= CreateObject("Excel.Application")xlApp2.Visible = TrueSelect 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.ClosefCloseApp
("XLMain") Set xlBook = Nothing xlApp2.QuitSet 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 IfEnd Function Thanks Gary

Did my previous answer to this not show up? You received more than one
response which you seem to have ignore and started a new post.