This project is read-only.

Why Use PowerShell in Excel?

I can think of at least two reasons:
  • Having the Excel.Application object variable available in PowerShell enables you to use PowerShell as a replacement for Excel VBA. This may not seem as a big deal, but I believe that the number of PowerShell users will increase rapidly in the future, and being able to automate Excel object model using the familiar PowerShell syntax is a plus. Here is just a small example:

$workbook = $Application.ActiveWorkbook
$worksheet = $workbook.Sheets.Add()
$worksheet.Name = "My Sheet Name"

createsheet.jpg

Of course, you can always use Excel object model from 'standalone' PowerShell scripts, but being able to use PowerShell from within Excel adds flexibility. This could be particularly useful for system administrators for displaying or exporting output of their scripts.
  • Having PowerShell available while working in Excel provides another advantage for Excel users - it enables them to access the vastness of .Net Framework easily. Writing managed addins for Excel is not too difficult, but on the other hand it is not trivial. Using PowerShell, you can have .Net in your workbook using a couple simple lines:

$sheet = $Application.ActiveSheet
$sheet.Cells.Item(1, 1) = [Guid]::NewGuid().ToString()

$rand = New-Object Random
$sheet.Cells.Item(2, 1) = $rand.Next(100)

$sheet.Cells.Item(3, 1) =
[TimeZone]::CurrentTimeZone.StandardName

netframeworkinexcel.jpg

Documentation pages

Global Variables Support in Console
Read-Host Functionality
Prompt for Parameters
Read-ExcelRange (Cmdlet)
Write-Excel(Cmdlet)

Last edited Jan 15, 2011 at 2:51 PM by Urkec, version 16

Comments

No comments yet.