Excel.EXE will not close... Only when database closes

[BMaerhofer]

I have tried many different ways and cannot seem to get it to close. I have
used this code before and it works just fine. Excel.EXE will keep running
after the code finishes and I get errors when trying to run another process
that uses excel. The only way it seems to kill the process is if I exit the
databaes after running.

Thanks in advance for helping!

HERE IS THE CODE:

Set XLApp = New Excel.Application
With XLApp

..Workbooks.Open MyFile

''''FORMAT''''

Range("A1:AE1").Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Cells.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Cells.EntireColumn.AutoFit
Columns("I:K").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
Range("I:K,AE:AE").Select
Range("AE1").Activate
Selection.NumberFormat = "#,##0.00"
Range("AA1").Select
ActiveCell.FormulaR1C1 = Forms!FrmWeeklyReports!Month & " AGE"
Range("AB1").Select
ActiveCell.FormulaR1C1 = Forms!FrmWeeklyReports!Month & " CD"
Range("AC1").Select
ActiveCell.FormulaR1C1 = Forms!FrmWeeklyReports!Month & " CAT"
Range("A1").Select

..ActiveWorkbook.Save
End With
XLApp.Quit


- I have tried .Quit . Close

Nothing but problems!

Thanks!!

 

[Ken Snell]

Replace your use of Selection and ActiveCell with specific references to the
desired cells. The references must be fully referenced through your XLApp
object and other objects.

I'd add a worksheet object and a workbook object to your code.

Dim xlWB As Object, xlWS As Object
Set xlWB = .Workbooks.Open(MyFile)
Set xlWS = xlWB.Worksheets(1)


For example, change this code (which I assume should have a dot (.) in front
of the Range):

Range("A1:AE1").Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

to this:

With xlWS.Range("A1:AE1").Interior
.ColorIndex = 15
.Pattern = xlSolid
End With


And another example -- change this code:
Range("AA1").Select
ActiveCell.FormulaR1C1 = Forms!FrmWeeklyReports!Month & " AGE"

to this:
With xlWS.Range("AA1")
.FormulaR1C1 = Forms!FrmWeeklyReports!Month & " AGE"
End With


Be sure to set xlWS to Nothing before you close the workbook, and set xlWB
to Nothing before you quit XLApp.

See this Knowledge Base article for more details about qualifying objects in
VBA automation:
Excel automation fails second time code runs
http://support.microsoft.com/kb/178510/en-us