Excel Automation, Cell Border Lines

[Stephen sjw_ost]

I am trying to figure out how to put border lines around each cell in my
excel data. I am using automation from access but the code I am using is only
putting a border around everything instead of putting the border around each
cell. Below is the code I have been trying to get to work.
*********************************************
Dim x, y As Long

x = 4 'initial row#
y = 1 'initial column#

ExSheet.Range(ExSheet.cells(x, y), ExSheet.cells(x + 2, y +
2)).Borders(7).Weight = 2
ExSheet.Range(ExSheet.cells(x, y), ExSheet.cells(x + 2, y +
2)).Borders(8).Weight = 2
ExSheet.Range(ExSheet.cells(x, y), ExSheet.cells(x + 2, y +
2)).Borders(9).Weight = 2
ExSheet.Range(ExSheet.cells(x, y), ExSheet.cells(x + 2, y +
2)).Borders(10).Weight = 2
***********************************************
Thanks for any help,

 

[kc-mass]

Look at this code:

Range("A1:E11").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

Regards

Kevin

 

[Shrini]

Stephen, get the selection from the range. Once you have the selection, you
can start setting up the borders using the selection. You will need to do all
six borders to actually get grid-lines. If you do only four you will get only
a wrapper.

xlEdgeTop
xlEdgeRight
xlEdgeBottm
xlEdgeLeft
xlInsideVertical
xlInsideHorizontal

 

[Stephen sjw_ost]

Thanks, I will try seting all six border settings.

 

[Stephen sjw_ost]

Thanks, I actually got the border setting numbers from creating my own macro
like this and then stepping thru the code. Unfortunatly I have been
unsuccessful with using the "With/End With" statements in Access to Excel
automation. Microsoft KB articles advise against useing them as well.
Anyway, I think what I missed was the xlDiagonalDown and the xlDiagonalUp
settings, as suggested by "Shrini's" post, in my first attemtps. I will try
setting all six border settings and post my results.

 

[Stephen sjw_ost]

I tried it again with all six border settings and it worked just fine.

Thanks for your help on this.