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

 

[karenspires]

Re: VBA object causes Excel Applicaiton orphan

2 or more cells that are adjacent to each other?

 

[Firefly2012]

Re: VBA object causes Excel Applicaiton orphan

Do you Quit the ObjXL first i.e.:

ObjXL.Quit
 
Set ObjXL = Nothing

Simply setting the reference to Nothing won't remove the object from memory.

 

[Jon von der Heyden]

Re: VBA object causes Excel Applicaiton orphan

I'm trying to understand the question, because I'm not sure of the relevance of the code excerpts. Do you mean it is orphaned after you attempt to destroy the object variable using:

Set OBJXL = Nothing

Did you quit the application before destroying the variable?

'you may want to save and close open workbooks 1st
OBJXL.Quit
Set OBJXL = Nothing

 

[Rx_]

Yes, after successfully saving the workbook,
3250 ObjXL.Quit
3260 Set ObjXL = Nothing
3270 Set rsReclMonDt = Nothing

The Task Manager shows Excel still happy and runnng.
If the code is re-run, a second Excel is still running in Task manager.

My code template works with out the orphane on around four dozen other excel reports with out any orphan.

3120        If CurrentUser <> "Admin" And CurrentUser <> "SomeUSERNAME" And blnTestMode = False Then
3130            ObjXL.ActiveWorkbook.SaveAs FileName:=strNewReportPath
3140        End If
' *******************************************  Note ********** Add Cross Tab Code here  2/6/2012
                    ObjXL.ActiveWorkbook.Names.Add Name:="Data1", RefersToR1C1Local:=ObjXL.Range("A5").CurrentRegion  ' Set name rage Data1 to currentregion
 
                        ObjXL.Sheets("Sheet2").Select
                        ObjXL.ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
                            "=Data1", Version:=xlPivotTableVersion14).CreatePivotTable TableDestination _
                            :="Sheet2!R5C1", TableName:="PivotTable1", DefaultVersion:= _
                            xlPivotTableVersion14
                        'ObjXL.Sheets("Sheet2").Select
                        ObjXL.Cells(5, 1).Select
                        ObjXL.Range("E4").Select
 
                        'ObjXL.ActiveWorkbook.Worksheets("Sheet2").PivotTables("PivotTable1").PivotCache. _
                            .CreatePivotTable TableDestination:="Sheet2!R5C1", TableName:="PivotTable2" _
                            , DefaultVersion:=xlPivotTableVersion14
                        ObjXL.Sheets("Sheet2").Select
                        ObjXL.Cells(5, 1).Select
                        ObjXL.Sheets("Sheet2").Select
                        ObjXL.Sheets("Sheet2").Name = "AveragePermitTime"
                        ObjXL.ActiveSheet.PivotTables("PivotTable1").Name = "AverageDays_Area"
                        With ObjXL.ActiveSheet.PivotTables("AverageDays_Area")
                            .InGridDropZones = True
                            .RowAxisLayout xlTabularRow
                        End With
                        With ObjXL.ActiveSheet.PivotTables("AverageDays_Area").PivotFields("Area")
                            .Orientation = xlRowField
                            .Position = 1
                        End With
                        ObjXL.Range("A6").Select
                        With ObjXL.ActiveSheet.PivotTables("AverageDays_Area").PivotFields("Area")
                            .Orientation = xlPageField
                            .Position = 1
                        End With
                        With ObjXL.ActiveSheet.PivotTables("AverageDays_Area").PivotFields( _
                            "Regulatory Office")
                            .Orientation = xlPageField
                            .Position = 1
                        End With
                        With ObjXL.ActiveSheet.PivotTables("AverageDays_Area").PivotFields("ST")
                            .Orientation = xlPageField
                            .Position = 1
                        End With
                        With ObjXL.ActiveSheet.PivotTables("AverageDays_Area").PivotFields("Status")
                            .Orientation = xlPageField
                            .Position = 1
                        End With
' Later in code
3170        ObjXL.ActiveWorkbook.SaveAs FileName:=strSaveAsFileName
3180        'ObjXL.Visible = False
            ObjXL.DisplayAlerts = True
' later in code
3250    ObjXL.Quit
3260    Set ObjXL = Nothing
3270    Set rsReclMonDt = Nothing
 
' The Task Manager shows the Excel object still in memory.
' My other reports  (with same output except no PivotTable) closes fine.

The Pivot table is completed just fine.
I was testing the code on citrix and watching processor timing when I noticed this one report would leave an orphaned Excel running each time run.
It runs fine, with out errors. It is just bad luck to leave orphans running around the server unsupervised. (smile)

 

[Jon von der Heyden]

Oh I see what you mean. Read this.

Edit: The article I refer you to suggests this is likely if you have unqualified references. I don't see any unqualified references in your code but you haven't posted your entire code so it still may be the cause of the problem.

 

[Firefly2012]

Oh I see what you mean. Read this.

That's a great article Jon - very informative and interesting. I'm going to read the referenced MS article now too

 

[RoryA]

Shouldn't this:

ObjXL.ActiveWorkbook.Names.Add Name:="Data1", RefersToR1C1Local:=ObjXL.Range("A5").CurrentRegion  ' Set name rage Data1 to currentregion

be this:

ObjXL.ActiveWorkbook.Names.Add Name:="Data1", RefersToRange:=ObjXL.Range("A5").CurrentRegion  ' Set name rage Data1 to currentregion

 

[Rx_]

Found it - The new Pivot Table is named PivotTable1 - The lines before this one work perfectlly if code jumps to saveas and close

ObjXL.ActiveSheet.PivotTables("PivotTable1").Name = "AverageDays_Area"
But, I am not sure how to fix this?

It does take the name "AverageDays_Area" and us used later in code:
e.g. With ObjXL.ActiveSheet.PivotTables("AverageDays_Area").PivotFields("ST")

' orphan not here
                        ObjXL.Sheets("Sheet2").Select
' orphane not here
 
                        ObjXL.Cells(5, 1).Select
                        ObjXL.Sheets("Sheet2").Select
' orphane not here
                        ObjXL.Sheets("Sheet2").Name = "AveragePermitTime"
 
 ' orphane not here
                        ObjXL.ActiveSheet.PivotTables("PivotTable1").Name = "AverageDays_Area"  ' HERE IT IS
      ' exit to saveas and close to locate orphan
        GoTo Proc_SAVEAS:
 
' orphan here

Jon - great article - Exactly my thoughts too - Can usually find these, but not in this case. So, I pasted a exit criteria and tried it in several code locations until this one line of code exposed itself as the problem.

Re to suggestion : RefersToRange:=
In Excel 2010, copied / pasted this in rechecked, this gets a compile error
"named Argument not found" Using the F2 Object Browser, sure enough it is does exist. It just won't compile for me.

 

[Jon von der Heyden]

I don't know what's going on if I'm honest. I don't see any unqualified reference at that point.