Hey, Scripting Guy! How Can I Tile Workbook Windows in Microsoft Excel?

ScriptingGuy1

Hey, Scripting Guy! I have a Windows PowerShell script that starts Excel and loads three comma-separated values files into separate workbooks. What I was wondering is whether there’s a way to have PowerShell tile these workbooks, either vertically or horizontally. I poked through various properties and searched the Web for ideas, but I couldn’t find anything. Any help would be greatly appreciated.
— RE

Hey, RE. In case you’re also wondering why there was no Hey, Scripting Guy! column yesterday that wasn’t because the Scripting Guy who writes this column is lazy. (He is lazy, but that isn’t why there was no column yesterday.) Instead, yesterday was Memorial Day in the US, one of those rare national holidays that even Microsoft employees get to take off. If there’s one thing you can say about the Scripting Guy who writes this column it’s this: if he doesn’t need to come into work and write this column, well, he’s not going to come into work and write this column.

For those of you unfamiliar with the holiday, Memorial Day was originally called Decoration Day, and was first held as a way to honor the memory of soldiers who died during the Civil War. (We tried to verify this with Peter Costantini, the oldest living Scripting Guy, but we never got an answer. Instead, Peter insisted on telling us – over and over again – how he tried to enlist in the Union army and fight in the Civil War, but was rejected because he was too old.) After World War II the meaning of Decoration Day was extended to make it a day in which Americans would pay homage to all the men and women who gave their lives for their country, regardless of which war they fought in.

For the first 100 years or so Memorial Day was always observed on May 30^th; in 1971, however, the date was switched to the last Monday in May, the better to turn Memorial Day into a three-day weekend. A number of people were – and still are – upset with the decision to turn Memorial Day into a three-day weekend, believing that it detracts from the original meaning of the holiday: to honor the men and women who lost their lives in military service to the country. But hey, it’s Memorial Day: a day for picnics, car racing, and great Memorial Day Blowout Sales! Who has time for anything else?

And yes, we are being facetious.

Not that it matters, mind you; after all, Memorial Day was yesterday, which means that today must be today. And if today is today, that means that we need to come up with a PowerShell script that can arrange workbook windows in Excel. You know, a script like this one:

$xlArrangeStyleHorizontal = -4128

$objExcel = New-Object -com Excel.Application
$objExcel.Visible = $True

$objWorkbook1 = $objExcel.Workbooks.Add()
$objWorkbook2 = $objExcel.Workbooks.Add()
$objWorkbook3 = $objExcel.Workbooks.Add()

[void] $objExcel.Windows.Arrange($xlArrangeStyleHorizontal)

So how does this script work? We’re glad you asked that question. The script starts off by assigning the value -4128 to a variable named $xlArrangeStyleHorizontal; we’ll use this variable to tell the script that we want to tile the workbook window horizontally. Don’t want to tile the windows horizontally? In that case, you can use any of the following values:

After assigning a value to the variable $xlArrangeStyleHorizontal we next use the New-Object cmdlet to create an instance of the Excel.Application object:

$objExcel = New-Object -com Excel.Application

By the way, don’t forget to include the –com parameter; that tells PowerShell that we want to create a COM object. If you leave this parameter out New-Object will try to instantiate an instance of a .NET Framework class named Excel.Application, which is going to be a bit of a problem seeing as how there isn’t a .NET Framework class named Excel.Application.

As soon as we have our new instance of Excel we set the Visible property to True ($True); that simply makes our instance of Excel visible onscreen. We then use the following lines of code to create three new workbooks, one named $objWorkbook1, one named $objWorkBook2, and the last one named $objWorkbook3:

$objWorkbook1 = $objExcel.Workbooks.Add()
$objWorkbook2 = $objExcel.Workbooks.Add()
$objWorkbook3 = $objExcel.Workbooks.Add()

Believe it or not, at this point we’re almost done. In fact, to tile our workbooks horizontally all we have to do is run the following line of code:

[void] $objExcel.Windows.Arrange($xlArrangeStyleHorizontal)

All we’re doing here is calling the Arrange method (a method that belongs to Excel’s Windows collection object). As you can see, we pass the Arrange method a single parameter: $xlArrangeStyleHorizontal, the variable which – as we noted earlier – tells the script that we want to arrange the windows horizontally. That’s all we have to do.

Oh, good question: why did we preface this command with [void]? Well, when we call the Arrange method the value True gets echoed back to the PowerShell console. That’s not a big deal but, then again, it’s easy enough to prevent that message from appearing onscreen. And how do we suppress a message like this? You got it: we just preface the command with [void].

By the way, our running instance of Excel is going to look like a lot like this, with the three workbooks tiled on top of each other:

Pretty cool, huh?

We should probably note that this works because we used a single instance of Excel to create all three of our workbooks. If you’d prefer to have each workbook open in its own window (independent of all the other workbooks) then you need to use a separate instance of Excel for each workbook. In other words, you need to use a script like this:

$objExcel = New-Object -com Excel.Application
$objExcel.Visible = $True
$objWorkbook1 = $objExcel.Workbooks.Add()

$objExcel2 = New-Object -com Excel.Application
$objExcel2.Visible = $True
$objWorkbook2 = $objExcel2.Workbooks.Add()

$objExcel3 = New-Object -com Excel.Application
$objExcel3.Visible = $True
$objWorkbook3 = $objExcel3.Workbooks.Add()

And don’t worry, VBScripters: we didn’t forget you. Here’s a VBScript version of our original script, the one that creates three workbooks and then arranges the three horizontally:

Const xlArrangeStyleHorizontal = -4128

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook1 = objExcel.Workbooks.Add()
Set objWorkbook2 = objExcel.Workbooks.Add()
Set objWorkbook3 = objExcel.Workbooks.Add()

objExcel.Windows.Arrange(xlArrangeStyleHorizontal)

That should do it, RE. So is the Scripting Guy who writes this column glad to be back to work after a three-day break? Let’s put it this way: he did spend the morning busily working on a script, but it was a script that reports back the number of days between the current date and July 4^th, the next official Microsoft day off:

dtmEndingDate = Date
intDays = DateDiff("d", dtmEndingDate, #7/4/2008#)
Wscript.Echo intDays

Wow, 38 days to go. Oh well, we’ll see you all tomorrow. And the day after that. And the day after that. And ….

ScriptingGuy1

Follow