I'm getting the following error:

error 91 object variable or with block variable not set

when running the below excel automation code, but only if the code has already been run. If I run the code then either close and reopen the db or compact and repair the db, the code runs with out error.


Set xlsSheet = xlsBook.Worksheets("Index2_Region")
xlsApp.Visible = True
xlsApp.Interactive = False
xlsSheet.Activate

xlsSheet.Rows("1:12").Select
Selection.Insert Shift:=xlDown ---this line is highlighted when debugging


Any thoughts or suggestions would be apprciated.

Is this all the VBA code used for the procedure?

Try:

HiTechCoach's suggestion is a good one - your Selection property is unqualified (Application implied) which will leave you with ghost Excel.exe's running when your procedure is complete. You could further revise it like this:
---> shorten to --->
thereby avoiding the Selection object entirely, which is almost always a good thing. Also note that the xlDown enumeration can only be used if you're using early binding. If you are using late binding then you should use -4121 instead.

Thanks for the suggestion. I'll give it try tomorrow. In all the years I've been developing in Access, this is the first job where I've had to use excel automation extensively.

Thanks guys.

I used xlsSheet.Rows("1:12").Insert Shift:=xlDown and it works beautifully.

You're welcome.

Glad Colin and I could assist.

PS: Thanks for sharing your solution!

I have another bit of code that does some formatting with the same sort of issue...works fine on the first run, but errors (same error 91) on subsequent runs.




Set xlsSheet = xlsWorkBook.Worksheets(1)

xlsSheet.Range("J4").Activate

Do Until Selection.Value = "" ---this line is highlighted when debugging


If Selection.Value = "Y" Then
If Right(Selection.Offset(0, -8).Value, 1) = "O" Then
Selection.Interior.ColorIndex = 33
Selection.Font.ColorIndex = 33
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Else
Selection.Interior.ColorIndex = 33
Selection.Font.ColorIndex = 33
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 15
End With
End If


I changed Selection.Value to xlsSheet.Selection.Value, but I get a compile error 'Method or data member not found'. I also tried changing 'xlsSheet.Range("J4").Activate' to 'xlsSheet.Range("J4").Select' to no avail.

When I look in the Object Browser under the Worksheet class there is no Selection member. I am using Access and Excel 2003, Excel 11.0 library.


I never thought I'd say this, but I miss Outlook automation.

In Excel, only the Application and Window classes have a Selection property. I could amend the code for you, but I get the impression that you would really like to nail this yourself.

Mike Rosenblum, who is a MS Excel MVP, wrote a really great article on automating Excel from VB6 and on how to spot and deal with unqualified references. Don't be put off by the fact that it is VB6 - the same rules and techniques will apply for MS Access VBA. The article can be found on a DBForum sister site called Xtreme VB Talk:

Automating Excel from VB 6.0 - Xtreme Visual Basic Talk


In the Excel object model, it is hardly ever necessary to activate or select worksheets and ranges. Using the selection object slows code down, loses intellisense and introduces buggy behaviour. The macro recorder produces code which contains a lot of it (because users have to select cells in order to edit them etc...) which is why it is seen in so many examples when it really should be avoided.

Of course, to clean up code and get rid of the Selection/Select's, you have to figure out which object the Selection references. In this case you haven't shown the entire set of code within the loop, but for the loop to finish it will be dependent on the next cell being selected each time: this means that you will have to redesign your loop so it works directly with the ranges rather than with the selection object.

If none of this makes sense then I'll be more than happy to post some revised code for you (if another member doesn't beat me to it).

Colin,

You are a Rockstar!

Thank you for the link. That is very helpful information.

Colin,

Great article. I really liked this bit: "It may seem like a cruel joke, but any usage of the global 'Application' reference will appear to run correctly within VB 6.0, but in fact this is true only the first time you run your code."

It would appear I've been leaving excel instances floating about....how embarrassing.

I added an xlsApp. (my excel application object) prefix to the first part of the if statement where the value = "Y". The first run went without a hitch. The second run hung up on the 5th record, the 5th record being the first one with a value of "N" rather than "Y".

Thanks again for you help.

Try this as a basis for correcting your procedure. I've tried to use your naming convention style as best as I can and, on review, it seems that the code was formatting almost everything in the cell the same except for the top border colorindex, so I shortened it a touch.