Quick and Dirty Excel from PowerShell

I continue to tinker with Office applications and Windows PowerShell. I was looking at an Excel issue related to opening a new workbook. To verify the problem wasn't PowerShell related I offered a suggestion to try creating an Excel workbook in VBScript.

In VBScript creating a blank workbook in Microsoft Excel can be accomplished with three lines of code.


set xl=wscript.createObject("Excel.Application")
set wb=xl.workbooks.add()
xl.Visible=vbTrue


In Windows PowerShell we essentially use the same 3 lines.


$xl=new-object -com "excel.application"
$sb=$xl.Workbooks.Add()
$xl.Visible=$True


But PowerShell is all about the objects, so how about a quick and dirty approach? Something perhaps in one line:


PS S:\> ((new-object -com "excel.application").Workbooks.Add()).application.Visible=$True


You could easily build a simple function to run the 3 lines it takes to open a new Excel workbook but one-liners like this make me smile. Granted, I'm probably not likely to use it, but if nothing else I think it reinforces the object nature of Windows PowerShell.

Each expression nested in () is treated as an object. The inner most expression using New-Object, creates the Excel application object which has a Workbooks property that is an object with an Add() method. Invoking this method creates the workbook object. If I stopped there Excel would be running but not visible. So I get the Application property of the workbook object, and set the Visible property to True.

This command doesn't offer any way to continue working with Excel from PowerShell; it only opens a new workbook. Let me be clear, I'm not advocating that this is how you work with Excel from PowerShell. I think you will need scripts like the ones I posted from my Deep Dive session. But this one-liner demonstrates what is possible with PowerShell and maybe it will add a little smile to your day as well.

---

---