Ghost excel process

[Keith Willis]

Hi All

I'm running the following code from within access, and it leaves a
ghost process open and i can't figure out why or how to get rid of it.
The process in question is from the globally declared "myExcel"
application object.

Public Sub GetERPTData()

Dim xlWBSource As Object
Dim xlWBDestination As Object
Dim Path1 As String
Dim Path2 As String
Dim Source_Path As String
Dim Destination_Path As String
Dim i As Long
Dim archPath As String

setVariables

Path1 = input_path
Path2 = input_path & "\" & blankTemplate
Desination_Path = Path2
Source_Path = ERPT_Path


Set myExcel2 = CreateObject("Excel.Application")
Set xlWBSource = myExcel.Workbooks.Open(Source_Path, , False)
Set xlWBDestination = myExcel2.Workbooks.Open(Desination_Path, ,
False)

'sort pivot tables

xlWBSource.Sheets(1).PivotTables("PivotTable2").PivotFields("EngArea").ClearAllFilters

xlWBSource.Sheets(1).PivotTables("PivotTable2").PivotFields("EngArea").CurrentPage
= _
"EMEIA"

xlWBSource.Sheets(1).PivotTables("PivotTable3").PivotFields("EngArea").ClearAllFilters

xlWBSource.Sheets(1).PivotTables("PivotTable3").PivotFields("EngArea").CurrentPage
= _
"EMEIA"

xlWBSource.Sheets(1).PivotTables("PivotTable4").PivotFields("EngArea").ClearAllFilters

xlWBSource.Sheets(1).PivotTables("PivotTable4").PivotFields("EngArea").CurrentPage
= _
"EMEIA"

xlWBSource.Sheets(1).PivotTables("PivotTable5").PivotFields("EngArea").ClearAllFilters

xlWBSource.Sheets(1).PivotTables("PivotTable5").PivotFields("EngArea").CurrentPage
= _
"EMEIA"

'find start position

xlWBSource.Sheets(1).Cells.Find(What:="Engagement Sub Area",
After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate


i = myExcel.ActiveCell.Row
i = i + 4

Do Until xlWBSource.Sheets(1).Cells(i, 1).Value = ""
xlWBSource.Worksheets(1).Activate


Select Case xlWBSource.Sheets(1).Cells(i, 1).Value

Case "Africa"
xlWBDestination.Sheets("Formulas").Range("C842").Value
= xlWBSource.Sheets(1).Cells(i, 2).Value
Case "BeNe"
xlWBDestination.Sheets("Formulas").Range("C843").Value
= xlWBSource.Sheets(1).Cells(i, 2).Value
Case "CIS"
xlWBDestination.Sheets("Formulas").Range("C844").Value
= xlWBSource.Sheets(1).Cells(i, 2).Value
Case "CSE"
xlWBDestination.Sheets("Formulas").Range("C845").Value
= xlWBSource.Sheets(1).Cells(i, 2).Value
Case "ETC"
xlWBDestination.Sheets("Formulas").Range("C854").Value
= xlWBSource.Sheets(1).Cells(i, 2).Value
Case "FSO"
xlWBDestination.Sheets("Formulas").Range("C847").Value
= xlWBSource.Sheets(1).Cells(i, 2).Value
Case "GSA"
xlWBDestination.Sheets("Formulas").Range("C848").Value
= xlWBSource.Sheets(1).Cells(i, 2).Value
Case "India"
xlWBDestination.Sheets("Formulas").Range("C849").Value
= xlWBSource.Sheets(1).Cells(i, 2).Value
Case "Mediterranean"
xlWBDestination.Sheets("Formulas").Range("C850").Value
= xlWBSource.Sheets(1).Cells(i, 2).Value
Case "MENA"
xlWBDestination.Sheets("Formulas").Range("C851").Value
= xlWBSource.Sheets(1).Cells(i, 2).Value
Case "Nordics"
xlWBDestination.Sheets("Formulas").Range("C852").Value
= xlWBSource.Sheets(1).Cells(i, 2).Value
Case "UK&I"
xlWBDestination.Sheets("Formulas").Range("C853").Value
= xlWBSource.Sheets(1).Cells(i, 2).Value
Case "FraMaLux"
xlWBDestination.Sheets("Formulas").Range("C846").Value
= xlWBSource.Sheets(1).Cells(i, 2).Value
End Select
i = i + 1
Loop



'find start position
xlWBSource.Sheets(1).Cells.Find(What:="Service Line",
After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate


i = myExcel.ActiveCell.Row
i = i + 4

Do Until xlWBSource.Sheets(1).Cells(i, 1).Value = ""
xlWBSource.Worksheets(1).Activate


Select Case xlWBSource.Sheets(1).Cells(i, 1).Value
Case "Advisory"
xlWBDestination.Sheets("Formulas").Range("C860").Value
= xlWBSource.Sheets(1).Cells(i, 2).Value
Case "Assurance"
xlWBDestination.Sheets("Formulas").Range("C861").Value
= xlWBSource.Sheets(1).Cells(i, 2).Value
Case "TAS"
xlWBDestination.Sheets("Formulas").Range("C862").Value
= xlWBSource.Sheets(1).Cells(i, 2).Value
Case "TAX"
xlWBDestination.Sheets("Formulas").Range("C863").Value
= xlWBSource.Sheets(1).Cells(i, 2).Value

End Select
i = i + 1
Loop

archPath = input_path & "\" & Format(Date, "yyyy-mm-dd") & " Input
Data Archive"
xlWBSource.saveas archPath & "\" & GetFilenameFromPath(Source_Path)

xlWBSource.Close savechanges:=False
Set xlWBSource = Nothing
DoEvents
xlWBDestination.Save
xlWBDestination.Close
Set xlWBDestination = Nothing
DoEvents
myExcel.DisplayAlerts = True
myExcel.Application.Quit
myExcel2.Application.Quit
Set myExcel = Nothing
Set myExcel2 = Nothing

 

[Keith Willis]

The problem was the active cell reference in

xlWBSource.Sheets(1).Cells.Find(What:="Service Line",
After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate

just needed to prefix it with the instance of excel i.e.
After:=myExcel.ActiveCell