Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ghost excel process
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").Pi votFields("EngArea").ClearAllFilters xlWBSource.Sheets(1).PivotTables("PivotTable2").Pi votFields("EngArea").CurrentPage = _ "EMEIA" xlWBSource.Sheets(1).PivotTables("PivotTable3").Pi votFields("EngArea").ClearAllFilters xlWBSource.Sheets(1).PivotTables("PivotTable3").Pi votFields("EngArea").CurrentPage = _ "EMEIA" xlWBSource.Sheets(1).PivotTables("PivotTable4").Pi votFields("EngArea").ClearAllFilters xlWBSource.Sheets(1).PivotTables("PivotTable4").Pi votFields("EngArea").CurrentPage = _ "EMEIA" xlWBSource.Sheets(1).PivotTables("PivotTable5").Pi votFields("EngArea").ClearAllFilters xlWBSource.Sheets(1).PivotTables("PivotTable5").Pi votFields("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").V alue = xlWBSource.Sheets(1).Cells(i, 2).Value Case "BeNe" xlWBDestination.Sheets("Formulas").Range("C843").V alue = xlWBSource.Sheets(1).Cells(i, 2).Value Case "CIS" xlWBDestination.Sheets("Formulas").Range("C844").V alue = xlWBSource.Sheets(1).Cells(i, 2).Value Case "CSE" xlWBDestination.Sheets("Formulas").Range("C845").V alue = xlWBSource.Sheets(1).Cells(i, 2).Value Case "ETC" xlWBDestination.Sheets("Formulas").Range("C854").V alue = xlWBSource.Sheets(1).Cells(i, 2).Value Case "FSO" xlWBDestination.Sheets("Formulas").Range("C847").V alue = xlWBSource.Sheets(1).Cells(i, 2).Value Case "GSA" xlWBDestination.Sheets("Formulas").Range("C848").V alue = xlWBSource.Sheets(1).Cells(i, 2).Value Case "India" xlWBDestination.Sheets("Formulas").Range("C849").V alue = xlWBSource.Sheets(1).Cells(i, 2).Value Case "Mediterranean" xlWBDestination.Sheets("Formulas").Range("C850").V alue = xlWBSource.Sheets(1).Cells(i, 2).Value Case "MENA" xlWBDestination.Sheets("Formulas").Range("C851").V alue = xlWBSource.Sheets(1).Cells(i, 2).Value Case "Nordics" xlWBDestination.Sheets("Formulas").Range("C852").V alue = xlWBSource.Sheets(1).Cells(i, 2).Value Case "UK&I" xlWBDestination.Sheets("Formulas").Range("C853").V alue = xlWBSource.Sheets(1).Cells(i, 2).Value Case "FraMaLux" xlWBDestination.Sheets("Formulas").Range("C846").V alue = 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").V alue = xlWBSource.Sheets(1).Cells(i, 2).Value Case "Assurance" xlWBDestination.Sheets("Formulas").Range("C861").V alue = xlWBSource.Sheets(1).Cells(i, 2).Value Case "TAS" xlWBDestination.Sheets("Formulas").Range("C862").V alue = xlWBSource.Sheets(1).Cells(i, 2).Value Case "TAX" xlWBDestination.Sheets("Formulas").Range("C863").V alue = 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ghost excel process
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ghost of Excel | Excel Programming | |||
How to count process running time ( process not finished) | Excel Programming | |||
How to count process running time ( process not finished) | Excel Programming | |||
Mail merge issue - ghost Excel process remains after closing application | Excel Programming | |||
Mail merge issue - ghost Excel process remains after closing application | Excel Programming |