Opening Excel from Access, getting error 91 AFTER first successful run

Question

I have this code in Access to open and populate an excel report. It work perfectly the first time it is run, but if I go to run it again for a different group, it gives me the run-time error 91: Object variable or With block variable not set.

wb.Worksheets(1).Range("A" & i & ":g" & i).Select
wb.Worksheets(1).Range("g" & i).Activate
With wb.Worksheets(1).Range("g" & i)
    Selection.Font.size = 13
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End With

The error is currently being thrown at the fourth line, "Selection.font.size=13. I don't usually program in excel, so I'm trying things that might not make sense:

wb.Worksheets(1).Range("A" & i & ":g" & i).Selection.Font.size = 13
wb.Worksheets(1).Selection.Font.size = 13

If anyone can point me in the right direction it would be much appreciated.

Change `Selection.Font.size = 13` to `.Font.size = 13` Similarly with others. Also [INTERESTING READ](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Siddharth Rout, Nov 05 '13 at 18:17

score 0 · Accepted Answer · answered Nov 05 '13 at 18:27

Like Sid said, your WITH statements do not match your SELECT statements, and don't use select if you don't absolutely need to. Also, shortening, your code could look like this:

With wb.Worksheets(1).Range("g" & i)
.Font.size = 13
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
End With
With wb.Worksheets(1).Range("g" & i).Borders
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlMedium
End With

Aha! Thanks so much! I still don't understand why it would run correctly the first time, though... — TinaB, Nov 05 '13 at 18:38

Opening Excel from Access, getting error 91 AFTER first successful run

1 Answers1