How Can I Use Windows PowerShell to Automate Microsoft Excel?

How Can I Use Windows PowerShell to Automate Microsoft Excel?

  • Comments 14
  • Likes
Hey, Scripting Guy! Question

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

-- MW

SpacerHey, Scripting Guy! AnswerScript Center

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.

Note. What do the Scripting Guys dream about? Surprisingly enough, none of the Scripting Guys dream. But, then again, maybe that’s not so surprising: after all, when you work at Microsoft you’re living the dream.

More or less.

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.

Note. Interestingly enough, VBScript lets you do this any which way you want. Want to leave out the default property name? That's fine. But what if you want to reference the default property name? Then go ahead and do so; this line of code works just fine in VBScript:

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.

Update: Windows PowerShell Week is over, but you can watch the webcasts on-demand anytime.


Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • How can I use PowerShell to clean up Carriage Returns and slashes from an Excel spreadsheet before converting it to a CSV?

    Perhaps using the clean function? or invoking something similar to CTRL+H and passing the CTRL+J carriage return representation?

    Your assistance is greatly appreciated.

  • I am running below code -

    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Interop.Excel")

    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Core")

    [Microsoft.Office.Interop.Excel.Application]$xl =   new-object Microsoft.Office.Interop.Excel.ApplicationClass

    $file="D:\test\test.csv"

    $xl.visible = $true

    $wbk = $xl.Workbooks.Open("$file")

    $wks = $wbk.worksheets.item(1)

    $r1=[Microsoft.Office.Interop.Excel.IRange]$wks.Range("A1:D1")

    $r2=[Microsoft.Office.Interop.Excel.IRange]$wks.Range("A2:D2")

    #Commented code

    #$mis = [System.Reflection.Missing]::Value;

    #$xl.Union($r5, $r6, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis)

    .............

    It throws below error while I try to typecast _ComObject returned to IRange

    Error message -

    $r1=[Microsoft.Office.Interop.Excel.IRange]$wks.Range <<<< ("A1:D1")

       + CategoryInfo          : NotSpecified: (:) [], RuntimeException

       + FullyQualifiedErrorId : RuntimeException

    Typecasting is required otherwise the next command to union two range (commented code) fails with same error.

    I am stuck on it. Please advice.

  • I'm running the code in your example,

    $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()

    and generate the following error.  Any ideas?

    PS H:\> c:\data\ACE-IT\projects\TabletConfiguration\test.ps1

    Exception calling "Add" with "0" argument(s): "The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT))"

    At C:\data\ACE-IT\projects\TabletConfiguration\test.ps1:5 char:22

    + $b = $a.Workbooks.Add <<<< ()

       + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException

       + FullyQualifiedErrorId : ComMethodTargetInvocation

    You cannot call a method on a null-valued expression.

    At C:\data\ACE-IT\projects\TabletConfiguration\test.ps1:6 char:24

    + $c = $b.Worksheets.Item <<<< (1)

       + CategoryInfo          : InvalidOperation: (Item:String) [], RuntimeException

       + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.

    At C:\data\ACE-IT\projects\TabletConfiguration\test.ps1:8 char:14

    + $c.Cells.Item <<<< (1,1) = "A value in cell A1."

       + CategoryInfo          : InvalidOperation: (Item:String) [], RuntimeException

       + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.

    At C:\data\ACE-IT\projects\TabletConfiguration\test.ps1:9 char:10

    + $b.SaveAs <<<< ("C:\temp\Test.xls")

       + CategoryInfo          : InvalidOperation: (SaveAs:String) [], RuntimeException

       + FullyQualifiedErrorId : InvokeMethodOnNull

  • hello,

    pls, how i can us the methode : .Calculate, .CalculateFull.....

    thx

    my best regard

  • If I have my PowerShell running to do Excel automation using Windows Scheduler, how do I avoid conflict with other Excel users on the same machine when my job is running? The problem is other Excel users will get affect by my job when they are using Excel while the job is running. Thanks in advance.

  • For Windows 8, I had to do a slight modification...  I can't save to the C-drive in Windows 8 using PowerShell, so....

    # Create directory if does not exist

    if ((Test-Path -Path "C:\Scripts") -ne $True)

    {

       New-Item C:\Scripts -type directory

    }

    $a = New-Object -comobject Excel.Application

    $a.Visible = $True

    $a.DisplayAlerts = $false

    $b = $a.Workbooks.Add()

    $c = $b.Worksheets.Item(1)

    $c.Cells.Item(1,1) = "A value in cell A1."

    $b.SaveAs("C:\Scripts\Test.xlsx")

    $a.Quit()

  • How to compare data from two different excel sheets and also need to display differences?

  • This site is very useful for learning excel automation

  • I have doubt in doing sorting for numbers from largest to smallest by automation..can anybody give me solution for tis?...

  • Sort a column:

    $excel = new-object -com Excel.Application

    $excel.Visible=$true

    $workbook = $excel.Workbooks.Open('c:\scripts\test.xlsx')

    $worksheet=$workbook.Sheets.Item(1)

    $range = $worksheet.UsedRange

    $range2 = $excel.Range("A1")

    [void] $range.Sort($range2)

  • Am creating pivot table in excel using powershell commands.in tat pivot table i need to filter one column values from largest to smallest..is it possible in automation?

  • This works fine if you run it from the console. I cannot run it from a scheduled task because I get this error New-Object : Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80080005 Server execution failed (Exception from HRESULT: 0x80080005 (CO_E_SERVER_EXEC_FAILURE)). At C:\PowerShell\BatchConvertXLS.ps1:58 char:14 + $Excel = New-Object -Com Excel.Application + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : ResourceUnavailable: (:) [New-Object], COMExcept ion + FullyQualifiedErrorId : NoCOMClassIdentified,Microsoft.PowerShell.Comman ds.NewObjectCommand Property 'visible' cannot be found on this object; make sure it exists and is settable. At C:\PowerShell\BatchConvertXLS.ps1:59 char:5 + $Excel.visible = $False + ~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : PropertyNotFound Property 'displayalerts' cannot be found on this object; make sure it exists and is settable Any ideas?

  • I am seeing the similar issue as Javier Villegas. My script runs fine when ran in console but fails with the error when ran through task scheduler.

    New-Object : Retrieving the COM class factory for component with CLSID
    {0002DF01-0000-0000-C000-000000000046} failed due to the following error:
    80080005 Server execution failed (Exception from HRESULT: 0x80080005
    (CO_E_SERVER_EXEC_FAILURE)).
    At E:\Scripts\Warmup_AllSites.ps1:18 char:5
    + $ie=New-Object -ComObject "InternetExplorer.Application"
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : ResourceUnavailable: (:) [New-Object], COMExcept
    ion
    + FullyQualifiedErrorId : NoCOMClassIdentified,Microsoft.PowerShell.Comman
    ds.NewObjectCommand

    Property 'visible' cannot be found on this object; make sure it exists and is
    settable.
    At E:\Scripts\Warmup_AllSites.ps1:20 char:1
    + $ie.visible = $ShowIE
    + ~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : PropertyNotFound

    You cannot call a method on a null-valued expression.
    At E:\Scripts\Warmup_AllSites.ps1:23 char:4
    + $ie.navigate($_.url)
    + ~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.
    At E:\Scripts\Warmup_AllSites.ps1:23 char:4
    + $ie.navigate($_.url)
    + ~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.
    At E:\Scripts\Warmup_AllSites.ps1:23 char:4
    + $ie.navigate($_.url)
    + ~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.
    At E:\Scripts\Warmup_AllSites.ps1:23 char:4
    + $ie.navigate($_.url)
    + ~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.
    At E:\Scripts\Warmup_AllSites.ps1:23 char:4
    + $ie.navigate($_.url)
    + ~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.
    At E:\Scripts\Warmup_AllSites.ps1:23 char:4
    + $ie.navigate($_.url)
    +

  • thanks