{"id":67113,"date":"2006-06-14T18:51:00","date_gmt":"2006-06-14T18:51:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2006\/06\/14\/how-can-i-get-a-list-of-available-metadata-for-microsoft-office-documents\/"},"modified":"2006-06-14T18:51:00","modified_gmt":"2006-06-14T18:51:00","slug":"how-can-i-get-a-list-of-available-metadata-for-microsoft-office-documents","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-get-a-list-of-available-metadata-for-microsoft-office-documents\/","title":{"rendered":"How Can I Get a List of Available Metadata for Microsoft Office Documents?"},"content":{"rendered":"

\"Hey,<\/p>\n

Hey, Scripting Guy! How can I get a list of available metadata for Microsoft Office documents?<\/p>\n

— JR<\/p>\n

\"Spacer\"\"Hey,\"Script<\/a><\/p>\n

Hey, JR. If we understand your question correctly, you’d like to know if it’s true that July 21, 2006 will mark the 500th<\/sup>Hey, Scripting Guy!<\/i> column; in addition, you’d like to know whether we are planning to do anything to mark this auspicious occasion. Well, the answer to both questions is yes. <\/p>\n

And, yes, technically you can<\/i> send us an electronic postcard. But that doesn’t sound half as much fun as getting a real<\/i> postcard, if you know what we mean.<\/p>\n

Anyway, thanks for writing, JR. Hope we were able to help.<\/p>\n

Hmmm, now that you mention it, we guess your question did<\/i> say something about metadata, didn’t it? Wonder how we could have missed that<\/i>? But, what the heck: we have some time to kill while we wait for the postcards to start rolling in, so let’s see if we can show you how to get a list of the available metadata for a Microsoft Office document.<\/p>\n

To begin with, we should note that there is actually a separate COM object (Dsofile.exe) that you can download<\/strong><\/a> and use to get metadata from Microsoft Office documents. The advantage of using Dsofile is that it can retrieve metadata from any<\/i> Microsoft Office document. The disadvantage to Dsofile? You’ll obviously have to download and install it on every computer where you might need to run your metadata-retrieving script. Because a lot of people don’t like to download and install additional utilities, we won’t discuss Dsofile today; if you’re interested, take a look at the Tales From the Script<\/i> column Dsofile: The Untold Story<\/strong><\/a>.<\/p>\n

Instead, what we’re going to do today is show you how to get at metadata (also referred to as document properties<\/i>) using plain old Microsoft Office. The nice thing about this, of course, is that there’s nothing to download or install: if you’ve got Microsoft Office up and running then you’ve already got everything you need. The one disadvantage: you’ll have to modify your script slightly depending on the type of document you want to examine. But don’t worry, we’ll explain how to do that. Because you asked a fairly simple question – How can I get a list of available metadata for Microsoft Office documents? – let’s start off with an equally-simple script, one that retrieves metadata for an Excel spreadsheet (C:\\Scripts\\Test.xls):<\/p>\n

Set objExcel = CreateObject(\"Excel.Application\")\nobjExcel.Visible = True\nSet objWorkbook = objExcel.Workbooks.Open(\"C:\\Scripts\\Test.xls\")\n\nFor Each strProperty in objWorkbook.BuiltInDocumentProperties\n    Wscript.Echo strProperty.Name\nNext\n<\/pre>\n
Yes, it is<\/i> a very simple little script. We start out by creating an instance of the Excel.Application<\/strong> object. And you’re right: because we’re using Excel we’ve pretty much limited ourselves to retrieving information about Excel spreadsheets. What if we wanted to retrieve information about Word documents? In that case, we’d need to use the Word.Application<\/strong> object. OK, then how about PowerPoint presentations? That’s easy: we’d need to use the PowerPoint.Application<\/strong> object. Microsoft Publisher? That’s right: Publisher.Application<\/strong>. And so on.<\/p>\n
After creating the Excel.Application object we use this line of code to make Excel visible onscreen:<\/p>\n
objExcel.Visible = True\n<\/pre>\n
Needless to say, we don’t need to do this: the script works just fine even if you can’t<\/i> see Excel onscreen. We do this primarily to help you visualize what’s going on. If you want to run the script without ever seeing the spreadsheet then simply leave out this line of code. Of course, each time you run the script you’ll also end up with an invisible copy of Excel running in the background. To combat that, just make sure you call the Quit<\/strong> method to dismiss Excel when the script finishes. The “invisible” version of this script looks like this:<\/p>\n
Set objExcel = CreateObject(\"Excel.Application\")\nSet objWorkbook = objExcel.Workbooks.Open(\"C:\\Scripts\\Test.xls\")\n\nFor Each strProperty in objWorkbook.BuiltInDocumentProperties\n    Wscript.Echo strProperty.Name\nNext\n\nobjExcel.Quit\n<\/pre>\n
After getting Excel up and running we then use this line of code to open the spreadsheet C:\\Scripts\\Test.xls:<\/p>\n
Set objWorkbook = objExcel.Workbooks.Open(\"C:\\Scripts\\Test.xls\")\n<\/pre>\n
So far so good. Now, how do we get to the metadata? Well, it turns out that Microsoft Office documents store their metadata in a collection named BuiltInDocumentProperties<\/strong>. To get a list of available metadata for a Microsoft Excel spreadsheet all we have to do is set up a For Each loop to walk through this collection, echoing back the Name<\/strong> of each item (each piece of metadata) found in the collection:<\/p>\n
For Each strProperty in objWorkbook.BuiltInDocumentProperties\n    Wscript.Echo strProperty.Name\nNext\n<\/pre>\n
What does that give us? That gives us something very much like this:<\/p>\n
Title\nSubject\nAuthor\nKeywords\nComments\nTemplate\nLast author\nRevision number\nApplication name\nLast print date\nCreation date\nLast save time\nTotal editing time\nNumber of pages\nNumber of words\nNumber of characters\nSecurity\nCategory\nFormat\nManager\nCompany\nNumber of bytes\nNumber of lines\nNumber of paragraphs\nNumber of slides\nNumber of notes\nNumber of hidden Slides\nNumber of multimedia clips\nHyperlink base\nNumber of characters (with spaces)\n<\/pre>\n
There’s your available metadata.<\/p>\n
But wait: we aren’t done yet. After all, that script shows us all the metadata that could<\/i> be configured for Test.xls. But what if we’d like to find out which metadata has<\/i> been configured for Test.xls? Can we get at that information? Of course we can; this script returns metadata property names and<\/i> configured values:<\/p>\n
On Error Resume Next\n\nSet objExcel = CreateObject(\"Excel.Application\")\nobjExcel.Visible = True\nSet objWorkbook = objExcel.Workbooks.Open(\"C:\\Scripts\\Test.xls\")\n\nFor Each strProperty in objWorkbook.BuiltInDocumentProperties\n    Wscript.Echo strProperty.Name & \" - \" & strProperty.Value\nNext\n<\/pre>\n
The only difference between this script and our first script? This line right here, where we echo back not only the item Name but also the item Value<\/strong>:<\/p>\n
Wscript.Echo strProperty.Name & \" - \" & strProperty.Value\n<\/pre>\n
Oh: and we also added On Error Resume Next<\/strong> to the beginning of the script. That’s important: in some cases the script will crash upon encountering a property that has never been configured. To guard against that possibility, just use On Error Resume Next.<\/p>\n
The net result will be similar to this, depending on which properties have values assigned to them and which ones do not:<\/p>\n
Title - Metadata Test\nSubject - Excel Test Scripts\nAuthor - Ken Myer\nKeywords - testing, scripts\nComments - This is a sample spreadsheet used for testing purposes.\nTemplate -\nLast author - Ken Myer\nRevision number -\nApplication name - Microsoft Excel\nCreation date - 6\/13\/2006 8:40:17 PM\nLast save time - 6\/13\/2006 9:07:15 AM\nSecurity - 0\nCategory -\nFormat -\nManager -\nCompany - Microsoft Corporation\nHyperlink base -\n<\/pre>\n
Ah, we had a feeling you were going to ask that. Yes, you can<\/i> use a script to programmatically set the value of some of these properties. Some properties – such as the document Creation date<\/strong> or Number of characters<\/strong> – are read-only, for obvious reasons; however, other properties – such as Title<\/strong>, Subject<\/strong>, and Author<\/strong> – can be configured using a script similar to this:<\/p>\n
On Error Resume Next\n\nSet objExcel = CreateObject(\"Excel.Application\")\nobjExcel.Visible = True\nSet objWorkbook = objExcel.Workbooks.Open(\"C:\\Scripts\\Book1.xls\")\n\nFor Each strProperty in objWorkbook.BuiltInDocumentProperties\n    If strProperty.Name = \"Title\" Then\n        strProperty.Value = \"Test Title\"\n    End if\nNext\n<\/pre>\n
Again, this is fairly simple. After setting up our For Each loop we use this line of code to check each document property to see if the Name of the property is equal to Title<\/i>:<\/p>\n
If strProperty.Name = \"Title\" Then\n<\/pre>\n
If it is, then we use this line of code to set the Value of the property to Test Title<\/i>:<\/p>\n
strProperty.Value = \"Test Title\"\n<\/pre>\n
Give it a try and see what happens.<\/p>\n
Two quick notes. Yes, there might be a slightly faster way to get at the value of an individual property like Title. As usual, though, we went for the approach we thought was easier rather than the approach that might have been a tiny bit faster. Also, as you doubtless know, you can add your own, custom metadata to Microsoft Office documents. Can you get at those<\/i> properties using a script? You bet: just use the CustomDocumentProperties<\/strong><\/a> collection instead of the BuiltInDocumentProperties collection.<\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I get a list of available metadata for Microsoft Office documents? — JR Hey, JR. If we understand your question correctly, you’d like to know if it’s true that July 21, 2006 will mark the 500thHey, Scripting Guy! column; in addition, you’d like to know whether we are planning to […]<\/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":[711,48,49,3,5],"class_list":["post-67113","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-application","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I get a list of available metadata for Microsoft Office documents? — JR Hey, JR. If we understand your question correctly, you’d like to know if it’s true that July 21, 2006 will mark the 500thHey, Scripting Guy! column; in addition, you’d like to know whether we are planning to […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/67113","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=67113"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/67113\/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=67113"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=67113"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=67113"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}