Hey, Scripting Guy! How Can I Read Custom Summary Information Properties for an Office Excel File?

ScriptingGuy1

Hey, Scripting Guy! We’ve added a number of custom properties to the summary information pages of our Office Excel files. How can I access those custom properties using a script?

— UR

Hey, UR. You know, the Scripting Guy who writes this column almost didn’t write this column today. That’s because he’s seriously thinking about making a career change: he’s all-but decided to quit his job and become a full-time competitor in the Winter Scripting Games. (By the way, the 2008 edition of the Games begins February 15^th.)

Now, we know what you’re thinking, and that’s OK; needless to say, that’s not the first time he’s heard someone tell him that he’s crazy. (In fact, between the Scripting Son and the Scripting Editor, he hears that several times a day, both at home and at work.) Nevertheless, the Scripting Guy who writes this column remains undeterred: he wants to compete in the Scripting Games full-time, partly because that sounds like way more fun than working, but mainly because – as a Microsoft employee – he’s ineligible to win any of Scripting Games prizes.

Is that a problem? You bet it is. In addition to all the great prizes we originally announced, yesterday we revealed that ActiveState has upped the ante by tossing in a pair of Perl Developer Kits. And now, just one day later, the Windows PowerShell team is getting into the act, something that occurred after the Scripting Guy who writes this column received an email from PowerShell architect Jeffrey Snover.

Anyway, Jeffrey wanted to know if people were allowed to use Windows PowerShell 2.0 in the Scripting Games. And, to be honest, our first thought was this: no. That’s not because we have anything against PowerShell 2.0; we don’t. However, we were leaning in that direction because PowerShell 2.0 is still in beta; that’s why we were going to make PowerShell 1.0 the official platform for the Scripting Games. As many of you know, however, Jeffrey Snover is a very persuasive guy; right after we finished mowing his lawn and picking up his dry cleaning, we agreed to allow people to use PowerShell 1.0 or PowerShell 2.0.

But that’s not all. Thanks to the generosity of the PowerShell team, not only can you use PowerShell 2.0, but we will (subject to availability) send one of the coveted Windows PowerShell T-shirts to anyone who uses a 2.0-specific feature in one of their solutions. (Hint: Some of the new array capabilities might be a good candidate for this. And tell you what: if you want to display data in a grid rather than in the console window, well, we’ll allow that, too.) Regardless, just use one of the cool new features found in PowerShell 2.0 in your (correctly working) entry to any of the PowerShell events and you’ll get a PowerShell T-shirt. It’s that simple.

By the way, some of you might be thinking, “Well, that’s nice. But I’ll just get a PowerShell T-shirt some other time.” Well, maybe, but we wouldn’t count on that: after all, there are only a handful of T-shirts left, and the Scripting Guys have them all.

That’s right: we’ve cornered the market on PowerShell T-shirts. It’s not quite the same as cornering the market on gold or silver, but it was the best we could do.

Anyway, if you’re looking for another reason to enter the Scripting Games, well, there you go.

Speaking of the Scripting Games, some of you are probably a little skeptical; you’re not convinced that the Scripting Guy who writes this column has what it takes to compete full-time on the Winter Scripting Games circuit. Well, we’ll just see about that. For example, what do you suppose the Scripting Guy who writes this column would do if one of the Scripting Games events required him to read a custom property added to the summary information sheet for a Microsoft Excel file? Most likely he’d do this:

Set objExcel = CreateObject(“Excel.Application”)Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)For Each strProperty in objWorkbook.CustomDocumentProperties    Wscript.Echo strProperty.Name & ” – ” & strProperty.ValueNextobjExcel.Quit

As you can see, this is actually a pretty simple script. (One of the keys to successfully competing in the Scripting Games: keep your scripts as short and sweet as possible. Remember, you have to write at least 10 scripts over the course of the Games.)

In this script, we start out by creating an instance of the Excel.Application object; once we have that object we then use the Open method to open the file C:\Scripts\Test.xls:

Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)

Once the spreadsheet is open we set up a For Each loop to walk us through all the custom properties that have been added to the summary information page. Ah, good question: what is the summary information page? That’s the page you see when you right-click a .XLS file and select Properties:

In today’s column we’re working with the properties found only on the Custom page. If you want to work with the standard (built-in) properties, the ones shown on the Summary page, well, take a peek at this Hey, Scripting Guy! column for a few pointers.

At any rate, we set up a For Each loop that loops through all the values in the CustomDocumentProperties collection. For each custom property attached to the document we use this line of code to echo back the property Name and Value:

Wscript.Echo strProperty.Name & ” – ” & strProperty.Value

After that all we have to do is call the Quit method to terminate both our instance of Excel and the script.

What do you think? Does that have “Winter Scripting Games Champion” written all over it or what?

You’re right; it could be a little better, couldn’t it? After all, the preceding script retrieves the values of all the custom properties added to a .XLS file. (Note: This will also work on a .XLSX – Excel 2007 – file.) What if you want the value for only one particular property? Well, if you know the name of the property you can use the following script to retrieve just the value of that one property. This script echoes back the value of the custom property named TestProperty (and only the value of the custom property named TestProperty):

Set objExcel = CreateObject(“Excel.Application”)Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)Wscript.Echo objWorkbook.CustomDocumentProperties(“TestProperty”)objExcel.Quit

As you can see, in this script we don’t use a For Each loop to loop through all the custom properties attached to the file. Instead, we use the following line of code to echo back the value of the TestProperty property:

Wscript.Echo objWorkbook.CustomDocumentProperties(“TestProperty”)

Cool, huh? Oh, and by accessing an individual property you can also use a script to modify the value of that property. This script changes the value of the TestProperty property:

Set objExcel = CreateObject(“Excel.Application”)Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)objWorkbook.CustomDocumentProperties(“TestProperty”).Value = “My updated value.”objWorkbook.SaveobjExcel.Quit

Notice that we do two things here. First, we assign a new value to the property’s Value property (try saying that three times fast!):

objWorkbook.CustomDocumentProperties(“TestProperty”).Value = “My updated value.”

And then, because we did make a change to the file, we need to call the Save method to save the spreadsheet before closing it:

objWorkbook.Save

That should do it, UR. As for the Scripting Guy who writes this column, he needs to resume his workouts. After all, if he’s going to make his living as a full-time competitor in the Winter Scripting Games, well, he’s going to have to put in a lot of hard work.

And, sure, he’s also going to have to convince Microsoft to add a whole bunch of prize money to the Games. But that shouldn’t be a problem. After all, we keep adding new prizes to the Scripting Games on a daily basis. It’s probably just a matter of time before we get that prize money.

ScriptingGuy1

Follow