VBA object causes Excel Application orphan

[Rx_]

The code accomlishes its purpose. But, the OBJXL (excel object) is orphaned and refuses to be destryoed with Set ObjXL = Nothing

Any suggestions would be welcome

Without the OBJXL qualifier in front of .Range - it is an error.

With the qualifier: <OBJEVCT Set Not Variable Block With or>
ObjXL.ActiveWorkbook.Names.Add Name:="Data1", RefersToR1C1Local:=ObjXL.Range("A5").CurrentRegion ' Set name rage Data1 to currentregion

This is set up for a PivotCaches:
ObjXL.ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"=Data1", Version:=xlPivotTableVersion14).CreatePivotTable TableDestination _
:="Sheet2!R5C1", TableName:="PivotTable1", DefaultVersion:= _
xlPivotTableVersion14

 

[Norie]

I changed the code a bit and as far as I can see this doesn't leave a ghost instance of Excel floating about.

Option Compare Database
Option Explicit
 
Sub The()
    ProcessTEST False
    
End Sub

Public Function ProcessTEST(Test As Boolean) As Boolean
' Past this function in MS Access 2010 code module
' in Tools Reference - set a reference to MS Excel 2010
' In Immediate Window - type in either ? ProcessTest(TRUE) or ? ProcessTEST(FASLE)
Dim ObjXL As Excel.Application
Dim objWB As Excel.Workbook
Dim objWS As Excel.Worksheet
Dim strNewReportPath As String    ' for directory to save
Dim intWorksheetNum As Integer
Dim intRowNumber As Integer
Dim intColumnNumber As Integer
Dim intRowPos As Integer
Dim intHeaderColCount As Integer
Dim intMaxheaderColCount As Integer
Dim strSaveAsFileName As String    ' the name with time stamp to save this report

    Set ObjXL = CreateObject("Excel.Application")
    ObjXL.Visible = True    ' ******* change as needed
    Set objWB = ObjXL.Workbooks.Add    ' defaults to 3 sheets to the wind
    strNewReportPath = "C:\" & Year(Now()) & "-" & Month(Now()) & "-" & Day(Now()) & "-" & Hour(Now()) & "-" & Minute(Now()) & "-" & "PivotTest.xlsx"
    objWB.SaveAs FileName:=strNewReportPath
    Set objWS = ObjXL.Sheets("Sheet1")
    With objWS
        .Range("A5").Value = "Dog"
        .Range("B5").Value = "Cat"
        .Range("C5").Value = "Owl"
        .Range("D5").Value = "Mouse"
        .Range("A6").Value = "1"
        .Range("B6").Value = "2"
        .Range("C6").Value = "3"
        .Range("D6").Value = "4"
        .Range("A7").Value = "9"
        .Range("B7").Value = "8"
        .Range("C7").Value = "7"
        .Range("D7").Value = "6"
        .Range("A8").Value = "3"
        .Range("B8").Value = "4"
        .Range("C8").Value = "5"
        .Range("D8").Value = "6"
        .Range("A9").Value = "7"
        .Range("B9").Value = "6"
        .Range("C9").Value = "5"
        .Range("D9").Value = "4"
    End With
    ObjXL.DisplayAlerts = False
    objWB.Names.Add Name:="Data1", RefersToR1C1Local:=objWS.Range("A5").CurrentRegion    ' Set name rage Data1 to currentregion
    Set objWS = objWB.Sheets("Sheet2")
    objWB.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
                             "=Data1", Version:=xlPivotTableVersion14).CreatePivotTable TableDestination _
                             :="Sheet2!R5C1", TableName:="PivotTable1", DefaultVersion:= _
                             xlPivotTableVersion14
    objWS.Name = "AveragePermitTime"
    If Test Then
        objWB.Close SaveChanges:=True
        ObjXL.Quit
        Set ObjXL = Nothing    ' click Code RESET (stop code) - Excel.EXE goes away in Task Manager
        If Err.Number = 0 Then ProcessTEST = True
        Exit Function
        ' Excel.EXE is not in task manager everything closed as expected
    End If
    ObjXL.Sheets(2).PivotTables(1).Name = "AverageDays_Area"
    'ObjXL.ActiveSheet.PivotTables(1).Name = "AverageDays_Area" ' <<--- Also Keeps Excel process if this is run
    DoEvents    ' Workbook visible - sure enough PivotTable Name is updated
    ' under PivotTable Tools - Options - then on menu PivoTable Name: see the new name AverageDays_Area is in fact updated
    ObjXL.ActiveWorkbook.SaveAs FileName:=strNewReportPath
    ObjXL.ActiveWorkbook.Close
    '770 ObjXL.Visible = True
    ObjXL.Quit    ' Workbook closes
    'ObjXL.Quit ' As suggested - Excel.EXE still runs
    Set ObjXL = Nothing    ' Click Code RESET (stop code) - Excel.EXE still in Task Manager
    If Err.Number = 0 Then
        ProcessTEST = True
        ' Excel.EXE is still running in Task Manager
    End If
    Exit Function
End Function

 

[Rx_]

Thank you very, very much for testing. Let me ask you a couple of questions:
You did test both ways, passing in a True and a False into the function call?

What version of MS Office including any SP are you running?
Mine is 32 bit Excel.

Thanks - My result of leaving or not leaving a ghost Excel.ECE are 100% of the time.

 

[Norie]

I tested both with True and False and I'm using 32-bit Excel (SP1).

In fact I just tested it 100 time with False, and there was no instance of Excel left at the end.

Thought I better do the same for True, and again no left over instance of Excel.

 

[Rx_]

Very well done indeed! Thank you so very much again.
The Excel version at this worksite is 14.0.4763.1000

 

[Norie]

Here I'm using version 14 too, can't seem to find the rest of the verision no but what you've posted is familiar.

 

[Rx_]

This indicates that neither my workstation nor the server have the Office 2010 SP1 installed. The update history indicates this to be the case.
After searching Microsoft for the version numbers, mine appears to indicate this too.

At this point, there is only one crosstab report that has infrequent use. Will have to elevate this question about installing the Office service packs through channels.

When looking at the service pack updates last year, this problem was not on the list.

Would you suggest starting a new thread or is there already one for issues related to the Office SP? On the Access 2010 site, there were some significant problems introduced for Access programmers.
Have there been any issues noted for Excel in the Office 2010 SP?

 

[Norie]

So the code I posted still leaves a ghost instance of Excel?

 

[Rx_]

Bravo! Your code does NOT leave a ghost.
objWB - I didn't catch the change in your code from mine.
So sorry, it was the end of a long week. Very subtle, but the result is huge.

Your code does not leave an excel ghost
my code does leave a ghost 100% of the time (testing with False).

Thank you so much. I will alter my large code to take this into account.
The Workbook is something that is used in many dozens of my other reports and it has never caused a problem until this process.

Your understanding of this problem and help is very appreciated!
There are few people out there that could have understood this, much less see it through.