Vista - Excel Cook Book

As promissed;

OldDogs Excel Cook Book

All errors and mistakes are my own. Thanks to all who have helped
along the way.

________________________________________________________________________

How do I create an Excel object?

$xl = new-object -comobject excel.application

The Scripting Guys have a column about tab expansion with an Excel
example.
________________________________________________________________________

How do I make Excel visible?
$xl.Visible = $true

________________________________________________________________________

How do I add a workbook?

$wb = $a.Workbooks.Add()

________________________________________________________________________

How do I add a worksheet?

$xl = new-object -comobject excel.application $xl.Visible = $true
$wb = $xl.Workbooks.Open("C:\Scripts\ado_object_model.xls")
$ws = $xl.Sheets.Add()

________________________________________________________________________

How do I change the value of the active cell?

$xl.ActiveCell.Value2 = "x"

________________________________________________________________________

How do I change the value of a specified cell?

$xl.ActiveSheet.Range("B1").Value2 = "y"

________________________________________________________________________

How do I list the workbook's name?

$wb.Name

________________________________________________________________________

How do I loop through a range of cells by row number?

$xl = new-object -comobject excel.application
$xl.Visible = $true
$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1)

for ($row = 1; $row -lt 11; $row++)
{
$ws.Cells.Item($row,1) = $row
}

________________________________________________________________________

How do I write a list of files to Excel?

$xl = new-object -comobject excel.application
$xl.Visible = $true
$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1)

$row = 1
$s = dir
$s | foreach -process `
{ `
$ws.Cells.Item($row,1) = $_; `
$row++ `
}

________________________________________________________________________

How do I write a list of processes to Excel?

function Release-Ref ($ref) {

([System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]
$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

}

# -----------------------------------------------------

$xl = New-Object -comobject Excel.Application

$xl.Visible = $True

$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1)
$range = $wst.Cells.Item(1,1)

$row = 1
$s = Get-Process | Select-Object name
$s | foreach -process { `
$range = $ws.Cells.Item($row,1); `
$range.value2 = $_.Name; $row++ }

$xl.DisplayAlerts = $False
$wb.SaveAs("C:\Scripts\Get_Process.xls")

Release-Ref $range

Release-Ref $ws

Release-Ref $wb

$xl.Quit()

Release-Ref $xl

________________________________________________________________________

How do I open a workbook?

#an existing Workbook
$xl.Workbooks.Open("C:\Scripts\ado_object_model.xls")

________________________________________________________________________

How do I open a new workbook?

$xl = new-object -comobject excel.application
$xl.Visible = $true
$wb = $xl.Workbooks.Add()
$xl.ActiveCell.Value2 = "x"
$xl.ActiveSheet.Range("B1").Value2 = "y"


________________________________________________________________________

How do I write the command history to Excel?

function Release-Ref ($ref) {

([System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]
$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

}

# -----------------------------------------------------

$xl = New-Object -comobject Excel.Application

$xl.Visible = $True

$wb = $excel.Workbooks.Add()
$ws = $workbook.Worksheets.Item(1)
$range = $worksheet.Cells.Item(1,1)

$row = 1
$s = Get-History | Select-Object CommandLine $s | foreach -process { `
$range = $worksheet.Cells.Item($row,1); `
$range.value2 = $_.CommandLine; `
$row++ }

$xl.DisplayAlerts = $False
$wb.SaveAs("C:\Scripts\Get_CommandLine.xls")

Release-Ref $range

Release-Ref $ws

Release-Ref $wb

$xl.Quit()

Release-Ref $xl


________________________________________________________________________

How Can I Convert a Tilde-Delimited File to Microsoft Excel Format?

# Script name: ConvertTilde.ps1
# Created on: 2007-01-06
# Author: Kent Finkle
# Purpose: How Can I Convert a Tilde-Delimited File to Microsoft Excel
Format?

$s = gc C:\Scripts\Test.txt
$s = $s -replace("~","`t")
$s | sc C:\Scripts\Test.txt
$xl = new-object -comobject excel.application
$xl.Visible = $true
$wb = $xl.Workbooks.Open("C:\Scripts\Test.txt")


________________________________________________________________________

Add Validation to an Excel Worksheet

$comments = @'
Script name: Add-Validation.ps1
Created on: Wednesday, September 19, 2007
Author: Kent Finkle
Purpose: How can I use Windows Powershell to Add Validation to an
Excel Worksheet?
'@

#-----------------------------------------------------
function Release-Ref ($ref) {
([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
[System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}
#-----------------------------------------------------

$xlValidateWholeNumber = 1
$xlValidAlertStop = 1
$xlBetween = 1

$xl = new-object -comobject excel.application
$xl.Visible = $True

$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1)
$r = $ws.Range("e5")
$r.Validation.Add($xlValidateWholeNumber, ` $xlValidAlertStop,
$xlBetween, "5", "10") $r.Validation.InputTitle = "Integers"
$r.Validation.ErrorTitle = "Integers"
$r.Validation.InputMessage = "Enter an integer from five to ten"
$r.Validation.ErrorMessage = "You must enter a number from five to
ten"

$a = Release-Ref $r
$a = Release-Ref $ws
$a = Release-Ref $wb
$a = Release-Ref $xl

________________________________________________________________________

Add a Chart to an Excel Worksheet

$row = 8
$yrow = 9
$xlBarStacked = 58
$xl = New-Object -c excel.application
$xl.visible = $true
$wb = $xl.workbooks.add()
$sh = $wb.sheets.item(1)
$range = $sh.range("a${row}:b$yrow")
$range.activate
# create and assign the chart to a variable
$ch = $sh.shapes.addChart().chart
$ch.chartType = $xlBarStacked
$ch.setSourceData($range)

# excel has 48 chart styles, you can cycle through all
1..48 | % {$ch.chartStyle = $_; $xl.speech.speak("Style $_"); sleep
1}
$ch.chartStyle = 27 # <-- use the one you
like



________________________________________________________________________

Sort a column in an Excel Worksheet

$xlSummaryAbove = 0
$xlSortValues = $xlPinYin = 1
$xlAscending = 1
$xlDescending = 2


# one-column sort --> works
[void]$range1.sort($range2, $xlAscending)
[void]$range1.sort($range3, $xlAscending)
# two-column sort --> doesn't sort both columns
# the 4th arg [xlSortType] gives problems so pass $null or '',
# it may be for PivotTables only
[void]$range1.sort($range2, $xlAscending, $range3, '', $xlAscending)

________________________________________________________________________

SubTotal a column in an Excel Worksheet________________________________________________________________________

How to use xlConstants


$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("d:\book1.xls")
$ws = $wb.worksheets | where {$_.name -eq "sheet1"}
$selection = $ws.range("A11")
$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
________________________________________________________________________

About autofill in excel

$xlFillWeekdays = 6

$xl = new-object -com excel.application
$xl.visible=$true
$wb = $xl.workbooks.add()
$ws = $wb.worksheets | where {$_.name -eq "sheet1"}
$range1= $sheet.range("A1")
$range1.value() = (get-date).toString("d")
$range2 = $sheet.range("A1:A25")
$range1.AutoFill($range2,$xlFillWeekdays)
$range1.entireColumn.Autofit()
# $wb.close()
# $xl.quit()

________________________________________________________________________

How to get a range in excel

# get-excelrange.ps1
# opens an existing workbook in Excel 2007, using PowerShell
# and turns a range bold # Thomas Lee - tfl@xxxxxx

# Create base object
$xl = new-object -comobject Excel.Application

# make Excel visible
$xl.visible = $true

# open a workbook
$wb = $excel.workbooks.open("C:\Scripts\xlsx1.xlsx")

# Get sheet1
$ws = $wb.worksheets | where {$_.name -eq "sheet1"}

# Make A1-B1 bold
$range = $ws.range("A1:B1")
$range.font.bold = "true"

# Make A2-B2 italic
$range2 = $sheet1.range("A2:B2")
$range2.font.italic = "true"

# Set range to a value
$range3=$ws.range("table1")
$Range3.font.size=24

# now format an entire row
$range4=$ws.range("3:3")
$range4.cells="Row 3"
$range4.font.italic="$true"
$range4.font.bold=$True
$range4.font.size=10
$range4.font.name="comic Sans MS"

________________________________________________________________________

How do I add a comment to a cell in Excel

$xll = New-Object -com Excel.Application
$xl.visible = $True
$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1)
$ws.Cells.Item(1,1) = “A value in cell A1.”
[void]$ws.Range("A1").AddComment()
[void]$ws.Range("A1").comment.Visible = $False
[void]$ws.Range("A1").Comment.text("OldDog: `r this is a comment")
[void]$ws.Range("A2").Select

The 'r adds a line feed after the comment's author. This is required!

________________________________________________________________________

How do I copy and Paste Special in Excel

$xlPasteValues = -4163 # Values only, not formulas
$xlCellTypeLastCell = 11 # to find last used cell

$used = $ws.usedRange
$lastCell = $used.SpecialCells($xlCellTypeLastCell)
$row = $lastCell.row

$range = $ws.UsedRange
[void]$ws.Range("A8:F$row").Copy()
[void]$ws.Range("A8").PasteSpecial(-4163)

________________________________________________________________________

This is a two-thumbs-up post, Mike.

"OldDog" <mikef2691@xxxxxx> wrote in message
news:9cc1bc2c-8337-4299-9fc3-e26f30a1d19f@xxxxxx

On Aug 28, 11:50*am, "Alex K. Angelopoulos" <aka(at)mvps.org> wrote:Comming from you two, that is very flatering indeed.

Marco;

If I had any idea how to set up a Blog and if anyone would be foolish
enough to read what I wrote, I might consider it.

OldDog

Fantastic!
Thank you for posting this.
Kind regards,
Stuart

FYI

For those interested, the Office Web
Components (OWC) Spreadsheet
component also works almost the
same way!

Be aware that the (OWC) Office Web
Components (a chart, a data source,
a pivot table and the spreadsheet component),
run invisible (not seen) in a PowerShell
(non WPF) window. But they are visible
when run in a HTA window, a browser window
or a .NET form based window (etc.) that all
"run" within PowerShell.

Anyway, just replace

How do I create an Excel object?

$xl = new-object -comobject excel.application

with

How does one create the Office Web
Components (OWC) Spreadsheet
component's COM object?

Try number (versions) 10, or 11 or 12.

$owcSp = new-object -comobject OWC10.Spreadsheet

From here on out, a lot of the actions
are the same.

Oh and do not forget the .NET way of
doing Excel or the newer Excel Services
or for the OWC Spreadsheet too! After all,
the .NET is the PowerShell's main way!

Nice OD, it's good to share

--
Kiron

Great post. But I'm getting an error message on the line that creates the
chart and assigns to $ch: "Method invocation failed because
[System.__ComObject] doesn't contain a method named 'addChart'
The 'Shapes' property of $sh appears empty.
Everything appears find but I'm unfamiliar with using COM objects.
Any ideas?
- Doug"OldDog" wrote:

It seems there were a few bugs in the chart example.
This TechNet Scripting post was helpful:
http://www.microsoft.com/technet/scr...7/hey0208.mspx

Thanks again for this post. I'd love to see more like this. Here are my
modifications:

$xrow = 1
$yrow = 8
$xl = New-Object -c excel.application
$xl.visible = $true
$wb = $xl.workbooks.add()
$sh = $wb.sheets.item(1)
1..8 | % { $sh.Cells.Item(1,$_) = $_ }
1..8 | % { $sh.Cells.Item(2,$_) = 9-$_ }

$range = $sh.range("a${xrow}:h$yrow")
$range.activate
# create and assign the chart to a variable
$ch = $xl.charts.add()
$ch.chartType = 58
$ch.setSourceData($range)
$ch.export("C:\test.jpg")
$xl.quit()

"Doug" wrote:

Hello OldDog
good stuff thanks, Iam using excel 2007 so i have the USlocal problem , I
can open excel and workbook but Iam having trouble with $s3 =
$workbook.sheets.item() |where {$_.name -eq "Sheet3"}
$s3.delete()
I get this error "You cannot call a method on a null-valued expression.
At line 17, position 11
$s3.delete()"
I'd say it's a syntax problem
cheers oldtechie
"OldDog" wrote:

This is a wonderful refrence.
Though just one question.
How can you open an excel file find the used range then add data just after
what ever the last used row?