Excel 2003 Automation - Object variable or With block variable not set
Hello -
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 (LoadTPR) to set up, then import the workbook. LoadTPR reformats some numeric columns which are formatted with commas to eliminate the commas. The workbook is saved as a .csv, then imported using TransferText.
The LoadTPR subroutine runs without error the first time through. The second iteration, though, abends with error 91: Object variable or With block variable not set.
I don't know why this is happening. I believe I am explicitly and thoroughly cleaning up the Excel objects at the end of the subroutine, but there's something that's causing this procedure to stop. FWIW, there are no other instances of Access or Excel running on the machine concurrently.
I have included the code below. The error is occurring on line 160.
Any help would be most appreciated.
Thanks,
Todd
Private Sub LoadTPR(strFile As String)
Const strProcedure As String = "LoadTPR"
Dim xlsSheet As Excel.Worksheet
Dim xlsBook As Excel.Workbook
Dim xls As Excel.Application
Dim xlsRange As Excel.Range
Dim strCsv As String
10 On Error GoTo ErrorHandler
20 strCsv = CurrentProject.Path & "\TPR.csv"
' Delete csv file
30 On Error Resume Next
40 Kill strCsv
50 Err.Clear
60 On Error GoTo ErrorHandler
' Set up the workbook for import
70 Set xls = New Excel.Application
80 With xls
90 Set xlsBook = .Workbooks.Open(strFile, False, False)
100 With xlsBook
110 Set xlsSheet = .Worksheets(1)
120 With xlsSheet
' Reset numeric formats, drop commas
130 Set xlsRange = .Columns("L:L")
140 With xlsRange
150 .Select
160 Selection.NumberFormat = "0" '<== Error Here
170 End With 'With xlsRange
180 Set xlsRange = .Columns("P:P")
190 With xlsRange
200 .Select
210 Selection.NumberFormat = "0"
220 End With 'With xlsRange
230 Set xlsRange = .Columns("O:O")
240 With xlsRange
250 .Select
260 Selection.NumberFormat = "0.00"
270 End With 'With xlsRange
280 Set xlsRange = .Columns("M:N")
290 With xlsRange
300 .Select
310 Selection.NumberFormat = "0.000000"
320 End With 'With xlsRange
330 End With 'With xlsSheet
340 .SaveAs strCsv, xlCSV
350 .Save
360 .Close True
370 End With 'With xlsBook
380 End With 'With xls
390 DoCmd.SetWarnings False
400 DoCmd.TransferText acImportDelim, "TPR Import Specification", "tblTPR", strCsv
410 DoCmd.SetWarnings True
ExitSub:
420 On Error Resume Next
430 Set xlsRange = Nothing
440 Set xlsSheet = Nothing
450 Set xlsBook = Nothing
460 xls.Quit
470 Set xls = Nothing
480 Kill strCsv
490 Err.Clear
500 Exit Sub
ErrorHandler:
510 DoCmd.Hourglass False
520 Debug.Print strProcedure & " Error: " & Err.Description & " (" & Err & ") on line " & Erl
530 Resume ExitSub
End Sub
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 (LoadTPR) to set up, then import the workbook. LoadTPR reformats some numeric columns which are formatted with commas to eliminate the commas. The workbook is saved as a .csv, then imported using TransferText.
The LoadTPR subroutine runs without error the first time through. The second iteration, though, abends with error 91: Object variable or With block variable not set.
I don't know why this is happening. I believe I am explicitly and thoroughly cleaning up the Excel objects at the end of the subroutine, but there's something that's causing this procedure to stop. FWIW, there are no other instances of Access or Excel running on the machine concurrently.
I have included the code below. The error is occurring on line 160.
Any help would be most appreciated.
Thanks,
Todd
Private Sub LoadTPR(strFile As String)
Const strProcedure As String = "LoadTPR"
Dim xlsSheet As Excel.Worksheet
Dim xlsBook As Excel.Workbook
Dim xls As Excel.Application
Dim xlsRange As Excel.Range
Dim strCsv As String
10 On Error GoTo ErrorHandler
20 strCsv = CurrentProject.Path & "\TPR.csv"
' Delete csv file
30 On Error Resume Next
40 Kill strCsv
50 Err.Clear
60 On Error GoTo ErrorHandler
' Set up the workbook for import
70 Set xls = New Excel.Application
80 With xls
90 Set xlsBook = .Workbooks.Open(strFile, False, False)
100 With xlsBook
110 Set xlsSheet = .Worksheets(1)
120 With xlsSheet
' Reset numeric formats, drop commas
130 Set xlsRange = .Columns("L:L")
140 With xlsRange
150 .Select
160 Selection.NumberFormat = "0" '<== Error Here
170 End With 'With xlsRange
180 Set xlsRange = .Columns("P:P")
190 With xlsRange
200 .Select
210 Selection.NumberFormat = "0"
220 End With 'With xlsRange
230 Set xlsRange = .Columns("O:O")
240 With xlsRange
250 .Select
260 Selection.NumberFormat = "0.00"
270 End With 'With xlsRange
280 Set xlsRange = .Columns("M:N")
290 With xlsRange
300 .Select
310 Selection.NumberFormat = "0.000000"
320 End With 'With xlsRange
330 End With 'With xlsSheet
340 .SaveAs strCsv, xlCSV
350 .Save
360 .Close True
370 End With 'With xlsBook
380 End With 'With xls
390 DoCmd.SetWarnings False
400 DoCmd.TransferText acImportDelim, "TPR Import Specification", "tblTPR", strCsv
410 DoCmd.SetWarnings True
ExitSub:
420 On Error Resume Next
430 Set xlsRange = Nothing
440 Set xlsSheet = Nothing
450 Set xlsBook = Nothing
460 xls.Quit
470 Set xls = Nothing
480 Kill strCsv
490 Err.Clear
500 Exit Sub
ErrorHandler:
510 DoCmd.Hourglass False
520 Debug.Print strProcedure & " Error: " & Err.Description & " (" & Err & ") on line " & Erl
530 Resume ExitSub
End Sub
Prefix it with the application.
Instead of Selection.NumberFormat = "0" '<== Error Here
Use:
And use the xls prefix for all instances of "Selection" in your code that are currently not prefixed.
Instead of Selection.NumberFormat = "0" '<== Error Here
Use:
xls.Selection.NumberFormat = "0" And use the xls prefix for all instances of "Selection" in your code that are currently not prefixed.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
try this,
change
Selection.NumberFormat = "0" '<== Error Here
with
.Selection.NumberFormat = "0" '<== Error Here
^ dot before selection
change
Selection.NumberFormat = "0" '<== Error Here
with
.Selection.NumberFormat = "0" '<== Error Here
^ dot before selection
Well that was certainly a flurry of responses ;)
ASKER
I'm checking out the suggestions. Will get back to you shortly. Thanks for the quick responses.
ASKER
KyleSW: Your suggestion (ID: 38395171) to drop the Range Sets and use the line
.Columns("L:L").NumberForm
appears to be working. I'm going to expand the test, which takes a little longer to run. I'll post my results as soon as I'm done.
.Columns("L:L").NumberForm
appears to be working. I'm going to expand the test, which takes a little longer to run. I'll post my results as soon as I'm done.
ASKER
That fixed it.
Thanks,
Todd
Thanks,
Todd
What if you wanted to do something else with the ranges other than just set the format?
Good point norie, you could do something like:
With .Columns("L:L")
.NumberFormat = "0"
'Do Something Else
End With
Well I was thinking of using the variable.
ASKER
With .Columns("L:L")
.NumberFormat = "0"
'Do Something Else
End With
FWIW: I think this was the way I originally coded this section, when I started to run into problems. That's why I added the code setting the ranges, etc. I'm going to have to look back and see if I have a saved version with the original code I used. I can't do it at the moment because the PC I am doing the development on is tied up doing a big run of the batch (which appears to be running OK, by the way).
Then again, I did add some other items: Although I always have always set my objects to nothing, I added an explicit "xls.quit" to close out the Excel instance, the lack of which may have been a contributing factor in previous versions.
.NumberFormat = "0"
'Do Something Else
End With
FWIW: I think this was the way I originally coded this section, when I started to run into problems. That's why I added the code setting the ranges, etc. I'm going to have to look back and see if I have a saved version with the original code I used. I can't do it at the moment because the PC I am doing the development on is tied up doing a big run of the batch (which appears to be running OK, by the way).
Then again, I did add some other items: Although I always have always set my objects to nothing, I added an explicit "xls.quit" to close out the Excel instance, the lack of which may have been a contributing factor in previous versions.
ASKER
Just tried
With .Columns("L:L")
.NumberFormat = "0"
End With
with and without the "xls.quit" in the cleanup section; of course I didn't get any errors...
With .Columns("L:L")
.NumberFormat = "0"
End With
with and without the "xls.quit" in the cleanup section; of course I didn't get any errors...
The problem was the use of Selection.
It wasn't even tied to any of the Excel objects, eg application. worksheet, workbook, range.
Even if this code was in Excel you wouldn't use Selection, it's rarely, if ever, used.
It wasn't even tied to any of the Excel objects, eg application. worksheet, workbook, range.
Even if this code was in Excel you wouldn't use Selection, it's rarely, if ever, used.
ASKER
Duly noted. I will say, though, that Excel still mystifies me at times.
I'm still trying to figure out why my code would work the first time, but not on subsequent calls. I also wanted to expand the testing a little on the other suggestions made in this question. The sub I have been using is below. What I would do is comment out different sections as I went along. Now I can't get my original code to error out!
I'm suspecting it may have to do with other instances of Excel in the stack, since I did find multiple instances in the stack as I was doing this latest investigation. I'm betting they've been out there for a while since this machine has been up for a few days now. One thing I'm finding is that so long as i don't have an active instance of Excel in the stack when I start this process up, the cleanup appears to work in as much as there's no active instance of Excel when the process completes. If, however, I open a instance of Excel (new, empty workbook), then run the process, I see two instances of Excel in the stack after the process has completed. The procedure I'm using to test this calls the subroutine 4 times.
Private Sub LoadTPR(strFile As String)
Const strProcedure As String = "LoadTPR"
Dim xlsSheet As Excel.Worksheet
Dim xlsBook As Excel.Workbook
Dim xls As Excel.Application
Dim xlsRange As Excel.Range
Dim strCsv As String
10 On Error GoTo ErrorHandler
20 strCsv = CurrentProject.Path & "\TPR.csv"
' Delete csv file
30 On Error Resume Next
40 Kill strCsv
50 Err.Clear
60 On Error GoTo ErrorHandler
' Set up the workbook for import
70 Set xls = New Excel.Application
80 With xls
90 Set xlsBook = .Workbooks.Open(strFile, False, False)
100 With xlsBook
110 Set xlsSheet = .Worksheets(1)
120 With xlsSheet
130 .Columns("L:L").NumberForm
140 .Columns("M:N").NumberForm
150 With .Columns("L:L")
160 .NumberFormat = "0"
170 End With
180 With .Columns("M:N")
190 .NumberFormat = "0.000000"
200 End With
210 Set xlsRange = .Columns("L:L")
220 xlsRange.NumberFormat = "0"
230 Set xlsRange = .Columns("M:N")
240 xlsRange.NumberFormat = "0.000000"
250 Set xlsRange = .Columns("L:L")
260 With xlsRange
270 .Select
280 xls.Selection.NumberFormat
290 End With 'With xlsRange
300 Set xlsRange = .Columns("M:N")
310 With xlsRange
320 .Select
330 xls.Selection.NumberFormat
340 End With 'With xlsRange
350 Set xlsRange = .Columns("L:L")
360 With xlsRange
370 .Select
380 Selection.NumberFormat = "0"
390 End With 'With xlsRange
400 Set xlsRange = .Columns("M:N")
410 With xlsRange
420 .Select
430 Selection.NumberFormat = "0.000000"
440 End With 'With xlsRange
450 With .Columns("L:L")
460 .Select
470 Selection.NumberFormat = "0"
480 End With 'With xlsRange
490 With .Columns("M:N")
500 .Select
510 Selection.NumberFormat = "0.000000"
520 End With 'With .Columns("M:N")
530 End With 'With xlsSheet
540 .SaveAs strCsv, xlCSV
550 .Save
560 .Close True
570 End With 'With xlsBook
580 End With 'With xls
ExitSub:
590 On Error Resume Next
600 Set xlsRange = Nothing
610 Set xlsSheet = Nothing
620 Set xlsBook = Nothing
630 xls.Quit
640 Set xls = Nothing
650 Kill strCsv
660 Err.Clear
670 Exit Sub
ErrorHandler:
680 DoCmd.Hourglass False
690 Debug.Print strProcedure & " Error: " & Err.Description & " (" & Err & ") on line " & Erl
700 Resume ExitSub
End Sub
I'm still trying to figure out why my code would work the first time, but not on subsequent calls. I also wanted to expand the testing a little on the other suggestions made in this question. The sub I have been using is below. What I would do is comment out different sections as I went along. Now I can't get my original code to error out!
I'm suspecting it may have to do with other instances of Excel in the stack, since I did find multiple instances in the stack as I was doing this latest investigation. I'm betting they've been out there for a while since this machine has been up for a few days now. One thing I'm finding is that so long as i don't have an active instance of Excel in the stack when I start this process up, the cleanup appears to work in as much as there's no active instance of Excel when the process completes. If, however, I open a instance of Excel (new, empty workbook), then run the process, I see two instances of Excel in the stack after the process has completed. The procedure I'm using to test this calls the subroutine 4 times.
Private Sub LoadTPR(strFile As String)
Const strProcedure As String = "LoadTPR"
Dim xlsSheet As Excel.Worksheet
Dim xlsBook As Excel.Workbook
Dim xls As Excel.Application
Dim xlsRange As Excel.Range
Dim strCsv As String
10 On Error GoTo ErrorHandler
20 strCsv = CurrentProject.Path & "\TPR.csv"
' Delete csv file
30 On Error Resume Next
40 Kill strCsv
50 Err.Clear
60 On Error GoTo ErrorHandler
' Set up the workbook for import
70 Set xls = New Excel.Application
80 With xls
90 Set xlsBook = .Workbooks.Open(strFile, False, False)
100 With xlsBook
110 Set xlsSheet = .Worksheets(1)
120 With xlsSheet
130 .Columns("L:L").NumberForm
140 .Columns("M:N").NumberForm
150 With .Columns("L:L")
160 .NumberFormat = "0"
170 End With
180 With .Columns("M:N")
190 .NumberFormat = "0.000000"
200 End With
210 Set xlsRange = .Columns("L:L")
220 xlsRange.NumberFormat = "0"
230 Set xlsRange = .Columns("M:N")
240 xlsRange.NumberFormat = "0.000000"
250 Set xlsRange = .Columns("L:L")
260 With xlsRange
270 .Select
280 xls.Selection.NumberFormat
290 End With 'With xlsRange
300 Set xlsRange = .Columns("M:N")
310 With xlsRange
320 .Select
330 xls.Selection.NumberFormat
340 End With 'With xlsRange
350 Set xlsRange = .Columns("L:L")
360 With xlsRange
370 .Select
380 Selection.NumberFormat = "0"
390 End With 'With xlsRange
400 Set xlsRange = .Columns("M:N")
410 With xlsRange
420 .Select
430 Selection.NumberFormat = "0.000000"
440 End With 'With xlsRange
450 With .Columns("L:L")
460 .Select
470 Selection.NumberFormat = "0"
480 End With 'With xlsRange
490 With .Columns("M:N")
500 .Select
510 Selection.NumberFormat = "0.000000"
520 End With 'With .Columns("M:N")
530 End With 'With xlsSheet
540 .SaveAs strCsv, xlCSV
550 .Save
560 .Close True
570 End With 'With xlsBook
580 End With 'With xls
ExitSub:
590 On Error Resume Next
600 Set xlsRange = Nothing
610 Set xlsSheet = Nothing
620 Set xlsBook = Nothing
630 xls.Quit
640 Set xls = Nothing
650 Kill strCsv
660 Err.Clear
670 Exit Sub
ErrorHandler:
680 DoCmd.Hourglass False
690 Debug.Print strProcedure & " Error: " & Err.Description & " (" & Err & ") on line " & Erl
700 Resume ExitSub
End Sub
ASKER
Just wondering if the unqualified Selection (i.e. 'Selection' as opposed the '.Selection') may have been looking for its home on the 2nd call since xls was set to Nothing at the end of the first call.
ASKER
Ended the Access session, then restarted it. Now I can replicate the error.
ASKER
I'm going to open up a new question on this.
Open in new window