There are two basic ways to interact with Excel via the COM objects and via the interop assembly. Functionally I think the COM will allow you to accomplish the same tasks, but it will not be as easy. To load the interop you will need:
001
002 003 004 005 006 007 |
#Load the Excel Assembly, Locally or from GAC
try { Add-Type -ASSEMBLY "Microsoft.Office.Interop.Excel" | out-null }catch { #If the assembly can't be found this will load the most recent version in the GAC [Reflection.Assembly]::LoadWithPartialname("Microsoft.Office.Interop.Excel") | out-null } |
I tend to distribute the inerop DLL in my network share so I don't have to make sure that all servers and workstations have it installed. The above should take care of either loading a local assembly or looking in the GAC.
To access Excel data, you have to be aware of the hierarchy of things. At the top is the application class that contains one or more workbooks that contain one or more worksheets. Within the worksheet are ranges. Each layer can access down to some of the other layers.
001
002 003 004 005 006 007 008 009 |
Function Open-ExcelApplication {
Param([switch] $Visible,[switch] $HideAlerts) $app = New-Object Microsoft.Office.Interop.Excel.ApplicationClass $app.Visible = $Visible $app.DisplayAlerts = -not $HideAlerts return $app } $app = open-excelApplication -Visible $app | gm active* |
Yields:
Name MemberType Definition
---- ---------- ----------
ActiveCell Property Microsoft.Office.Interop.Excel.Range ActiveCell {get;}
ActiveChart Property Microsoft.Office.Interop.Excel.Chart ActiveChart {get;}
ActiveDialog Property Microsoft.Office.Interop.Excel.DialogSheet ActiveDialog ...
ActiveEncryptionSession Property int ActiveEncryptionSession {get;}
ActiveMenuBar Property Microsoft.Office.Interop.Excel.MenuBar ActiveMenuBar {get;}
ActivePrinter Property string ActivePrinter {get;set;}
ActiveProtectedViewWindow Property Microsoft.Office.Interop.Excel.ProtectedViewWindow Activ...
ActiveSheet Property System.Object ActiveSheet {get;}
ActiveWindow Property Microsoft.Office.Interop.Excel.Window ActiveWindow {get;}
ActiveWorkbook Property Microsoft.Office.Interop.Excel.Workbook ActiveWorkbook {...
All of the classes also have a .Application property that points back to the top.
001
002 003 004 005 006 007 008 009 010 011 012 013 |
function New-ExcelWorkBook {
Param([parameter(ValueFromPipeline=$true)] $ExcelApplication ,[switch] $Visible) process { if ($ExcelApplication -eq $null ) { $ExcelApplication = Open-ExcelApplication -Visible:$Visible } $WorkBook = $ExcelApplication.WorkBooks.Add() return $WorkBook } } $book = $app | New-ExcelWorkBook $book | gm active* |
Yields:
TypeName: System.__ComObject#{000208da-0000-0000-c000-000000000046}
Name MemberType Definition
---- ---------- ----------
ActiveChart Property Chart ActiveChart () {get}
ActiveSheet Property IDispatch ActiveSheet () {get}
ActiveSlicer Property Slicer ActiveSlicer () {get}
Alternately, you can open an existing workbook:
001
002 003 004 005 006 007 008 009 010 011 012 013 014 015 016 017 018 019 020 021 |
function Get-ExcelWorkBook {
Param([parameter(Mandatory=$true,ValueFromPipeline=$true)] $inputObject ,[switch] $Visible ,[switch] $readonly) [Microsoft.Office.Interop.Excel.ApplicationClass] $app = $null if ($inputObject -is [Microsoft.Office.Interop.Excel.ApplicationClass]) { $app = $inputObject $WorkBook = $app.ActiveWorkbook } else { $app = Open-ExcelApplication -Visible:$Visible try { if ($inputObject.Contains("\\") -or $inputObject.Contains("//")) { $WorkBook = $app.Workbooks.Open($inputObject,$true,[System.Boolean]$readonly) } else { $WorkBook = $app.Workbooks.Open((Resolve-path $inputObject),$true,[System.Boolean]$readonly) }} catch {$WorkBook = $app.Workbooks.Open((Resolve-path $inputObject),$true,[System.Boolean]$readonly)} } #todo: Add Switch to toggle Full Rebuild (this does an update data) $app.CalculateFullRebuild() return $WorkBook } |
The Interop allows you easy access to the classes and enumerations. The largest caveat is what you may expect vs what you get when you look at the COM collections. These collections are built implementing default properties that do not come across in powershell. A recorded macro may reference WorkSheets("Sheet1") but in PS you will need to say $WorkSheets.item("Sheet1"). So, what looks like it may be an array may need a call to the item property to do what you expect.
When you look at Excel you see cells, when you automate it you have ranges.
001
002 |
$Sheet = $Book.Worksheets.item("Sheet1")
$sheet | gm -MemberType *Property | where { $_.Definition -match "Range" } |
Yields:
TypeName: System.__ComObject#{000208d8-0000-0000-c000-000000000046}
Name MemberType Definition
---- ---------- ----------
Range ParameterizedProperty Range Range (Variant, Variant) {get}
Cells Property Range Cells () {get}
CircularReference Property Range CircularReference () {get}
Columns Property Range Columns () {get}
Rows Property Range Rows () {get}
UsedRange Property Range UsedRange () {get}
All of the following are the same:
001
002 003 004 005 006 007 |
$sheet.Range("A1").Text
$sheet.Range("A1:A1").Text $sheet.Range("A1","A1").Text $sheet.cells.Item(1,1).text $sheet.Columns.Item(1).Rows.Item(1).Text $sheet.Rows.Item(1).Columns.Item(1).Text $sheet.UsedRange.Range("a1").Text |
If you were going to use a formula in a cell, this same convention is used for the .Range ParameterizedProperty. Cells you have to use the .Item property but you can more easily use in loops as the cell is a coordinate. UsedRange is limited to the cells that have or have had data in them as a block. So furthest X and furthest Y make up the range. If you want to know what these bounds are:
001
002 |
$sheet.UsedRange.Columns.Count
$sheet.UsedRange.Rows.Count |
To close it all down you can do something like:
001
002 003 004 005 006 007 008 009 010 011 012 |
Function Close-ExcelApplication {
Param([parameter(Mandatory=$true,ValueFromPipeline=$true)] $inputObject) if ($inputObject -is [Microsoft.Office.Interop.Excel.ApplicationClass]) { $app = $inputObject } else { $app = $inputObject.Application Release-Ref $inputObject } $app.ActiveWorkBook.Close($false) | Out-Null $app.Quit() | Out-Null Release-Ref $app } |
This will work fine if you only have one workbook open with changes. If you have more than one open you will need to make sure that you close or save each sheet. If you try to close and have more than the active workbook not saved, excel will prompt you to save. This is not something that you want if you expect your script to run unattended.
Another big point to consider is the garbage collection. I know it was a big concern with Office 2003 and may be unneeded in 2007 or 2010, but an extra step to clean up your variables should be used.
001
002 003 004 005 |
function Release-Ref ($ref) {
([System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$ref) -gt 0) | Out-Null [System.GC]::Collect() | Out-Null [System.GC]::WaitForPendingFinalizers() | Out-Null } |
If you don't do this, Excel may (may) fail to close.
More later...
