{"id":56253,"date":"2008-02-07T22:44:00","date_gmt":"2008-02-07T22:44:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2008\/02\/07\/hey-scripting-guy-how-can-i-read-custom-summary-information-properties-for-an-office-excel-file\/"},"modified":"2008-02-07T22:44:00","modified_gmt":"2008-02-07T22:44:00","slug":"hey-scripting-guy-how-can-i-read-custom-summary-information-properties-for-an-office-excel-file","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-read-custom-summary-information-properties-for-an-office-excel-file\/","title":{"rendered":"Hey, Scripting Guy! How Can I Read Custom Summary Information Properties for an Office Excel File?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! We\u2019ve 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<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, UR. You know, the Scripting Guy who writes this column almost didn\u2019t write this column today. That\u2019s because he\u2019s seriously thinking about making a career change: he\u2019s all-but decided to quit his job and become a full-time competitor in the Winter Scripting Games<\/B><\/A>. (By the way, the 2008 edition of the Games begins February 15th<\/SUP>.)<\/P>\n

Now, we know what you\u2019re thinking, and that\u2019s OK; needless to say, that\u2019s not the first time he\u2019s heard someone tell him that he\u2019s 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<\/I> more fun than working, but mainly because \u2013 as a Microsoft employee \u2013 he\u2019s ineligible to win any of Scripting Games prizes.<\/P>\n

Is that a problem? You bet it is. In addition to all the great prizes<\/B><\/A> we originally announced, yesterday<\/B><\/A> 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.<\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. Do the Scripting Guys really know famous people like Windows PowerShell architect Jeffrey Snover? Of course we do; in fact, Jeffrey Snover and the Scripting Guys are close personal friends. How close? Let\u2019s put it this way: sometimes Jeffrey lets us wash his car or whitewash his fence. What a great guy!<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

Anyway, Jeffrey wanted to know if people were allowed to use Windows PowerShell 2.0<\/B><\/A> in the Scripting Games. And, to be honest, our first thought was this: no. That\u2019s not because we have anything against PowerShell 2.0; we don\u2019t. However, we were leaning in that direction because PowerShell 2.0 is still in beta; that\u2019s 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<\/I> PowerShell 2.0.<\/P>\n

But that\u2019s 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\u2019ll 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\u2019ll get a PowerShell T-shirt. It\u2019s that simple.<\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. OK, so it\u2019s probably not that<\/I> simple; there will have to be a few rules and restrictions (like one shirt per competitor). We\u2019ll post the complete details sometime before the Games begin.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

By the way, some of you might be thinking, \u201cWell, that\u2019s nice. But I\u2019ll just get a PowerShell T-shirt some other time.\u201d Well, maybe<\/I>, but we wouldn\u2019t count on that: after all, there are only a handful of T-shirts left, and the Scripting Guys have them all. <\/P>\n

That\u2019s right: we\u2019ve cornered the market on PowerShell T-shirts. It\u2019s not quite the same as cornering the market on gold or silver, but it was the best we could do.<\/P>\n

Anyway, if you\u2019re looking for another reason to enter the Scripting Games, well, there you go.<\/P>\n

Speaking of the Scripting Games, some of you are probably a little skeptical; you\u2019re 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\u2019ll 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\u2019d do this<\/I>:<\/P>

Set objExcel = CreateObject(“Excel.Application”)Set objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)For Each strProperty in objWorkbook.CustomDocumentProperties    Wscript.Echo strProperty.Name & ” \u2013 ” & strProperty.ValueNextobjExcel.Quit<\/PRE>\n
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.) <\/P>\n
In this script, we start out by creating an instance of the Excel.Application<\/B> object; once we have that object we then use the Open<\/B> method to open the file C:\\Scripts\\Test.xls:<\/P>
Set objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)<\/PRE>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. You might have noticed that we didn\u2019t set the Visible<\/B> property to True. That\u2019s because we didn\u2019t see any reason to make Excel visible; when we access the summary information property there really isn\u2019t anything to see onscreen anyway. But if you\u2019d like to watch the magic unfold then just make this the second line in your script: objExcel.Visible = True<\/B>.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
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<\/I> the summary information page? That\u2019s the page you see when you right-click a .XLS file and select Properties<\/B>:<\/P>\"Microsoft \n

In today\u2019s column we\u2019re working with the properties found only on the Custom<\/B> page. If you want to work with the standard (built-in) properties, the ones shown on the Summary<\/B> page, well, take a peek at this Hey, Scripting Guy!<\/I><\/B><\/A> column for a few pointers.<\/P>\n
At any rate, we set up a For Each loop that loops through all the values in the CustomDocumentProperties<\/B> collection. For each custom property attached to the document we use this line of code to echo back the property Name<\/B> and Value<\/B>:<\/P>
Wscript.Echo strProperty.Name & ” \u2013 ” & strProperty.Value<\/PRE>\n
After that all we have to do is call the Quit<\/B> method to terminate both our instance of Excel and the script.<\/P>\n
What do you think? Does that have \u201cWinter Scripting Games Champion\u201d written all over it or what?<\/P>\n
You\u2019re right; it could<\/I> be a little better, couldn\u2019t 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 \u2013 Excel 2007 \u2013 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):<\/P>
Set objExcel = CreateObject(“Excel.Application”)Set objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)Wscript.Echo objWorkbook.CustomDocumentProperties(“TestProperty”)objExcel.Quit<\/PRE>\n
As you can see, in this script we don\u2019t 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:<\/P>
Wscript.Echo objWorkbook.CustomDocumentProperties(“TestProperty”)<\/PRE>\n
Cool, huh? Oh, and by accessing an individual property you can also use a script to modify<\/I> the value of that property. This script changes the value of the TestProperty property:<\/P>
Set objExcel = CreateObject(“Excel.Application”)Set objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)objWorkbook.CustomDocumentProperties(“TestProperty”).Value = “My updated value.”objWorkbook.SaveobjExcel.Quit<\/PRE>\n
Notice that we do two things here. First, we assign a new value to the property\u2019s Value<\/B> property (try saying that<\/I> three times fast!):<\/P>
objWorkbook.CustomDocumentProperties(“TestProperty”).Value = “My updated value.”<\/PRE>\n
And then, because we did<\/I> make a change to the file, we need to call the Save<\/B> method to save the spreadsheet before closing it:<\/P>
objWorkbook.Save<\/PRE>\n
That should do it, UR. As for the Scripting Guy who writes this column, he needs to resume his workouts. After all, if he\u2019s going to make his living as a full-time competitor in the Winter Scripting Games, well, he\u2019s going to have to put in a lot of hard work.<\/P>\n
And, sure, he\u2019s also going to have to convince Microsoft to add a whole bunch of prize money to the Games. But that shouldn\u2019t be a problem. After all, we keep adding new prizes to the Scripting Games on a daily basis. It\u2019s probably just a matter of time before we get that prize money.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. Actually, the talk here in Redmond is that Microsoft made its recent offer for Yahoo! for one reason and one reason only: because that would make one heck<\/I> of a grand prize for the 2009 Scripting Games. Are we really planning to give away Yahoo! in next year\u2019s Games? Well, it\u2019s a little too early to make any promises. But we\u2019ll see.<\/P>\n
And yes, we\u2019d rather have a Dr. Scripto bobblehead<\/B><\/A>, too. But Yahoo! would<\/I> make a nice consolation prize.<\/P><\/TD><\/TR><\/TBODY><\/TABLE><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! We\u2019ve 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\u2019t write this column today. That\u2019s because he\u2019s seriously thinking about making […]<\/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":[48,49,3,5],"class_list":["post-56253","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! We\u2019ve 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\u2019t write this column today. That\u2019s because he\u2019s seriously thinking about making […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/56253","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=56253"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/56253\/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=56253"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=56253"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=56253"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}