Setting Excel Borders From VBScript
Borders are controlled via the Borders property of the Excel Range object, where a range is a bunch of cells. Before we start looking at the borders we need to define a few nice Excel constants:
You can define these as constants or an scripting dictionary or what ever takes your fancy.
These define the style of the border
xlNone = -4142; Note this is the same as xlLineStyleNone
xlContinuous = 1;
xlDash = -4115;
xlDashDot = 4;
xlDashDotDot = 5;
xlDot = -4118;
xlDouble = -4119;
xlSlantDashDot = 13;
These define the weight of the border
xlHairLine = 1;
xlMedium = -4138;
xlThick = 4;
xlThin = 2;
This is handy to make borders have the default color index
xlAutomatic = -4105;
These define the placement of border pieces
xlDiagonalDown = 5;
xlDiagonalUp = 6;
xlEdgeLeft = 7;
xlEdgeTop = 8;
xlEdgeBottom = 9;
xlEdgeRight = 10;
xlInsideVertical = 11;
xlInsideHorizontal = 12;
So, if I wanted to put a slant dash dot heavy border on the right hand side of the first cell of the first sheet I could do something like this:
' VBScript
dim excel
set excel=WScript.CreateObject("Excel.Application")
dim wb
set wb=excel.WorkBooks.Add()
dim ws
set ws=wb.Sheets("Sheet1")
dim range
set range=ws.Cells(1,1)
with range.Borders(xlEdgeRight)
.LineStyle=xlSlantDashDot
.Weight=xlThick
.ColorIndex=xlAutomatic
end with
[Via]