Attention boblarson

[elliotgr]

Hi, I believe you had a similar problem. Manipulating Excel, I get an Excel process which I can't shut down. All the references to close the file and quit Excel are there, and they are set to nothing.
Everything is explicity referenced so there should be no instance hanging.
I use similar code in another Access application and it works correctly.
Did you ever find a solution?
Glen

 

[vbaInet]

It might take a while before Bob gets to you so I'll chime in in the meantime.

Did you close the workbook? Setting it to Nothing is not sufficient.

 

[boblarson]

Hi, I believe you had a similar problem. Manipulating Excel, I get an Excel process which I can't shut down. All the references to close the file and quit Excel are there, and they are set to nothing.
Everything is explicity referenced so there should be no instance hanging.
I use similar code in another Access application and it works correctly.
Did you ever find a solution?
Glen

Post the entire procedure you are using (and any procedures that procedure runs as well) so we can see what you have. Without that, I can't tell you. But it is likely you missed something.

 

[vbaInet]

Ah... it wasn't that long then Over to you Bob.

 

[boblarson]

Ah... it wasn't that long then Over to you Bob.

I've been fortunate to have had some time to come in and answer some questions. Don't know how long that window will last, so you never know.

 

[elliotgr]

Hi

I had to do a 'SaveAs'. Doing the '.close true' did not remove the Excel App from Task Manager.

Code changed from

COMPLETION_xlWBa.Close True
Set COMPLETION_xlWBa = Nothing
 
COMPLETION_objExcel1.DisplayAlerts = True
 
COMPLETION_objExcel1.Quit
Set COMPLETION_objExcel1 = Nothing

to

COMPLETION_xlWBa.SaveAs FileName:=strFileName, FileFormat:= _
xlWorkbookDefault, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
 
COMPLETION_xlWBa.Close True
Set COMPLETION_xlWBa = Nothing
 
COMPLETION_objExcel1.DisplayAlerts = True
 
COMPLETION_objExcel1.Quit
Set COMPLETION_objExcel1 = Nothing

Maybe a Win7 /Excel 2007 conflict?

 

[vbaInet]

Post the entire procedure you are using (and any procedures that procedure runs as well) so we can see what you have. Without that, I can't tell you. But it is likely you missed something.

I'm sure Bob is interested in the entire procedure.

 

[elliotgr]

I have it working, but then I got random results, with the Excel app not finishing the pivot table creation properly all the time.

The problem was

COMPLETION_xlWBa.Worksheets("Sheet3").PivotTables("PivotTable2").AddDataField [B][I]ActiveSheet[/I][/B].PivotTables( _
        "PivotTable2").PivotFields("VALID_ANALYSIS%"), "Count of VALID_ANALYSIS%", _
        xlCount

Every now and again it appears the ActiveSheet reference was lost, even though it appeared in the locals window as correct. Holding the cursor over the ActiveSheet command showed 'connection to computer lost'.

I changed to code to explicitly reference the ActiveSheet and it is now reliable.

COMPLETION_xlWBa.Worksheets("Sheet3").PivotTables("PivotTable2").AddDataField [B][I]COMPLETION_xlWBa.Worksheets("Sheet3").[/I][/B]PivotTables( _
        "PivotTable2").PivotFields("VALID_ANALYSIS%"), "Count of VALID_ANALYSIS%", _
        xlCount

Thanks for the help. This is now resolved.

 

[vbaInet]

Activesheet is not a reliable object for referring to the object you're using. You need explicit references.

 

[elliotgr]

I did a bit of copy/paste from Excel to save time and this slipped thru. Would like to have pasted the code, but it's too large, even for a zip file.
I'm going to write a snippet in future to validate the Excel code portion for non-explicit references.
Despite going thru the code for 3 days, I missed the ActiveWorkSheet

 

[vbaInet]

It would be safe to search for words like:

ActiveSheet
ThisWorkbook
ActiveWorkbook

Maybe you need another pair of eyes at work to look through it too.

 

[boblarson]

As I said, if you post the entire procedure we may spot somewhere that you have used something which is not tied back to the application object.

Read this if you haven't already:
http://www.btabdevelopment.com/ts/excelinstance