Hey, Scripting Guy! How can I use Windows PowerShell to automate Microsoft Excel?

— MW

Hey, MW. Yes, it’s true: the week of November 6-10, 2006 will be Windows PowerShell Week on TechNet, with the Scripting Guys presenting a series of webcasts (one each day, Monday through Friday) designed to introduce you to Windows PowerShell. Those are all the details at the moment (we’ll keep you posted), but you can bet that the week will be filled with fun and excitement as we show how to carry out all sorts of useful system administration tasks using Windows PowerShell.

What’s that? Well, we suppose it is like a dream come true, assuming that you always dream about Windows PowerShell, of course. And no, that’s OK: all things considered, we’d just as soon not know what it is you always dream about.

So, MW, are we going to make you wait until November before we show you how you can use Windows PowerShell to automate Microsoft Excel? Well, to tell you the truth, that was the original plan. But then we realized that we’d have to go read a few more emails, that we’d have pick out a different question to answer, that we’d – well, needless to say, that sounded like an awful lot of work. Especially when you consider how easy it was to just write a sample script that uses Windows PowerShell to automate Microsoft Excel:

$a = New-Object -comobject Excel.Application

$a.Visible = $True
$b = $a.Workbooks.Add()
$c = $b.Worksheets.Item(1)
$c.Cells.Item(1,1) = “A value in cell A1.”
$b.SaveAs(“C:\Scripts\Test.xls”)
$a.Quit()

The key to this script occurs in the very first line, the one where we use the New-Object Cmdlet to create an instance of Microsoft Excel. Notice that we need to pass New-Object two items: the –comobject parameter, which tells the Cmdlet that we want to create a new COM object (as opposed to, say, a new .NET Framework object); and Excel.Application, the ProgID of the object we want to create. And no, we didn’t make a mistake (well, not here anyway): you don’t need to enclose the ProgID in double quote marks.

And you wondered why people were so excited about Windows PowerShell!

In effect, line 1 is equivalent to this line of VBScript code:

Set a = CreateObject(“Excel.Application”)

Although the lines are functionally equivalent, there are a couple differences we should point out. For one, in Windows PowerShell we don’t have to use the Set keyword (or some equivalent) in order to create an object reference. For another, we do need to use a variable name like $a; in Windows PowerShell variable names must begin with a dollar sign ($).

Minor things, sure, but things you need to know.

Now, back to the script. In the next line we set the Visible property of Excel to true, thus enabling us to see the application onscreen:

$a.Visible = $True

That should be reasonably familiar: you set property values in Windows PowerShell using the “dot” notation just like you do in VBScript. The only real difference here? We set the Visible property to $True, prefacing the built-in variable with a dollar sign. Remember when we said you had to preface variable names with a dollar sign? There’s a reason why we mentioned that.

Come to think of it, yes, that just might be the first time we ever had a valid reason for saying something in this column.

That brings us to the following two lines of code, lines where we use the Add method to add a new workbook to our instance of Excel, then use the Item property to create an object reference to the first worksheet in that workbook:

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

Thanks for pointing that out: the second line is a tad bit different than what you’d see in VBScript, isn’t it? In VBScript we create an object reference to worksheet 1 using code similar to this:

Set c = b.Worksheets(1)

That’s easy enough: we want worksheet 1 so we specify Worksheets(1). Then why, in Windows PowerShell, do we refer to worksheet 1 as Worksheets.Item(1)?

Well, as it turns out, the Item property is the default property for the Worksheet object. In VBScript you can usually get away without explicitly referencing the default property; if you don’t specify a property name then VBScript uses the default property. That’s not the case with Windows PowerShell, however. Suppose we left out the default property and tried creating an object reference using code like this:

$c = $b.Worksheets(1)

What’s going to happen in that case? This:

Method invocation failed because [System.__ComObject] doesn’t contain a method named ‘Worksheets’.

In other words, make sure you always include the property name, even when dealing with the default property.

Set c = b.Worksheets.Item(1)

The next two lines of code are there just to show you how to actually do something with Excel once the application is up and running. In line 1, we add some text to cell A1 (row 1, column 1); again, notice that we explicitly referenc the Item property when dealing with the cells in the worksheet. After modifying cell A1 we then call the SaveAs method to save the spreadsheet as C:\Scripts\Test.xls:

$c.Cells.Item(1,1) = “A value in cell A1.”
$b.SaveAs(“C:\Scripts\Test.xls”)

Speaking of methods, any time you call a method in Windows PowerShell you must include parentheses on the end, even if those parentheses are empty. For example, in VBScript you can call the Save method using code similar to this:

b.Save

That won’t do you much good in Windows PowerShell, however. Instead, you need to tack on a set of parentheses, which tells Windows PowerShell that we’re using a method:

$b.Save()

And, yes, we know. But you’ll get used to it in no time.

After we save the spreadsheet we call the Quit method (there’s those parentheses again) and we’re done:

$a.Quit()

Hope that gets you started, MW. As for everyone else, if today’s column seemed a bit mystifying, mainly because you’ve never even heard of Windows PowerShell, well, you might want to take a peek at our Windows PowerShell center. Oh: and did we mention that Windows PowerShell Week is coming November 6-10, 2006? Well, we should have: undoubtedly those 5 webcasts will represent the best thing that’s happened to Windows PowerShell since … well, Windows PowerShell is brand-new, so not a lot has actually happened to it yet. But you know what we mean.

Top of page