Use of Selection when late binding

[Dale Fye]

I'm using automation in Access to reformat several Excel spreadsheets within
a workbook I will be receiving on a weekly basis. I have my code working
using early binding, but would prefer to use late binding as I expect we will
be migrating to Office 2007 in the near future and I'd prefer not to have to
change my references.

My problem is that I'm not sure how to refer to a range of cells and remove
their borders. The following code works with early binding.

Set rng = sht.Range("A1:O1")
rng.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Set rng = nothing

When I remove the reference to Excel, and change it to the following, I get
a "Variable not defined" error, and the first instance of "Selection" is
highlighted:

Set rng = sht.Range("A1:O1")
rng.Select
Selection.Borders(5).LineStyle = -4142
Selection.Borders(6).LineStyle = -4142
Selection.Borders(7).LineStyle = -4142
Selection.Borders(8).LineStyle = -4142
Selection.Borders(9).LineStyle = -4142
Selection.Borders(10).LineStyle = -4142
Selection.Borders(11).LineStyle = -4142
Selection.Borders(12).LineStyle = X - 4142
Set rng = Nothing

Dale

 

[Dale Fye]

Disrregard.

The solution to this was to preface Selection with a reference to the Excel
application object:

xlApp.Selection.Borders(5).LineStyle = -4142

Dale

 

[George Nicholson]

In addition, Selection is unnecessary 95% of the time, just refer to the
Range object:

Set rng = sht.Range("A1:O1")
With rng
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
'etc
End With

 

[Dale Fye]

Thanks, George. I'll keep that in mind.
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Klatuu]

Selection is not reliable at all in automation and as you said almost never
necessary. Coding directly in Excel, however, it is best to use it.