May 28th, 2008

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

Hey, Scripting Guy! Question

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

SpacerHey, Scripting Guy! AnswerScript Center

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.

Note. Well, OK, there was that one time that he came into work on January 2nd, which was technically a holiday – and a day off – because January 1st fell on a Sunday. But that wasn’t because he was eager to come into work; that was because he’s an idiot. (Especially when you consider the fact that he was here for 2 or 3 hours before it suddenly dawned him that the rest of the campus was completely deserted.)

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 30th; 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:

Constant

Value

xlArrangeStyleCascade

7

xlArrangeStyleHorizontal

-4128

xlArrangeStyleTiled

1

xlArrangeStyleVertical

-4166

Note. Why in the world did the Excel team pick the values 1, 7, -4128, and -4166? To tell you the truth, we don’t know; we learned a long time ago not to ask questions like that.

After all, the answer usually just gives us a headache.

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

Note. Why in the world did the Scripting Guys pick the names $objWorkbook1, $objWorkbook2, and $objWorkbook3? That one we can answer: because we were too lazy to try and come up with names any more clever than that.

Besides, the names Larry, Moe, and Curly were already taken.

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:

Microsoft Excel

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 4th, 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 ….

Author

0 comments

Discussion are closed.