{"id":66513,"date":"2006-09-08T15:24:00","date_gmt":"2006-09-08T15:24:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2006\/09\/08\/how-can-i-use-windows-powershell-to-automate-microsoft-excel\/"},"modified":"2006-09-08T15:24:00","modified_gmt":"2006-09-08T15:24:00","slug":"how-can-i-use-windows-powershell-to-automate-microsoft-excel","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-use-windows-powershell-to-automate-microsoft-excel\/","title":{"rendered":"How Can I Use Windows PowerShell to Automate Microsoft Excel?"},"content":{"rendered":"

\"Hey, \n

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

— MW<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, MW. Yes, it\u2019s 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\u2019ll 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.<\/P>\n

What\u2019s that? Well, we suppose it is<\/I> like a dream come true, assuming that you always dream about Windows PowerShell, of course. And no, that\u2019s OK: all things considered, we\u2019d just as soon not<\/I> know what it is you always dream about.<\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. What do the Scripting Guys<\/I> dream about? Surprisingly enough, none of the Scripting Guys dream. But, then again, maybe that\u2019s not so surprising: after all, when you work at Microsoft you\u2019re living<\/I> the dream.<\/P>\n

More or less.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

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\u2019d have to go read a few more emails, that we\u2019d have pick out a different question to answer, that we\u2019d \u2013 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:<\/P>

$a = New-Object -comobject Excel.Application<\/p>\n
$a.Visible = $True<\/p>\n
$b = $a.Workbooks.Add()\n$c = $b.Worksheets.Item(1)<\/p>\n
$c.Cells.Item(1,1) = “A value in cell A1.”\n$b.SaveAs(“C:\\Scripts\\Test.xls”)<\/p>\n
$a.Quit()\n<\/PRE>\n
The key to this script occurs in the very first line, the one where we use the New-Object<\/B> Cmdlet to create an instance of Microsoft Excel. Notice that we need to pass New-Object two items: the \u2013comobject<\/B> 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<\/B>, the ProgID of the object we want to create. And no, we didn\u2019t make a mistake (well, not here anyway): you don\u2019t need to enclose the ProgID in double quote marks.<\/P>\n
And you wondered why people were so excited about Windows PowerShell!<\/P>\n
In effect, line 1 is equivalent to this line of VBScript code:<\/P>
Set a = CreateObject(“Excel.Application”)\n<\/PRE>\n
Although the lines are functionally equivalent, there are a couple differences we should point out. For one, in Windows PowerShell we don\u2019t have to use the Set keyword (or some equivalent) in order to create an object reference. For another, we do<\/I> need to use a variable name like $a; in Windows PowerShell variable names must begin with a dollar sign ($).<\/P>\n
Minor things, sure, but things you need to know.<\/P>\n
Now, back to the script. In the next line we set the Visible<\/B> property of Excel to true, thus enabling us to see the application onscreen:<\/P>
$a.Visible = $True\n<\/PRE>\n
That should be reasonably familiar: you set property values in Windows PowerShell using the \u201cdot\u201d notation just like you do in VBScript. The only real difference here? We set the Visible property to $<\/B>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.<\/P>\n
Come to think of it, yes, that just might<\/I> be the first time we ever had a valid reason for saying something in this column.<\/P>\n
That brings us to the following two lines of code, lines where we use the Add<\/B> method to add a new workbook to our instance of Excel, then use the Item<\/B> property to create an object reference to the first worksheet in that workbook:<\/P>
$b = $a.Workbooks.Add()\n$c = $b.Worksheets.Item(1)\n<\/PRE>\n
Thanks for pointing that out: the second line is<\/I> a tad bit different than what you\u2019d see in VBScript, isn\u2019t it? In VBScript we create an object reference to worksheet 1 using code similar to this:<\/P>
Set c = b.Worksheets(1)\n<\/PRE>\n
That\u2019s easy enough: we want worksheet 1 so we specify Worksheets(1)<\/B>. Then why, in Windows PowerShell, do we refer to worksheet 1 as Worksheets.Item(1)<\/B>?<\/P>\n
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\u2019t specify a property name then VBScript uses the default property. That\u2019s not the case with Windows PowerShell, however. Suppose we left out the default property and tried creating an object reference using code like this:<\/P>
$c = $b.Worksheets(1)\n<\/PRE>\n
What’s going to happen in that case? This:<\/P>
Method invocation failed because [System.__ComObject] doesn’t contain a method named ‘Worksheets’.\n<\/PRE>\n
In other words, make sure you always include the property name, even when dealing with the default property.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. 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<\/I> to reference the default property name? Then go ahead and do so; this line of code works just fine in VBScript:<\/P>
Set c = b.Worksheets.Item(1)\n<\/PRE><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
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<\/B> property when dealing with the cells in the worksheet. After modifying cell A1 we then call the SaveAs<\/B> method to save the spreadsheet as C:\\Scripts\\Test.xls:<\/P>
$c.Cells.Item(1,1) = “A value in cell A1.”\n$b.SaveAs(“C:\\Scripts\\Test.xls”)\n<\/PRE>\n
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:<\/P>
b.Save\n<\/PRE>\n
That won\u2019t do you much good in Windows PowerShell, however. Instead, you need to tack on a set of parentheses, which tells Windows PowerShell that we\u2019re using a method:<\/P>
$b.Save()\n<\/PRE>\n
And, yes, we know. But you\u2019ll get used to it in no time.<\/P>\n
After we save the spreadsheet we call the Quit<\/B> method (there\u2019s those parentheses again) and we’re done:<\/P>
$a.Quit()\n<\/PRE>\n
Hope that gets you started, MW. As for everyone else, if today\u2019s column seemed a bit mystifying, mainly because you\u2019ve never even heard<\/I> of Windows PowerShell, well, you might want to take a peek at our Windows PowerShell<\/B><\/A> 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\u2019s happened to Windows PowerShell since \u2026 well, Windows PowerShell is brand-new, so not a lot has actually happened to it yet. But you know what we mean.<\/P>\n\n<\/THEAD>\n\n\n
\n
Update: Windows PowerShell Week<\/B><\/A> is over, but you can watch the webcasts on-demand anytime.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>
\n
\n\n\n\n
\"Top<\/A>Top of page<\/A><\/TD><\/TR><\/TBODY><\/TABLE><\/DIV><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I use Windows PowerShell to automate Microsoft Excel?— MW Hey, MW. Yes, it\u2019s 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 […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[711,48,49,3,45],"class_list":["post-66513","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-application","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-windows-powershell"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I use Windows PowerShell to automate Microsoft Excel?— MW Hey, MW. Yes, it\u2019s 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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66513","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=66513"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66513\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=66513"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=66513"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=66513"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}