It is very strange that Excel continues to run in the background.
I have 4 routines that formats different Excel worksheets after I have exported an query... I don't have problems with Excel continuing to run in the background.
Here is part of the routine: (I cut out a lot of lines)
(See the last 8 lines)
Code:
Sub EditVendorWkSht(pWkshtPathName As String, pVendor As String, pMthYr As String)
Dim xlx As Object
Dim xlw As Object
Dim xls As Object
Dim sRow As Integer
Dim eRow As Integer
Dim RowDiff As Integer
Dim blnEXCEL As Boolean
blnEXCEL = False
' Establish an EXCEL application object
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
On Error GoTo 0
' Change True to False if you do not want the workbook to be
' visible when the code is running
xlx.Visible = False
Set xlw = xlx.Workbooks.Open(pWkshtPathName)
'get worksheet name
' (note that the worksheet must already be in the EXCEL file)
Set xls = xlw.Worksheets(1)
'add two rows at the top
xls.Rows("1").EntireRow.Insert
xls.Rows("1").EntireRow.Insert
xls.Range("A1").Select
xls.Range("A1").FormulaR1C1 = pVendor
xls.Range("A2").Select
xls.Range("A2").FormulaR1C1 = pMthYr
If pVendor = "Statement Monthly Details" Then
With xls
'
' lots of formatting code
'
End With
End If
' Close the EXCEL file while saving the file, and clean up the EXCEL objects
Set xls = Nothing
xlw.Close True ' close the EXCEL file and save the new data
DoEvents 'had to add this - seems like the save needs time to finish
Set xlw = Nothing
If blnEXCEL = True Then
xlx.Quit '<<--- I noticed you don't have this command in your code
End If
Set xlx = Nothing
End Sub