?
Solved

Excel 2003 Automation - Object variable or With block variable not set

Posted on 2012-09-13
18
Medium Priority
?
774 Views
Last Modified: 2012-09-13
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
0
Comment
Question by:shambalad
  • 9
  • 4
  • 3
  • +2
18 Comments
 
LVL 36

Expert Comment

by:Norie
ID: 38395147
Remove Selection from Selection.NumberFormat and delete .Select
              Set xlsRange = .Columns("L:L")
              xlsRange.NumberFormat = "0" 
 
               Set xlsRange = .Columns("P:P")
               xlsRange.NumberFormat = "0"
 
               Set xlsRange = .Columns("O:O")
               xlsRange.NumberFormat = "0.00"
 
               Set xlsRange = .Columns("M:N")
               xlsRange.NumberFormat = "0.000000"
  

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 38395152
Prefix it with the application.

Instead of  Selection.NumberFormat = "0"   '<== Error Here

Use:

xls.Selection.NumberFormat = "0"  

Open in new window


And use the xls prefix for all instances of "Selection" in your code that are currently not prefixed.
0
 
LVL 5

Accepted Solution

by:
KyleSW earned 2000 total points
ID: 38395171
There's no need to select, remove all the selections from your code, it is both flaky and slow, try the following for each of your columns, try replacing the following:
Set xlsRange = .Columns("L:L")
With xlsRange
    .Select
    Selection.NumberFormat = "0"
End With

Open in new window

simply with:
.Columns("L:L").NumberFormat = "0"

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38395173
try this,

change

Selection.NumberFormat = "0"   '<== Error Here

with

.Selection.NumberFormat = "0"   '<== Error Here
^ dot before selection
0
 
LVL 5

Expert Comment

by:KyleSW
ID: 38395190
Well that was certainly a flurry of responses ;)
0
 
LVL 7

Author Comment

by:shambalad
ID: 38395282
I'm checking out the suggestions. Will get back to you shortly. Thanks for the quick responses.
0
 
LVL 7

Author Comment

by:shambalad
ID: 38395358
KyleSW: Your suggestion (ID: 38395171) to drop the Range Sets and  use the line

.Columns("L:L").NumberFormat = "0"

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.
0
 
LVL 7

Author Closing Comment

by:shambalad
ID: 38395447
That fixed it.
Thanks,
Todd
0
 
LVL 36

Expert Comment

by:Norie
ID: 38395518
What if you wanted to do something else with the ranges other than just set the format?
0
 
LVL 5

Expert Comment

by:KyleSW
ID: 38395540
Good point norie, you could do something like:
With .Columns("L:L")
    .NumberFormat = "0"
    'Do Something Else
End With

Open in new window

0
 
LVL 36

Expert Comment

by:Norie
ID: 38395583
Well I was thinking of using the variable.
0
 
LVL 7

Author Comment

by:shambalad
ID: 38395656
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.
0
 
LVL 7

Author Comment

by:shambalad
ID: 38395984
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...
0
 
LVL 36

Expert Comment

by:Norie
ID: 38396004
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.
0
 
LVL 7

Author Comment

by:shambalad
ID: 38396357
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").NumberFormat = "0"
140         .Columns("M:N").NumberFormat = "0.000000"
 
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 = "0"
290         End With            'With xlsRange
 
300         Set xlsRange = .Columns("M:N")
310         With xlsRange
320            .Select
330            xls.Selection.NumberFormat = "0.000000"
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
0
 
LVL 7

Author Comment

by:shambalad
ID: 38396373
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.
0
 
LVL 7

Author Comment

by:shambalad
ID: 38396452
Ended the Access session, then restarted it. Now I can replicate the error.
0
 
LVL 7

Author Comment

by:shambalad
ID: 38396481
I'm going to open up a new question on this.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question