Excel Cook Book
OldDog
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
> mon tue wed
> eggs 1 1 1
> ham 5 5 5
> spam 1 4 7
> spam 2 5 8
> spam 3 6 9
> $range = $xl.range("A1:D6")
> $range.Subtotal(1,-4157,(2,3,4),$true,$false,$true)
________________________________________________________________________
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("A1:D1")
$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 - t...@psp.co.uk
# 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)
________________________________________________________________________
Marco Shaw [MVP]
> As promissed;
>
> OldDogs Excel Cook Book
>
> All errors and mistakes are my own. Thanks to all who have helped
> along the way.
Totally cool of you to post this.
I wonder if PowerTools can do all of this:
http://www.codeplex.com/PowerTools
You should set yourself up a blog, and post these as they come along...
Marco
--
*Microsoft MVP - Windows Server - Admin Frameworks
https://mvp.support.microsoft.com/profile/Marco.Shaw
*PowerShell Co-Community Director - http://www.powershellcommunity.org
*Blog - http://marcoshaw.blogspot.com
Alex K. Angelopoulos at
"OldDog" <mike...@comcast.net> wrote in message
news:9cc1bc2c-8337-4299...@r15g2000prd.googlegroups.com...
OldDog
> This is a two-thumbs-up post, Mike. :)
>
>
> read more »- Hide quoted text -
>
> - Show quoted text -
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
Marco Shaw [MVP]
> 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
Do you have a Microsoft Live ID or Google/gmail account? Both offer a
free blog...
I *think* to sign up:
-Microsoft Live ID: go to www.live.com
-Google: go to www.blogspot.com
Marco
Kryten
Thank you for posting this.
Kind regards,
Stuart
Flowering Weeds
> OldDogs Excel Cook Book
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!
OldDog
wrote:
OK, I set one up at
Kiron
--
Kiron
RichS [MVP]
One thing I would add is that if you are using a non US locale some of
these scripts won't work especially adding and saving workbooks
http://richardsiddaway.spaces.live.com/blog/cns!43CFA46A74CF3E96!1204.entry
--
Richard Siddaway
All scripts are supplied "as is" and with no warranty
PowerShell MVP
Blog: http://richardsiddaway.spaces.live.com/
PowerShell User Group: http://www.get-psuguk.org.uk
Doug
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
> 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
Doug
This TechNet Scripting post was helpful:
http://www.microsoft.com/technet/scriptcenter/resources/qanda/feb07/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()
OldDog
> It seems there were a few bugs in the chart example.
Thanks, Doug
Like I said, all errors and omissions are my own. I am glad you found
it and fixed it.
OD
oldtechie
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
Marco Shaw [MVP]
> 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
Check $s3 before you try to delete. Maybe you don't have a "Sheet3"?
OldDog
wrote:
Yeah, what Marco said. Make sure $s3 exist before you delete it.
Jim
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?
OldDog
<------ PS Script --------------------------------->
$xlCellTypeLastCell = 11
$xl = New-Object -c excel.application
$wb = $xl.Workbooks.Open("C:\Scripts\Book5.xlsx") #<-- Your
spreadsheet name goes here
$sh = $wb.worksheets.item('Sheet1') #<-- the sheet number or name goes
here.
$xl.visible = $true
$used = $sh.usedRange
$lastCell = $used.SpecialCells($xlCellTypeLastCell)
$newRow = $lastCell.row + 1
$sh.cells.item($newRow, 1).value2 = 'This is the new last row now'
<---------------- end Script ---------------------------->