Orphan Excel Object when setting Current region

[Rx_]

The code accomlishes its purpose. But, the OBJXL (excel object) is orphaned and refuses to be destryoed with Set ObjXL = Nothing
Please Note Update: The problem of a ghost excel.exe remaining in task manager was identified when assigning a name to the pivot table.
Any suggestions would be welcome

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

With the qualifier:
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

 

[vbaInet]

You need to close the workbook. I'm guessing it's a new Excel instance so:

ObjXL.Thisworkbook.Close
Set ObjXL = Nothing

If it's not then replace Thisworkbook with Activeworkbook.

 

[Rx_]

Thanks. That does not fix it for Excel 2010.
Narrowed it down to code posted at:
http://www.mrexcel.com/forum/showthread.php?p=3128247#post3128247
The orphan or ghost only happens if the PivotTable was named

ObjXL.Sheets(2).PivotTables(1).Name = "AverageDays_Area"

The solution was verified on page 3 of the post listed above.
It stumped me. And, it is unique to the PivotTable.
The code is posted in a function where the Pivot Table can be renamed or not renamed. Feel free to take a look at it.

 

[vbaInet]

So what was the solution?

The code in your original post did not suggest that you had a objWB object created for that workbook. If you close it with that object it should get rid of the instance.

 

[Rx_]

Your right. It should have. A lot of people looked at it and said the same thing. That is why I wrote the function and posted it on Mr. Excel's site so others could validate it - The function runs same code with the only difference of assigning the pivot a name or leaving the pivot table with the default name.
'ObjXL.ActiveSheet.PivotTables(1).Name = "AverageDays_Area"
If the Pivot table is not named (from its default), there is no ghost.


For what ever reason, the CreateObject makes a difference.
I have dozens of Excel coded reports using a basic code template that leaves no ghost. This was the first Pivot table code in a report.
To make my code more readable the pivot table was assigned a name. The code runs flawlessly with the exception of naming a pivot table.
But, the line where the pivot was assigned a name created the ghost problem.

instead of: Set ObjXL = New Excel.Application ' Post 18 on page 2

' excel 2010 32bit no SP1 - running from Access 2010
' Norie's solution on page 3 works with out a ghost and renames pivot table
http://www.mrexcel.com/forum/showthread.php?t=629763&page=3
Set ObjXL = CreateObject("Excel.Application")
ObjXL.Visible = True ' ******* not part of the solution
Set objWB = ObjXL.Workbooks.Add

 

[vbaInet]

Alright, I didn't know about that because I always give my pivot tables names so I obviously haven't come across that problem.

Did you really need a new Excel application though? GetObject() should have been sufficient if you didn't.

 

[Rx_]

LOL - the probem came from giving the Pivot Table a name.
Just wondering, do you assign the pivot table a name at creation?
Maybe that prevents the problem.
My code add a pivot table, with the default name. The ghost problem occures when the pivot table was assigned a new name.

I have always used Set ObjXL = New Excel.Application
with no problems until assigning a pivot table a new name.
If set objxl is replaced with the GetObject code, assigning a pivot table a new name does not leave a ghost instance.

I would be interested if you could share your code to give your pivot table a name, I would like to see if it makes a difference.

 

[vbaInet]

I create the pivot cache then create a table from it. I don't have any specific code but it goes something like this:

PivotCache.CreatePivotTable(TableDestination:=SomeTable, TableName:="PivotTableName")

Where PivotCache represents your pivot cache object already created.

Perhaps you could just go with GetObject() because most of the time we never need to create a new instance of Excel.

Set objWb = GetObject("Path to my xls")

 

[Rx_]

Thanks, tomorrow I am training staff for a new production SQL Server.
What my code did was add a pivot - with default name
then re-name the default to a name
That caused the ghost instance.
Let me be clear on this. It was not a 2nd instance of Excel.exe, the currently running instance just would not let itself be destroyed.

After a project milestone, I will come back and post code that can be run as a function. The function can re-produce the problem.

I apologize for having to wait, it is going to be a long weekend for me.