This is a follow-up question to one that was resolved yesterday (Q_27863723
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 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
150 End With
160 End With
170 Debug.Print "DONE!"
180 On Error Resume Next
200 Set db = Nothing
210 On Error GoTo 0
220 Exit Sub
230 Debug.Print strProcedure, Err, Err.Description, Erl
240 Resume ExitFunction
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
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
240 Set xlsApp = Nothing
250 Shell "TASKKILL /F /pid " & lngPID, vbHide
280 Exit Sub
290 DoCmd.Hourglass False
300 Debug.Print strProcedure & " Error: " & Err.Description & " (" & Err & ") on line " & Erl
310 Resume ExitSub