VBscript applying borders in Excel

[Codeblack]

Hello,

Can anyone tell me how to apply borders to a range of cells in excel sheet.

Any soultions will be highly appreciated.

Regards
Codeblack

[Tom Lavedas]

On Sep 11, 7:15 am, Codeblack <Codebl...@discussions.microsoft.com>
wrote:

I went to Excel and recorder a macro of marking and applying a
border. Then I adjusted it a bit (mostly looking up constants to add
to the stand alone) to use it as a stand alone script ...

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/11/2008 by Tom Lavedas
'
Const xlAutomatic = -4105
Const xlNone = -4142
Const xlContinuous = 1
Const xlThin = 2
Const xlDiagonalDown = 5
Const xlDiagonalUp = 6
Const xlEdgeLeft = 7
Const xlEdgeTop = 8
Const xlEdgeBottom = 9
Const xlEdgeRight = 10
Const xlInsideVertical = 11
Const xlInsideHorizontal = 12
'
With oXL.ActiveSheet.Range("A1:F29")
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
End Sub

I observed the repetitiveness of the With blockas and changed it
to ...

Sub Macro1(sAddress)

Const xlAutomatic = -4105
Const xlNone = -4142
Const xlContinuous = 1
Const xlThin = 2
Const xlDiagonalDown = 5
Const xlDiagonalUp = 6
Const xlEdgeLeft = 7
Const xlEdgeTop = 8
Const xlEdgeBottom = 9
Const xlEdgeRight = 10
Const xlInsideVertical = 11
Const xlInsideHorizontal = 12

With oXL.ActiveSheet.Range(sAddress)
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
for xlconstant = xlEdgeLeft to xlInsideHorizontal
With .Borders(xlconstant)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
next
End Sub

Then wrapped it in this bit of code to finish ...

sPath = "D:\someplace\"
set oXL = CreateObject("Excel.Application")
oXL.workbooks.open sPath & "testing.xls"
Macro1 "A1:F29"
oXL.ActiveWorkbook.SaveAs sPath & "newtest.xls"
oXL.quit

I tested the first subroutine, but not the final script. I leave that
to you to check.

Tom Lavedas
===========
http://members.cox.net/tglbatch/wsh/

[OldDog]

And in PowerShell;

$xlAutomatic = -4105
$xlBottom = -4107
$xlCenter = -4108
$xlContext = -5002
$xlContinuous = 1
$xlDiagonalDown = 5
$xlDiagonalUp = 6
$xlEdgeBottom = 9
$xlEdgeLeft = 7
$xlEdgeRight = 10
$xlEdgeTop = 8
$xlInsideHorizontal = 12
$xlInsideVertical = 11
$xlNone = -4142
$xlThin = 2

$xl = new-object -com excel.application
$xl.visible=$true
$wb = $xl.workbooks.open("C:\Scripts\Book1.xlsx")
$ws = $wb.worksheets | where {$_.name -eq "sheet1"}
$selection = $ws.range("A1:F29")
$selection.select()

$selection.HorizontalAlignment = $xlCenter
$selection.VerticalAlignment = $xlBottom
$selection.WrapText = $false
$selection.Orientation = 0
$selection.AddIndent = $false
$selection.IndentLevel = 0
$selection.ShrinkToFit = $false
$selection.ReadingOrder = $xlContext
$selection.MergeCells = $false
$selection.Borders.Item($xlInsideHorizontal).Weight = $xlThin
$selection.Borders.Item($xlInsideHorizontal).LineStyle =
$xlContinuous
$selection.Borders.Item($xlInsideHorizontal).ColorIndex = $xlAutomatic

[Codeblack]

Thank you very much all. Will try the options mentioned by you.

Regards
Ciodeblack