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

 

[Rx_]

Jon- thanks for sticking with me on this.
Changed the long-winded PivotTable name change to the ordinal number (1) and verified that it works in the debug window. Plus the code following it does reference it properly.

By creating a goto Proc_SaveAS - and trying it before and after this line of code, it is easy to see that EXCEL is still running / or not in the Task Manager.

I am not as crazy as the reports claim.
But, I wil power down my computer now and re-try it. I don't get it at all.
Pasting my code again so you can see the GoTo Proc_SaveAS

' >>>>>>>>>>  *******************************************  Note ********** Add Cross Tab Code here  4/16/2012
                    ObjXL.ActiveWorkbook.Names.Add Name:="Data1", RefersToR1C1Local:=ObjXL.Range("A5").CurrentRegion  ' Set name rage Data1 to currentregion
                     'ObjXL.ActiveWorkbook.Names.Add Name:="Data1", RefersToRange:=ObjXL.Range("A5").CurrentRegion  ' Set name rage Data1 to currentregion (Mr. Excel suggestion)
                        ObjXL.Sheets("Sheet2").Select
                        ObjXL.ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
                            "=Data1", Version:=xlPivotTableVersion14).CreatePivotTable TableDestination _
                            :="Sheet2!R5C1", TableName:="PivotTable1", DefaultVersion:= _
                            xlPivotTableVersion14
                        ObjXL.Cells(5, 1).Select
                        ObjXL.Range("E4").Select
                        ObjXL.Sheets("Sheet2").Select
                        ObjXL.Cells(5, 1).Select
                        ObjXL.Sheets("Sheet2").Select
                        ObjXL.Sheets("Sheet2").Name = "AveragePermitTime"
                    ' exit to saveas and close to locate UNQUALIFIED Process
        'MsgBox "save as going to"
        'GoTo Proc_SAVEAS           ' Exit here and close then set to nothing - Excel shuts down OK in Task Manager if the NEXT LINE is NOT run
                        ObjXL.ActiveSheet.PivotTables(1).Name = "AverageDays_Area"  ' <<--- Keeps Excel process if this is run
        MsgBox "save as going to"  ' if exit after this line - and go through the same close process - Excel keeps running in Task manager UNQUALIFIED PROCESS every time
        GoTo Proc_SAVEAS
                        ' No errors anytime in the code running
                        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
' and much more - the pivot looks and works great - it is the EXCEL process that is still running that bothers me

 

[Rx_]

This was an all day event.
After copying and createing a new function, stripping off every thing, formatting, print setup and whatever was not needed.

This still does the same thing. Turning the Excel to visible, watching Excel and the Task Manager - it is 100% consistant
The comments are left in so that it can be run both ways. One leaves Excel.exe running, the other does not.

ObjXL.ActiveWorkbook.SaveAs FileName:=strNewReportPath
ObjXL.Visible = True
'ObjXL.Quit
'Set ObjXL = Nothing ' click Code RESET (stop code) - Excel.EXE goes away in Task Manager

ObjXL.Sheets(2).PivotTables(1).Name = "AverageDays_Area"
'ObjXL.ActiveSheet.PivotTables(1).Name = "AverageDays_Area" ' <<--- This was determined to keep Excel.EXE in Task Manager - tried Sheet(2) to see if it would make a difference.
DoEvents ' Workbook visible - sure enough PivotTable Name is updated
ObjXL.ActiveWorkbook.SaveAs FileName:=strNewReportPath
ObjXL.Visible = True
ObjXL.Quit ' Workbook closes
Set ObjXL = Nothing ' Click Code RESET (stop code) - Excel.EXE still in Task Manager

 

[Firefly2012]

So the workbook closes if you pass ObjXL.Quit, but the instance of Excel remains open? What happens if you pass ObjXL.Quit a second time and then set the reference to Nothing i.e.:

objXL.Quit   'first one
objXL.Quit   'second one
Set objXL = Nothing

Is this Excel instance still open in Task Manager?

 

[RoryA]

Or:

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

 

[Jon von der Heyden]

In addition to Rory's suggestion, try and tidy up your code such that you don't not select any ranges or sheets. Avoid calling ActiveSheet. Be as explicit as you can. All that selecting and calling of ActiveSheet is unnecessary.

Say you want to copy cell A1 on sheets(3), your current approach is e.g.,

Sheets(3).Select
Range("A1").Select
ActiveCell.Copy

...but really it needs only be:

Sheets(3).Range("A1").Copy

If you use e.g. OBJXL.Range(...), you actually haven't explicitly qualified the workbook nor have you qualified the sheet. It should be OBJXL.ActiveWorkbook.Sheets(1).Range(...) for instance.

Hope this makes sense.

I know this is solving any problem directly but (1) it makes it easier to test your code and (2) it may inadvertently solve a 'global' reference that is causing the issue.

 

[Rx_]

Let me thank everyone for some impressive responses. It is hard to imagine finding this many people that actually understand this problem. It is evern more impressive to have this many good suggestions.

To answer one question, even if running ObjXL.quit and set ObjXL = Nothing a second time - it is ignored. This is not a 2nd instance of Excel.EXE running, it is the first. I will test it twice in a row as you suggest.
I will also put in the other suggestions and test.

If I set ObjXL.Visible = True - it does in fact appear to close. While the task lives on.

If the code jumps to the routine to quit and kill the variable, befor this single line, it all behaves. Jumpping to the exact place after this line of code, it does not die.

I will attempt to reduce the code to as small as possible and post. I don't see a method on this site to attach a file.

Thanks again everyone.

 

[RoryA]

You can't attach files here but you can post it on a site like box.net or skydrive and then post a link here, if you'd like us to test the actual file.

 

[Rx_]

This is a single function with instructions. The problem is created 100% of the time on my workstation and on a Windows 2008 Server.

If this code is too long or big, let me know and I will come back and remove it.

Option Compare Database
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 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
10 If ObjXL Is Nothing Then
20 Set ObjXL = New Excel.Application
30 ObjXL.EnableEvents = False
40 Else
50 Excel.Application.Quit
60 On Error Resume Next
70 ObjXL.Quit

80 Set ObjXL = Nothing
90 DoEvents
100 Set ObjXL = New Excel.Application
110 ObjXL.EnableEvents = False
120 End If
130 ObjXL.Visible = True ' ******* change as needed
140 ObjXL.Workbooks.Add ' defaults to 3 sheets to the wind
150 strNewReportPath = "C:\" & Year(Now()) & "-" & Month(Now()) & "-" & Day(Now()) & "-" & Hour(Now()) & "-" & Minute(Now()) & "-" & "PivotTest.xlsx"
160 ObjXL.ActiveWorkbook.SaveAs FileName:=strNewReportPath
170 ObjXL.Sheets("Sheet1").Select
180 With ObjXL
190 .Range("A5").Select
200 .ActiveCell.FormulaR1C1 = "Dog"
210 .Range("B5").Select
220 .ActiveCell.FormulaR1C1 = "Cat"
230 .Range("C5").Select
240 .ActiveCell.FormulaR1C1 = "Owl"
250 .Range("D5").Select
260 .ActiveCell.FormulaR1C1 = "Mouse"
270 .Range("A6").Select
280 .ActiveCell.FormulaR1C1 = "1"
290 .Range("B6").Select
300 .ActiveCell.FormulaR1C1 = "2"
310 .Range("C6").Select
320 .ActiveCell.FormulaR1C1 = "3"
330 .Range("D6").Select
340 .ActiveCell.FormulaR1C1 = "4"
350 .Range("A7").Select
360 .ActiveCell.FormulaR1C1 = "9"
370 .Range("B7").Select
380 .ActiveCell.FormulaR1C1 = "8"
390 .Range("C7").Select
400 .ActiveCell.FormulaR1C1 = "7"
410 .Range("D7").Select
420 .ActiveCell.FormulaR1C1 = "6"
430 .Range("A8").Select
440 .ActiveCell.FormulaR1C1 = "3"
450 .Range("B8").Select
460 .ActiveCell.FormulaR1C1 = "4"
470 .Range("C8").Select
480 .ActiveCell.FormulaR1C1 = "5"
490 .Range("D8").Select
500 .ActiveCell.FormulaR1C1 = "6"
510 .Range("A9").Select
520 .ActiveCell.FormulaR1C1 = "7"
530 .Range("B9").Select
540 .ActiveCell.FormulaR1C1 = "6"
550 .Range("C9").Select
560 .ActiveCell.FormulaR1C1 = "5"
570 .Range("D9").Select
580 .ActiveCell.FormulaR1C1 = "4"
590 .Range("D10").Select
600 End With
610 ObjXL.DisplayAlerts = False
620 ObjXL.ActiveWorkbook.Names.Add Name:="Data1", RefersToR1C1Local:=ObjXL.Range("A5").CurrentRegion ' Set name rage Data1 to currentregion
'ObjXL.ActiveWorkbook.Names.Add Name:="Data1", RefersToRange:=ObjXL.Range("A5").CurrentRegion ' Set name rage Data1 to currentregion (Mr. Excel suggestion)
630 ObjXL.Sheets("Sheet2").Select
640 ObjXL.ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"=Data1", Version:=xlPivotTableVersion14).CreatePivotTable TableDestination _
:="Sheet2!R5C1", TableName:="PivotTable1", DefaultVersion:= _
xlPivotTableVersion14
650 ObjXL.Sheets("Sheet2").Select
660 ObjXL.Sheets("Sheet2").Name = "AveragePermitTime"
670 If Test Then
680 ObjXL.ActiveWorkbook.SaveAs FileName:=strNewReportPath
690 ObjXL.Quit
700 Set ObjXL = Nothing ' click Code RESET (stop code) - Excel.EXE goes away in Task Manager
710 If Err.Number = 0 Then ProcessTEST = True
720 Exit Function
' Excel.EXE is not in task manager everything closed as expected
730 End If
740 ObjXL.Sheets(2).PivotTables(1).Name = "AverageDays_Area"
'ObjXL.ActiveSheet.PivotTables(1).Name = "AverageDays_Area" ' <<--- Also Keeps Excel process if this is run
750 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
760 ObjXL.ActiveWorkbook.SaveAs FileName:=strNewReportPath
770 ObjXL.Visible = True
780 ObjXL.Quit ' Workbook closes
790 ObjXL.Quit ' As suggested - Excel.EXE still runs
800 Set ObjXL = Nothing ' Click Code RESET (stop code) - Excel.EXE still in Task Manager
810 If Err.Number = 0 Then
820 ProcessTEST = True
' Excel.EXE is still running in Task Manager
830 End If
840 Exit Function
End Function

 

[RoryA]

you need to change line 50 to:

objXL.Quit

to start with. Will test the rest shortly.

 

[Rx_]

You are very correct. Mine was commented out, sorry I let that pass. It appears that I can not edit that now.