Excel cell borders

HCL Domino Domino Forum
1

I am exporting a set of documents to an excel spread sheet template. The template is designed for 13 records rows 1 - 13 lets say. By the end of the year I may have additional lines that I need added say now I need the template to handle 15 records rows 1 - 15. Every thing is fine other than the cell borders do not have the same grid lines as the template. I can’t seem to find the right method of setting the borders properties.To instert the rows. I am using this line

If indexY => 37 Then

Sheet.Rows(38 + r).Insert

How do I get the same borders on the inserted rows as the rest of the spread sheet?

2

Subject: Excel cell borders

You have to “manually” insert the borderstyles as well as the value to the inserted cell itself.

If You record a macro in excel then You can cut and paste the most of the code directly into LS. Just change the constant values into real values (or add the constants in Your LS):

Const xlNone = -4142

Const xlEdgeLeft = 7

Const xlEdgeRight = 10

Const xlDiagonalDown = 5

Const xlDiagonalUp = 6

Const xlEdgeTop = 8

Const xlEdgeBottom = 9

Const xlContinuous = 1

Const xlThin = 2

Const xlAutomatic = -4105

’ This will add a thin border to the left and right edge of the active cell:

Selection.Borders(xlDiagonalDown).LineStyle = xlNone

Selection.Borders(xlDiagonalUp).LineStyle = xlNone

With Selection.Borders(xlEdgeLeft)

    .LineStyle = xlContinuous ' Change this to whatever linestyle You prefer

    .Weight = xlThin ' Change this to the width/weight of the borderstyle

    .ColorIndex = xlAutomatic ' Change this if You want another color

End With

Selection.Borders(xlEdgeTop).LineStyle = xlNone

Selection.Borders(xlEdgeBottom).LineStyle = xlNone

With Selection.Borders(xlEdgeRight)

    .LineStyle = xlContinuous

    .Weight = xlThin

    .ColorIndex = xlAutomatic

End With

’ - - - CODE END - - -

hth