Method 'Range' of object '_Global' Failed Error

[Sandra]

Hello,

I'm writing a macro to write schedule data to Excel. All the data I need is
coming over great! The problem is when I try to format Excel from inside
MSProject 2000. The first time it runs it runs great! The 2nd time it runs
I get the error "Method 'Range' of object '_Global' Failed. I'm not doing
anything fancy or special...just putting borders to divide the data as
written below. The 2nd time through it HATEs my "Range" statement. Can
anyone help?

Range("A6:I12").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

Thanks!

 

[John]

Hello,

I'm writing a macro to write schedule data to Excel. All the data I need is
coming over great! The problem is when I try to format Excel from inside
MSProject 2000. The first time it runs it runs great! The 2nd time it runs
I get the error "Method 'Range' of object '_Global' Failed. I'm not doing
anything fancy or special...just putting borders to divide the data as
written below. The 2nd time through it HATEs my "Range" statement. Can
anyone help?

Range("A6:I12").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

Thanks!

Sandra,
I normally run into this type of problem when a reference is NOT set for
the Excel object library but it always fails when first run, not on a
second iteration. However, if you don't have the reference set, I
suggest you do that. From the VBA editor go to Tools/References. Find
whatever Excel object is listed for your installation and check it.

If a reference to the Excel object library is already set, I don't see
anything particularly wrong or suspect with your code snippet other than
the snippet doesn't show any kind of Excel object. For example, I would
expect so see something like the following in the first line:
xl.Range("A6:I12").Select

Hope this helps.
John