Advanced Windows PowerShell Scripting Video Training

Advanced Windows PowerShell Scripting Video Training
Advanced Windows PowerShell Scripting Video Training

Thursday, June 16, 2011

Excel Boarder Properties with PowerShell

The function below will display the values of various properties of borders in Excel.  This script is designed to help programmers find the appropriate values that they need.
<#
.SYNOPSIS
Displays the attributes and results for
several Excel boarder Properties.
.DESCRIPTION
This function will launch an Excel spreadsheet.
It will then display the properties for style,
color, and weight for borders.
#>

Function Display-ExcelBoarders
{
$a = New-Object -comobject Excel.Application

$a.Visible = $True

$b = $a.Workbooks.Add()
$c = $b.Worksheets.Item(1)


# Display Borders

# Column Header
$C.Cells.Item(1,1) = ".Borders.LineStyle = "

# AutoSize the Column
$C.Cells.Item(1,1).EntireColumn.AutoFit()

# Valid range for LineStyle is 0 through 13. 
For ($x=0;$x -lt 14; $x++)
{
$C.Cells.Item($x+2,1) =  $x
$C.Cells.Item($x+2,1).Borders.LineStyle = $x    
}

# Border Color
# Column Header
$C.Cells.Item(1,3) = ".Borders.ColorIndex = "

# AutoSize the Column
$C.Cells.Item(1,3).EntireColumn.AutoFit()

# Valid range for ColorIndex is 0 through 55. 
For ($x=0;$x -lt 56; $x++)
{
$C.Cells.Item($x+2,3) =  $x
$C.Cells.Item($x+2,3).Borders.ColorIndex = $x    
$C.Cells.Item($x+2,3).borders.weight = 4
}

# Border Weight
# Column Header
$C.Cells.Item(1,5) = ".Borders.Weight = "

# AutoSize the Column
$C.Cells.Item(1,5).EntireColumn.AutoFit()

# Valid range for Weight is 0 through 4. 
For ($x=0;$x -lt 5; $x++)
{
$C.Cells.Item($x+2,5) =  $x    
$C.Cells.Item($x+2,5).borders.weight = $x
}

# Author Information
$C.Cells.Item(1,7) = "Jason Yoder"
$C.Cells.Item(2,7) = "www.MCTExpert.com"



}

No comments: