Submitted By: Kent Finkle

Creates a spreadsheet and then places borders around specified cells.

PowerShell
Edit|Remove
$comments = @'
Script name: Put-Borders.ps1
Created on: Wednesday, June 06, 2007
Author: Kent Finkle
Purpose: How can I use Windows Powershell to
Put Borders Around Spreadsheet Cells?
'@
#-----------------------------------------------------
function Release-Ref ($ref) {
([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
[System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers() 
}
#-----------------------------------------------------
$xlContinuous = 1
$xlThick = 4
 
$objExcel = new-object -comobject excel.application
$objExcel.Visible = $True
$objWorkbook = $objExcel.Workbooks.Add()
$objWorksheet = $objWorkbook.Worksheets.Item(1)
 
$k = 1
for ($i = 1; $i -le 5; $i++) {
    for ($j = 1; $j -le 5; $j++) {
        $objWorksheet.Cells.Item($i,$j).Value() = $k
        $k++
    }
}
$objWorksheet.UsedRange.Borders.LineStyle = $xlContinuous

$blue = 255
$green= 0
$red = 0
$a = [long] ($blue + ($green * 256) + ($red * 65536))

$objWorksheet.UsedRange.Borders.Color = $a
$objWorksheet.UsedRange.Borders.Weight = $xlThick
 
$a = Release-Ref($objWorksheet)
$a = Release-Ref($objWorkbook)
$a = Release-Ref($objExcel)