Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634

    Unanswered: Excel Automation Error

    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.

  2. #2
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by rogue View Post
    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:
    Code:
    xlsSheet.Selection.Insert Shift:=xlDown
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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:
    Code:
    xlsSheet.Activate
    xlsSheet.Rows("1:12").Select
    Selection.Insert Shift:=xlDown
    ---> shorten to --->
    Code:
    xlsSheet.Rows("1:12").Insert Shift:=xlDown
    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.
    Code:
    xlsSheet.Rows("1:12").Insert Shift:=-4121
    Last edited by Colin Legg; 09-01-10 at 17:00.

  4. #4
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    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.

  5. #5
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Thanks guys.

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

  6. #6
    Join Date
    May 2010
    Posts
    601
    You're welcome.

    Glad Colin and I could assist.

    PS: Thanks for sharing your solution!
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  7. #7
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    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.

  8. #8
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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).

  9. #9
    Join Date
    May 2010
    Posts
    601
    Colin,

    You are a Rockstar!

    Thank you for the link. That is very helpful information.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  10. #10
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    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.

  11. #11
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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.

    Code:
        'additional procedural declarations
        Dim xlsRange As Excel.Range
        Dim xlsLastRow As Long
        Dim r As Long
    Code:
     
        Set xlsSheet = xlsWorkbook.Worksheets(1)
     
        'find the last used cell in column J
        xlsLastRow = xlsSheet.Range("J" & xlsSheet.Rows.Count).End(xlUp).Row
     
        For r = 4 To xlsLastRow
            Set xlsRange = xlsSheet.Cells(r, "J")
     
            With xlsRange
                If .Value2 = "Y" Then
     
                    .Interior.ColorIndex = 33
                    .Font.ColorIndex = 33
     
                    With .Borders(xlEdgeTop)
                        .LineStyle = xlContinuous
                        .Weight = xlThin
     
                        If VBA.Right$(xlsRange.Offset(, -8).Value2, 1) = "O" Then
                            .ColorIndex = xlAutomatic
                        Else
                            .ColorIndex = 15
                        End If
                    End With
                End If
            End With
        Next r
     
        'example clean-up code:
        Set xlsRange = Nothing
        Set xlsSheet = Nothing
     
        xlsWorkbook.Save
        xlsWorkbook.Close False
        Set xlsWorkbook = Nothing
     
        xlsApp.Quit
        Set xlsApp = Nothing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •