{"id":55023,"date":"2008-10-09T11:09:00","date_gmt":"2008-10-09T11:09:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2008\/10\/09\/hey-scripting-guy-how-can-i-read-microsoft-excel-metadata\/"},"modified":"2008-10-09T11:09:00","modified_gmt":"2008-10-09T11:09:00","slug":"hey-scripting-guy-how-can-i-read-microsoft-excel-metadata","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-read-microsoft-excel-metadata\/","title":{"rendered":"Hey, Scripting Guy! How Can I Read Microsoft Excel Metadata?"},"content":{"rendered":"

\"Hey, <\/p>\n

Hey, Scripting Guy! I have a VBScript script that reads Microsoft Office Excel metadata. I would like to update the script to Windows PowerShell, but can I do this? I tried using New-Object<\/b> to create the Excel.Application<\/b> object but somehow it does not seem to work. What gives? I thought Windows PowerShell could do everything VBScript can do and more. Am I wrong?<\/p>\n

– SV<\/p>\n

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

Hi SV,<\/p>\n

Wow! How many questions can you ask in one question? I am impressed! Let\u2019s see if I can answer them all: yes, a lot, sort of, not really. I think that\u2019s right. Let\u2019s begin at the beginning. You want to update a previously working VBScript script to Windows PowerShell. I appreciate your enthusiasm, but if you have a perfectly fine VBScript script, there is really no need to “translate” it to Windows PowerShell. In fact, of the more than 3,000 VBScript scripts I have written, I have actually translated fewer than 100 of them to Windows PowerShell. Generally, I am so excited about the new capabilities of Windows PowerShell that I have not had time to do the translation work. This having been said, I do know of a few companies that have passed down the edict “no more VBScript,” and they actually assigned several of the network administrators to translate the existing VBScript scripts to Windows PowerShell. If you are interested in this process, let me know via scripter@microsoft.com (in English, if possible)<\/a>, and I will be glad to go into it in more detail. <\/p>\n

Anyway, SV, I looked through the archives and found this script, which you may have been referring to:<\/p>\n

On Error Resume Next\nSet objExcel = CreateObject(\"Excel.Application\")\nobjExcel.Visible = True\nSet objWorkbook = objExcel.Workbooks.Open(\"C:\\Scripts\\Test.xls\")\nFor Each strProperty in objWorkbook.BuiltInDocumentProperties\n    Wscript.Echo strProperty.Name & \" - \" & strProperty.Value\nNext<\/pre>\n
This script took Greg probably about an hour (or less) to write. The GetExcelMetaData.ps1<\/b> script that follows this paragraph took me two days<\/i> to write, and in the end, I had to get help from my good friend and fellow Microsoftie Luis in Lisbon to solve a very perplexing problem with COM interop assemblies. So I am very glad you wrote and asked this question because I believe it will be instructive for us all. If you want to skip the details, here is the GetExcelMetaData.ps1<\/b> script. If you are a real geek (or geek at heart or geek wannabe), read on:<\/p>\n
$excel = New-Object -ComObject Excel.Application\n$excel.Visible = $false\n$workbook = $excel.Workbooks.Open(\"C:\\test\\excel.xls\")\n$binding = \"System.Reflection.BindingFlags\" -as [type]\nForeach($property in $workbook.BuiltInDocumentProperties)\n{\n  $pn = [System.__ComObject].invokemember(\"name\",$binding::GetProperty,$null,$property,$null)\n  trap [system.exception]\n   {\n     write-host -foreground blue \"Value not found for $pn\"\n    continue\n   }\n  \"$pn`: \" +\n   [System.__ComObject].invokemember(\"value\",$binding::GetProperty,$null,$property,$null)\n }\n$excel.quit()<\/pre>\n
So what is all this stuff about Excel metadata anyway? It sounds like something from some space epic: <\/p>\n
“Oh, my! The rebels are attacking the metadata!” <\/p>\n
“Launch the PowerShell!” <\/p>\n
(Or a scenario to that effect.) <\/p>\n
Anyway, in the image that follows this paragraph, you see some of the metadata associated with an Excel spreadsheet. There are actually two ways in Windows Vista to view metadata. When a file is selected in Explorer, some of the more important metadata properties are displayed in summary fashion along the bottom of the screen. A more complete listing is found when you right-click the file, choose properties, and then click the Details<\/b> tab. As you can see, some of this information can be extremely useful. I love adding the comments field and the tags property as they can be used with the search feature in Windows Vista to allow me to easily find particular documents among the billions of files that hide on my 200 GB laptop drive: <\/p>\n
\"Excel <\/p>\n
 <\/p>\n
Okay, SV, back to your question: Can I do this? Sure you can. The first thing we do in the GetExcelMetaData.ps1<\/b> script is the exact same thing you said you did: We create an instance of the Excel.Application<\/b> object. In VBScript you use the CreateObject<\/b> command, but in Windows PowerShell, we use the New-Object<\/b> cmdlet as seen here: <\/p>\n
$excel = New-Object -ComObject Excel.Application<\/pre>\n
So far so good. In the VBScript, Greg made the instance of Excel visible. That is cool, but is not needed for this particular script. After all we only want the metadata from the spreadsheet, not the actual data in the spreadsheet. To keep the spreadsheet invisible, we set the visible property of the Excel.Application<\/b> object to false. This is seen here: <\/p>\n
$excel.Visible = $false<\/pre>\n
(Okay, one more easy line of code, and then things are going to get hairy\u2014bail out now if you wish.) The next thing we need to do is to open the Excel spreadsheet. To do this, we use the open<\/b> method from the workbook<\/b> object. The open<\/b> method needs the path to the Microsoft Excel workbook on which you are planning on working. Here is the line of code that opens the workbook: <\/p>\n
$workbook = $excel.Workbooks.Open(\"C:\\test\\excel.xls\")<\/pre>\n
Now for the hard part (actually it is not too<\/i> bad). The next thing we do is turn a string into a data type. The reason for doing this is twofold. First, I want to show you how to use this technique. Second, doing this will actually make the code a bit easier to read. What we are really doing here is creating an alias for a data type. The data type in question is the System.Reflection.BindingFlags<\/b> class. If we just assigned the string “System.Reflection.BindingFlags” to the variable $binding<\/b>, we would simply have a string and not an instance of the System.Reflection.BindingFlags<\/b> class. The BindingFlags<\/b> are used to control the binding of many classes in the System.Reflection<\/b> and other Microsoft .NET Framework namespaces. We need to use this, so we can gain access to the InvokeMember<\/b> method. You will see this later. To turn a string into a data type, use the -as<\/b> operator and specify that it is -as [type]<\/b>. This is seen here:<\/p>\n
$binding = \"System.Reflection.BindingFlags\" -as [type]<\/pre>\n
Now we need to use the ForEach<\/b> statement to walk through the collection of BuiltInDocumentProperties<\/b> from the Excel Workbook<\/b> object. We use the $property<\/b> variable as our enumerator while walking through the collection. This is seen here: <\/p>\n
Foreach($property in $workbook.BuiltInDocumentProperties)<\/pre>\n
Now for the two-day problem! I should be able to simply use the following code to obtain the name of each BuiltInDocumentProperties<\/b> from the collection:<\/p>\n
$property.name<\/pre>\n
The problem is the way the interop assembly for this particular COM object works. The object comes back and says it is a [System.__ComObject]<\/b>. When you try to use $property.name<\/b>, you get an error that says, “[System.__ComObject]<\/b> does not have a property named name<\/b>.\u201d Here is where we need to use reflections: <\/p>\n
$pn = [System.__ComObject].invokemember(\"name\",$binding::GetProperty,$null,$property,$null)<\/pre>\n
The InvokeMember<\/b> can be used in three different ways (called overloaded). Here is the breakdown of the method call:<\/p>\n\n\n\n\n\n\n\n\n\n
Value<\/td>\nMeaning<\/td>\n<\/tr>\n<\/thead>\n
\n
name<\/p>\n<\/td>\n
\n
The name of the property to query.<\/p>\n<\/td>\n<\/tr>\n
\n
$binding::GetProperty<\/p>\n<\/td>\n
\n
The GetProperty<\/b> static method from the System.Reflection.BindingFlags<\/b> class we created earlier and stored in the $binding<\/b> variable.<\/p>\n<\/td>\n<\/tr>\n
\n
$null<\/p>\n<\/td>\n
\n
The binder object that specifies a set of properties and enables binding. We use $null<\/b> here to use the defaultBinder<\/b>.<\/p>\n<\/td>\n<\/tr>\n
\n
$property<\/p>\n<\/td>\n
\n
The target object. Here is an instance of a document property from the BuiltInDocumentProperties<\/b> collection.<\/p>\n<\/td>\n<\/tr>\n
\n
$null<\/p>\n<\/td>\n
\n
An array of objects to pass as arguments to the method. This is not needed here because we are querying a property.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
If you query for an instance of a document property that does not exist, the object throws an error. This is true even if you are using VBScript. Because we went to all this trouble to query the document properties, we decided to go one step farther and show you how to trap errors with Windows PowerShell. In VBScript the original script used On Error Resume Next and move to the next property in the collection when an error occurred. We could have done exactly the same thing by using $ErrorActionPreference = “SilentlyContinue”<\/b>. We decided it would be cool to use the Trap<\/b> statement instead because this is the way errors are handled in C#. When using the Trap<\/b> statement, you need to specify what kind of error to catch. Here we are catching a system.exception<\/b>, which is pretty generic. When the exception is detected, we print out the name of the document property that does not exist in blue (much more friendly than red), and then we continue to the next property. This is seen here:<\/p>\n
trap [system.exception]\n   {\n     write-host -foreground blue \"Value not found for $pn\"\n    continue\n   }<\/pre>\n
If the property does exist, we want to print out the name of the property and the value. This is seen here. Note we repeat the same command line that uses invokemember<\/b> as seen earlier. This time, however, we also print out the value of the property and the name of the property, which is contained in the $pn<\/b> variablevariable: <\/p>\n
\"$pn`: \" +\n   [System.__ComObject].invokemember(\"value\",$binding::GetProperty,$null,$property,$null)<\/pre>\n
When we have the list of document properties and their values, we simply need to exit Excel (if you do not do this, it will continue to hang around and eat up memory). This line is about the same as in the VBScript script:<\/p>\n
$excel.quit()<\/pre>\n
When we run the script, we are greeted with the following output: <\/p>\n
\"Excel <\/p>\n
 <\/p>\n
So, SV, as you can see, not everything is necessarily easier to do in Windows PowerShell than in VBScript. As I said at the outset, if you have a script that works, leave it alone unless there is some compelling reason to revise it (such as official edicts). This also illustrates why I prefer to use .NET Framework classes when working with Windows PowerShell\u2014in general, they will work easier and perform better.<\/p>\n
Ed Wilson and Craig Liebendorfer, Scripting Guys<\/b><\/b><\/span><\/font><\/p>\n
<\/b><\/font><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! I have a VBScript script that reads Microsoft Office Excel metadata. I would like to update the script to Windows PowerShell, but can I do this? I tried using New-Object to create the Excel.Application object but somehow it does not seem to work. What gives? I thought Windows PowerShell could do everything […]<\/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,45],"class_list":["post-55023","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-windows-powershell"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! I have a VBScript script that reads Microsoft Office Excel metadata. I would like to update the script to Windows PowerShell, but can I do this? I tried using New-Object to create the Excel.Application object but somehow it does not seem to work. What gives? I thought Windows PowerShell could do everything […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55023","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=55023"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55023\/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=55023"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=55023"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=55023"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}