how to stop an excel process after opening in it access 2003

[paul.raeburn]

I am trying to stop an excel process after working excel through access
2003... it does not seem to matter what I try to do an instace of excel
stays running...

canyone suggest what I maybe doing wrong

here is my code:

>>>code start>>>>
Function transfer_aga_corps_data()
On Error GoTo transfer_aga_corps_data_Err

Dim db As Database
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim DirPath As String
Dim xlsfname As String
Dim xltfname As String
Dim xltTempfname As String

Set db = CurrentDb()
Set xlApp = New Excel.Application
DirPath = "C:\Documents and Settings\PR\My Documents\Downloads\AWSA
Database\"
xltfname = "temp_aga_corps.xls" ' template file
xltTempfname = "UseOnce_temp_aga_corps.xls" ' only used for this
routine
xlsfname = "aga_corps.xls" ' the file that is
used for AWSA/AGA office
xlApp.Visible = False
xlApp.Application.DisplayAlerts = False
xlApp.DisplayAlerts = False

'delete the aga_corps spreadsheet
If Len(Dir(DirPath & xlsfname)) > 0 Then
Kill DirPath & xlsfname
End If
' create a workbook from the template
Set xlBook = Excel.Workbooks.Open(DirPath & xltfname)
' save the template as a workbook
xlBook.SaveAs DirPath & xltTempfname
xlBook.Close
' transfer data to new wookbook
DoCmd.TransferSpreadsheet acExport, 8, "qry_aga_corps", (DirPath &
xltTempfname), True, ""
' run macro in excel
Set xlBook = Excel.Workbooks.Open(DirPath & xltTempfname)
xlApp.Run "create_sheets"
' save workbook the temp workbook as the correct used one
xlBook.SaveAs Filename:=DirPath & xlsfname
xlBook.Close
'delete the UseOnce_temp_aga_corps.xls spreadsheet that is used for this
routine
If Len(Dir(DirPath & xltTempfname)) > 0 Then
Kill DirPath & xltTempfname
End If
'Cleanup
xlApp.Excel.Application.DisplayAlerts = True
xlApp.Excel.Application.Quit
xlApp.Excel.DisplayAlerts = True

Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
Set db = Nothing

transfer_aga_corps_data_Exit:

Exit Function

transfer_aga_corps_data_Err:
MsgBox Error$
Resume transfer_aga_corps_data_Exit

End Function
<< code end<<

Regards - PR

 

[jal]

Excel is hard to close down, I can't recall any solutions, really. If you're desperate to get this solved, try doing a Kill Process (code below). The downside is that if the user has another instance of Excel that he WANTS to keep open, this code will kill that too, and he will lose any unsaved data.

Call KillProcess("Excel")

or is it (can't recall)

Call KillProcess("Excel.Exe")


Private sub KillProcess(AppNameOfExe As String)
Dim oProcList As Object
Dim oWMI As Object
Dim oProc As Object
' step 1: create WMI object instance:
Set oWMI = GetObject("winmgmts:")
If IsNull(oWMI) = False Then
' step 2: create object collection of Win32 processes:
Set oProcList = oWMI.InstancesOf("win32_process")
' step 3: iterate through the enumerated collection:
For Each oProc In oProcList
' option to close a process:
On Error Resume Next
If UCase(oProc.Name) = UCase(AppNameOfExe) Then
oProc.Terminate (0)
End If 'IsNull(oWMI) = False
Next 'oProc In oProcList
Else 'IsNull(oWMI) = False
'report error
End If 'IsNull(oWMI) = False
' step 4: close log file; clear out the objects:
Set oProcList = Nothing
Set oWMI = Nothing
End Sub

 

[jal]

There are other versions of this type of code available. I found the above on the .Net and am not sure whether it is the best one.

 

[boblarson]

I believe your problem is this code:

' create a workbook from the template
Set xlBook = Excel.Workbooks.Open(DirPath & xltfname)


You should be using this instead:
' create a workbook from the template
Set xlBook = xlApp.Workbooks.Open(DirPath & xltfname)

 

[boblarson]

And the reasoning is that you need to use the objects that you explicitly open. If you just use a general Excel. etc reference to an object then it will keep that open until Access closes.

 

[jal]

Thanks again, Bob, I'll make a note of that. I hope the original poster got the info as well.

 

[paul.raeburn]

many thanks lads...

for both your time and knowledge

Regards - Paul

 

[JohnAdams]

For me, the xlApp trick didn't work. I defaulted to KillProcess as described above, but with one small tweak to avoid killing unsaved workbooks the user may have open:

    i = Excel.Application.Workbooks.Count
    
    If i = 0 Then
        Call KillProcess("EXCEL.EXE")
    End If

 

[elliotgr]

I had a similar issue. If Excel opens an Excel child process, Access is not aware of it, and you do not have the ability to reference it in Access and close it down. Only the Excel app that was explicitly referenced in Access can be closed down.