Excel Automation Objects Garbage Collection

This is a follow-up question to one that was resolved yesterday (Q_27863723)
To recap:
I am working on an Access 2003 mdb on an XP platform. I have a batch process that imports Excel workbooks into the mdb. For each workbook in a user-specified folder, I call a subroutine which reformats some columns along with some other processing.
I ran into an error where the subroutine would run without error the first time through. On the second iteration, though, it would abend with error 91, Object variable or With block variable not set, on a line with that referred to an ‘unqualified’ Excel Selection object (i.e. “Selection” as opposed to “.Selection”).
I don’t want to get into a discussion about the merits on the usage of an Excel Selection object without qualifying it as a member of another object. I now recognize it is poor form and will avoid the practice in the future.
But, I remained curious as to why it is that the code would work the first time through, but not the second. It suggests to me that there is some loose end out there in the Access heap.
I added some additional code to the Clean up section of the sub; setting the Selection object to nothing and executing a TASKKILL to force the Excel instance out of the stack.
I am now getting some different results. Instead of an error 91, I am now getting an error 462, The remote server machine does not exist or is unavailable.
But here is what I find most intriguing: I recreated the mdb on my laptop which is running Access 2010 (32 bit) on a Windows 7 Ultimate (64 bit) platform (I am unable to connect to Experts-Exchange from my client’s machine). When I run the code on this machine, it doesn’t get any errors!
So, are these ‘loose ends’ a problem that has resolved since Access 2003? Unfortunately, I don’t currently have a box running Access 2007. Is this fixed in Access 2007? What exactly is happening here?
Can anyone offer some insight into this?
I am including a zip file containing an mdb and 3 small workbooks. The app is set up to look for the workbooks in the same folder as the mdb. To execute, open up the module, and run the FormatBooks subroutine.
Please note that although the mdb was created in Access 2003 format, the references are pointing towards Office 14 Object Libraries. You will have to change the references if you are using an earlier version of MS Office.
Here’s the code that is in the module


Option Explicit
Option Compare Database
Private Const mstrModule As String = "basFormatBooks"
Private Declare Function GetWindowThreadProcessId Lib "user32" (ByVal hwnd As Long, lpdwprocessid As Long) As Long

Public Sub FormatBooks()
      Const strProcedure As String = "FormatBooks"
      Dim strFolder As String
      Dim db As DAO.Database
      Dim strFile As String
      Dim strBook As String
10    On Error GoTo ErrorHandler
20    DoCmd.SetWarnings False
30    strFolder = CurrentProject.Path & "\"
40    Set db = CurrentDb
50    With db
60       With .OpenRecordset("tblInput")
70          While Not .EOF
80             strBook = .Fields(0)
90             strFile = strFolder & strBook
100            Debug.Print "Starting " & strBook
110            FormatBook strFile
120            Debug.Print "Finished " & strBook
130            .MoveNext
140         Wend
150      End With
160   End With
170   Debug.Print "DONE!"
180   On Error Resume Next
190   db.Close
200   Set db = Nothing
210   On Error GoTo 0
220   Exit Sub

230   Debug.Print strProcedure, Err, Err.Description, Erl
240   Resume ExitFunction
End Sub

Private Sub FormatBook(strFile As String)
      Const strProcedure As String = "FormatBook"
      Dim xlsSheet As Excel.Worksheet
      Dim xlsApp As Excel.Application
      Dim xlsBook As Excel.Workbook
      Dim xlsRange As Excel.Range
      Dim lngPID As Long
10    On Error GoTo ErrorHandler

20    Set xlsApp = New Excel.Application
      ' Get ProcessID for Excel instance
30    GetWindowThreadProcessId xlsApp.hwnd, lngPID

40    With xlsApp
50    Set xlsBook = .Workbooks.Open(strFile, False, False)
60       With xlsBook
70          Set xlsSheet = .Worksheets(1)
80          With xlsSheet
90             Set xlsRange = .Columns("A:A")
100            With xlsRange
110               .Select
120               Selection.NumberFormat = "0"   '<== Error on this line
130            End With          'With xlsRange
140         End With             'With xlsSheet
150      End With                'With xlsBook
160   End With                   'With xlsApp

170   On Error Resume Next
180   xlsBook.Close False
190   Set xlsRange = Nothing
200   Set xlsSheet = Nothing
210   Set xlsBook = Nothing
220   Set Selection = Nothing
230   xlsApp.Quit
240   Set xlsApp = Nothing
250   Shell "TASKKILL /F /pid " & lngPID, vbHide
260   DoEvents
270   Err.Clear
280   Exit Sub

290   DoCmd.Hourglass False
300   Debug.Print strProcedure & " Error: " & Err.Description & " (" & Err & ") on line " & Erl
310   Resume ExitSub
End Sub
Who is Participating?
Rey Obrero (Capricorn1)Commented:
<Selection' is an Excel object.>
<When the Selection object is first used in an Access instance, it works OK.>


but since it is not qualified with either
          xlsRange, xlsSheet,  xlsBook, xlsApp

it is ('Selection' object) global to the calling program (access) , this global reference to an excel object forces excel to stay active.

to avoid this, make sure that every property, method, object within the automated application is referenced through an explicit variable (   xlsRange, xlsSheet,  xlsBook, xlsApp ) declared in the calling program.

hope this explains the cause of the problem..

let me repeat,

i never used any additional codes to kill the excel automation processing in my applications.
only these
set xlObj=nothing
Rey Obrero (Capricorn1)Commented:
test this codes

Private Sub LoadTPR(strFile As String)
      Const strProcedure As String = "LoadTPR"
      Dim xlObj as Object

      Dim strCsv As String
    On Error GoTo ErrorHandler
    strCsv = CurrentProject.Path & "\TPR.csv"
      ' Delete csv file
    On Error Resume Next
    Kill strCsv
    On Error GoTo ErrorHandler
	set xlObj=createobject("excel.application")
		xlObj.workbooks.open strFile, False, False
		xlObj.visible=true  ' if you don't want to see the excel application, just comment this line
			with xlObj
			    .activeworkbook.saveas strcsv,FileFormat:=6, createbackup:=false
			end with
			set xlObj=nothing
   DoCmd.SetWarnings False
   DoCmd.TransferText acImportDelim, "TPR Import Specification", "tblTPR", strCsv
	DoCmd.SetWarnings True

		set xlObj=nothing
        exit sub

   DoCmd.Hourglass False
   Debug.Print strProcedure & " Error: " & Err.Description & " (" & Err & ") on line " & Erl
   Resume ErrExit
End Sub

Open in new window

shambaladAuthor Commented:
That code works (once I added an "On error Resume Next" directly after the ErrExit label). I expected that. But that isn't cogent to the question I'm asking here. I'm trying to figure out why it is that the line "Selection.NumberFormat = 0" (which is line 120 in the FomatBook sub list above in the first post runs OK the first time the sub is executed, but is then hammered for the for rest of that Access instance.
FWIW, I'm now getting an error on that line on the laptop too (it's these sorts of things that drive me a little crazy). By the way, it is the addition of the TASKKILL command that changes the error from 91 to 462.
I want to try this as an ACCDB, too, but I've got a hot issue my client just handed me, which I have to turn my attention to.
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

shambaladAuthor Commented:
Just converted the mdb to an ACCDB. Still getting a 462 error on the 2nd iteration.
Rey Obrero (Capricorn1)Commented:
the line

"Selection.NumberFormat = 0"

does not have a valid reference to the object variable that you used in your codes.

i never used any additional codes to kill the excel automation processing in my applications.

if you haven't notice i only used one object variable (xlObj)


try this in your codes, add a dot (.) before Selection

100            With xlsRange
110               .Select
120               .Selection.NumberFormat = "0"   '<== Error on this line
130            End With          'With xlsRange

or this one

100            With xlsRange
110               .Select
120               xlsApp.Selection.NumberFormat = "0"   '<== Error on this line
130            End With          'With xlsRange
shambaladAuthor Commented:
This is not the point. The question is why does it work the first time the subroutine is called (and it can be used multiple times in that first call) and not the second time the subroutine is called?
Rey Obrero (Capricorn1)Commented:
close your access application, any excel application
open your Task manager
* in the process Tab, you will not see any Excel.exe and msaccess.exe that is running

open your Access app (msaccess.exe will show in the ask Manager), then run your codes (Excel.exe will show in the Task manager)
after your codes run, since you have close and quit xlsApp,  Excel.exe should be gone from the Task manager processes, but that is not the case ;-(

at this point, try opening any excel file and see what happen...

shambaladAuthor Commented:
OK. Perhaps I should restate what the thesis is here.
'Selection' is an Excel object.
When the Selection object is first used in an Access instance, it works OK.
When I am done working with the Excel objects, I attempt to remove them from the stack and heap using various techniques such as the Quit method, setting the pointers to nothing and using the TASKKILL command. I also added a DoEvents to allow Access to perform any tasks it may have been holding in abeyance.
After taking these steps, it would appear that everything having to do with that instance of Excel has been removed from the Access stack and heap,
If I try to use the Selection object again in that same Access session, however, it raises an error,
This suggests to me that I have not been successful in disposing all of the garbage from my previous instance of Excel within that session.
This question is about how Access implements the Garbage Collector. I know that if I avoid using the Selection object in the way it is coded in the first post, I won’t get the error. But what I want to know is how I can dispose of this Excel object or any other object I’ve created in an Access so that there are no traces at all left behind.
shambaladAuthor Commented:
OK, I'll buy that. It's a scoping error (global within a private sub) that slips through the cracks of the object model the first time around.
As an addendum to this (at the risk of appearing to flog a dead horse), the following does work when executed as a module within a workbook:

Private Sub formatbooks()
      Dim strFile As String
10    strFile = "C:\Users\Todd\Desktop\Demo1.xlsx"
20    FormatBook strFile
30    strFile = "C:\Users\Todd\Desktop\Demo2.xlsx"
40    FormatBook strFile
50    strFile = "C:\Users\Todd\Desktop\Demo3.xlsx"
60    FormatBook strFile
End Sub

Private Sub FormatBook(strFile As String)
      Const strProcedure As String = "FormatBook"
      Dim xlsSheet As Excel.Worksheet
      Dim xlsBook As Excel.Workbook
      Dim xlsRange As Excel.Range
10    On Error GoTo ErrorHandler
20    Set xlsBook = Application.Workbooks.Open(strFile, False, False)
30       With xlsBook
40          Set xlsSheet = .Worksheets(1)
50          With xlsSheet
60             Set xlsRange = .Columns("A:A")
70             With xlsRange
80                .Select
90                Selection.NumberFormat = "0"
100            End With          'With xlsRange
110         End With             'With xlsSheet
120      End With                'With xlsBook

130   On Error Resume Next
140   xlsBook.Close False
150   Set xlsRange = Nothing
160   Set xlsSheet = Nothing
170   Set xlsBook = Nothing
180   Set Selection = Nothing
190   DoEvents
200   Err.Clear
210   Exit Sub

220   Debug.Print strProcedure & " Error: " & Err.Description & " (" & Err & ") on line " & Erl
230   Resume ExitSub
End Sub
shambaladAuthor Commented:
Thank you for your patience.
shambaladAuthor Commented:
BTW - Excel actually is gone from the stack in Task Manager. That is what was throwing me, But as you suggest, and I concur, this is a scoping error, so all bets are off.
shambaladAuthor Commented:
I should probably just put this laptop away and call it a night, but...
If you run the mdb I zipped for this question, you will see that the Excel instance is removed from the list of processes in Task Manager,
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.