Kill Excel!

[SymbionA]

I am having trouble closing excel from VBA, there have been numerous threads on this subject but I still cant get my code to remove excel.exe

Any ideas on how I can modify my code to remove Excel from Task Manager? My code is below:

Thanks in advance!

Public Function ExportRequest() As String
On Error GoTo err_Handler

' Excel object variables
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim sTemplate As String
Dim sTempFile As String
Dim sOutput As String

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String
Dim lRecords As Long
Dim iRow As Integer
Dim iCol As Integer
Dim iFld As Integer

Const cTabTwo As Byte = 2
Const cStartRow As Byte = 2
Const cStartColumn As Byte = 1

DoCmd.Hourglass True

' set to break on all errors
Application.SetOption "Error Trapping", 0

' start with a clean file built from the template file
'sTemplate = CurrentProject.Path & "\SalesTemplate.xls"
'sOutput = CurrentProject.Path & "\SalesOutput.xls"

sTemplate = "\\S-healthcare\commonHC\Distribution Services\National Buying\Inv_pur\Development\InvBuySuggested 2007Test.xls"
sOutput = "\\S-healthcare\commonHC\Distribution Services\National Buying\Inv_pur\Development\InvBuySuggested 2007NewTest.xls"
If Dir(sOutput) <> "" Then Kill sOutput
FileCopy sTemplate, sOutput

' Create the Excel Applicaiton, Workbook and Worksheet and Database object
Set appExcel = Excel.Application
Set wbk = appExcel.Workbooks.Open(sOutput)
Set wks = appExcel.Worksheets(cTabTwo)
sSQL = "select * from OutputFile"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)

' For this template, the data must be placed on the 4th row, third column.
' (these values are set to constants for easy future modifications)
iCol = cStartColumn
iRow = cStartRow
If Not rst.BOF Then rst.MoveFirst
Do Until rst.EOF
iFld = 0
lRecords = lRecords + 1
'Me.lblMsg.Caption = "Exporting record #" & lRecords & " to SalesOutput.xls"
'Me.Repaint

For iCol = cStartColumn To cStartColumn + (rst.Fields.Count - 1)
wks.Cells(iRow, iCol) = rst.Fields(iFld)

If InStr(1, rst.Fields(iFld).Name, "Date") > 0 Then
wks.Cells(iRow, iCol).NumberFormat = "mm/dd/yyyy"
End If

wks.Cells(iRow, iCol).WrapText = False
iFld = iFld + 1
Next

wks.Rows(iRow).EntireRow.AutoFit
iRow = iRow + 1
rst.MoveNext
Loop

ExportRequest = "Total of " & lRecords & " rows processed."
'Me.lblMsg.Caption = "Total of " & lRecords & " rows processed."

exit_Here:
' Cleanup all objects (resume next on errors)

appExcel.DisplayAlerts = False
appExcel.Save
appExcel.DisplayAlerts = True
appExcel.Quit

 

[JerryKlmns]

exit_Here:
' Cleanup all objects  (resume next on errors)
rst.Close
Set rst = Nothing
Set dbs = Nothing

appExcel.DisplayAlerts = False
wbk.Save
appExcel.DisplayAlerts = True
Set wks = Nothing
Set wbk = Nothing
appExcel.Quit
Set appExcel = Nothing

 

[formerTexan]

I ran into the same problem, raised the issue here and received a comprehensive set of excellent replies. You might want to check my old thread:

http://www.tek-tips.com/viewthread.cfm?qid=1284240&page=1

Cheers,
Bill

 

[PHV]

Replace this:
Set appExcel = Excel.Application
with this:
Set appExcel = CreateObject("Excel.Application")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JerryKlmns]

One more litlle line to be added

exit_Here:
' Cleanup all objects  (resume next on errors)
rst.Close
Set rst = Nothing
Set dbs = Nothing

appExcel.DisplayAlerts = False
wbk.Save
appExcel.DisplayAlerts = True
Set wks = Nothing
[b]wbk.Close[/b]
Set wbk = Nothing
appExcel.Quit
Set appExcel = Nothing

 

[SymbionA]

Thanks guys for all your help.

JerryKlmns I did put your code in and re-ran the code, however, EXCEL.exe was still open in Task Manager, however, the clean up code you provided is much better

I then combined user PHV code and re-ran my application and Excel.exe closed!!

Marvellous!

Thanks again to all who contributed, that was so annoying.