{"id":55793,"date":"2008-04-12T01:05:00","date_gmt":"2008-04-12T01:05:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2008\/04\/12\/hey-scripting-guy-how-can-i-use-windows-powershell-to-look-at-all-the-microsoft-office-documents-in-a-folder\/"},"modified":"2008-04-12T01:05:00","modified_gmt":"2008-04-12T01:05:00","slug":"hey-scripting-guy-how-can-i-use-windows-powershell-to-look-at-all-the-microsoft-office-documents-in-a-folder","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-use-windows-powershell-to-look-at-all-the-microsoft-office-documents-in-a-folder\/","title":{"rendered":"Hey, Scripting Guy! How Can I Use Windows PowerShell to Look at All the Microsoft Office Documents in a Folder?"},"content":{"rendered":"

\"Hey, <\/p>\n

Hey, Scripting Guy! I am trying to use Windows PowerShell to look at files on my computer; what I want to do is get back a history (that is, the last time a document was accessed) for all the Microsoft Office files in a folder and its subfolders. However, I\u2019m not having much luck. Any ideas would be appreciated.
— KM<\/p>\n

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

Hey, KM. We don\u2019t know if you participated in the 2008 Winter Scripting Games<\/b><\/a> \u2013 well, OK, actually that\u2019s not true: we know very well whether or not you participated in the 2008 Winter Scripting Games. In fact, we know pretty much everything there is to know about you: your shoe size; your favorite color; what you had for lunch yesterday; whether or not you\u2019ve ever used any non-Microsoft software. However, we\u2019re not supposed to let anyone know that we track that information, so kind of keep that to yourself, OK?<\/p>\n\n<\/thead>\n\n\n
\n

Note<\/b>. Actually, we don\u2019t keep track of information like that; in fact, we don\u2019t keep track of any<\/i> information. (Which is why it always takes us so long to find the car when we head for home each evening.) We always get a chuckle out of people who claim that Microsoft is compiling \u2013 and analyzing \u2013 reams of data about its customers. The truth is, we at Microsoft have enough trouble keeping track of our own<\/i> information (anyone seen any archived Scripting Guys webcasts lately?), let alone anyone else\u2019s.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/div>\n

At any rate, if you participated in the 2008 Winter Scripting Games \u2013 and we\u2019re not saying that you did, only saying if<\/i> you did, well, then we have some good news for you: we\u2019re finally getting all the prizes sent out. On top of that, after several hours of non-stop writing we even finished signing and mailing out all the Certificates of Excellence. <\/p>\n\n<\/thead>\n\n\n
\n

Note<\/b>. Ever wonder why Scripting Guy Peter Costantini decided to become a Microsoft PM? It had nothing to do with advancing his career; it\u2019s because he couldn\u2019t stand the thought of signing Joseph <\/i>Peter Costantini<\/i> 700+ times. Thank goodness Jean and Greg have very short names.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/div>\n

In fact, with the exception of a few for a handful of people who sent their address in late, all<\/i> the certificates have been signed and mailed; we\u2019ll get the remaining certificates sent out in the next day or two. In addition to that, nearly all of the non-bobblehead prizes have been sent out as well; depending on where you live (that is, in the US or not in the US) you should receive your prize within the next week or so.<\/p>\n

As for the bobbleheads, well, we\u2019ll start shipping those out very soon: we\u2019re just waiting on the boxes we need to pack the things. As soon as those boxes arrive we\u2019ll start sending out bobbleheads.<\/p>\n

And no, there\u2019s no need to drop us a line when you actually receive your prize. We\u2019ll know when you receive your prize.<\/p>\n

Well, not that we\u2019ll actually know<\/i> mind you, because we don\u2019t keep track of information like that. But we will hire a psychic<\/b><\/a> to keep us informed of when the prizes get delivered; that will save you the trouble of notifying us.<\/p>\n\n<\/thead>\n\n\n
\n

Note<\/b>. The Psychics for Hire Web site has an online test you can use to test your extra-sensory perception. The Scripting Guy who writes this column tried the test three different times; each time he got 0%. According to the Web site \u201cYou may be trying too hard, or your \u2018rational\u2019 mind may be blocking your intuitive mind.\u201d Which would make sense if he actually had<\/i> a rational mind.<\/p>\n

Incidentally, the Psychics for Hire Web site also uses cookies to keep track of whether you\u2019ve visited the site or not. You\u2019d think that someone there would just know<\/i> whether or not you\u2019ve visited the site. <\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/div>\n

Of course, all of that is good news only for those people who took part in the Scripting Games. Do we have any good news for people who didn\u2019t<\/i> take part in the Scripting Games? Sadly, no; we don\u2019t. The truth is, if you didn\u2019t take part in the Scripting Games you\u2019ll probably never<\/i> get any good news ever again. By not participating in the Scripting Games you\u2019ve pretty much thrown your life away.<\/p>\n

Oh, wait; never mind. Here\u2019s some good news after all: a Windows PowerShell script that reports back the LastAccessTime<\/b> property for all the Microsoft Office documents in a folder and its subfolders:<\/p>\n

Get-ChildItem C:\\Scripts -recurse -include *.doc, *.xls, *.ppt, *.mdb | \nSelect-Object FullName, LastAccessTime\n<\/pre>\n
Before we go any further we should note that we cheated a little bit here. There might<\/i> be a really easy way to determine whether a file is a Microsoft Office document or not; however, we weren\u2019t sure what that way might be. (We came up with a couple of ideas, but all of them were a bit goofy. And, per doctor\u2019s orders, we\u2019re trying to cut down on goofiness.) Therefore, we cheated. Instead of using the registry or some mythical .NET Framework property to try and identify a Microsoft Office document we did it the old-fashioned way: we looked at the file extension of each file. <\/p>\n
Now, admittedly, that might sound a bit scary; no doubt you have visions of having to write some monstrous WMI query along the order of this:<\/p>\n
Set colFiles = objWMIService._\n    (\"SELECT * FROM CIM_DataFile WHERE Path = '\\\\Scripts\\\\' \" & _\n        \"AND Drive = 'C:' AND (Extension = 'doc'\" OR Extension = 'xls' OR Extension = 'ppt' OR Extension = 'mdb'\"))\n<\/pre>\n
Etc., etc. <\/p>\n
Fortunately, though, Windows PowerShell \u2013 and the Get-ChildItem<\/b> cmdlet \u2013 helps you avoid that sort of craziness; as it is, it\u2019s much easier to specify multiple file extensions using Get-ChildItem\u2019s \u2013include<\/b> parameter than it is to write some gargantuan Where clause. In fact, if you look closely at our call to Get-ChildItem you\u2019ll see how simple it really is:<\/p>\n
Get-ChildItem C:\\Scripts -recurse -include *.doc, *.xls, *.ppt, *.mdb\n<\/pre>\n
All we\u2019re doing here us asking Get-ChildItem to return all the items found in the C:\\Scripts folder. Because we\u2019re also interested in the items found in any subfolders of C:\\Scripts (and any subfolders of those subfolders) we also tack on the \u2013recurse<\/b> parameter; that causes Get-ChildItem to search the entire directory tree, and without us having to write a complicated recursive function<\/b><\/a>.<\/p>\n
Oh, and because we care about only certain files (or, more correctly, certain file extensions), we also tossed in the \u2013include parameter, followed by the file extensions of interest:<\/p>\n
-include *.doc, *.xls, *.ppt, *.mdb\n<\/pre>\n\n<\/thead>\n\n\n
\n
Note<\/b>. Needless to say, we aren\u2019t limited to just those extensions, or to just four file extensions. Add as many file extensions as you want, just make sure to follow the same format (*.file_extension<\/i>), and make sure you separate the extensions using a comma.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
You might have noticed that we don\u2019t use only the Get-ChildItem cmdlet; we also use the Select-Object<\/b> cmdlet. Why do we need two<\/i> cmdlets to carry out a single task? Well, by default, Get-ChildItem returns data in the following format:<\/p>\n
Mode                LastWriteTime     Length Name\n----                -------------     ------ ----\n-a---        12\/17\/2007   9:33 PM     905216 challenge.mdb\n-a---          1\/3\/2007   8:00 AM     266240 scores.mdb\n-a---          1\/3\/2007   8:00 AM      92160 Scores.xls\n-a---         1\/28\/2008  10:50 AM     274432 test.mdb\n-a---          1\/3\/2007   8:00 AM      94720 test.ppt\n-a---          2\/5\/2008  10:27 PM      14848 test.xls\n-a---          1\/8\/2008  12:55 PM      21504 Test1.doc\n-a---         1\/18\/2008   9:24 AM      13824 test2.xls\n<\/pre>\n
That\u2019s nice, but it doesn\u2019t include the property we\u2019re really interested in: LastAccessTime. That\u2019s we don\u2019t immediately display the information returned by Get-ChildItem; if we do, we won\u2019t actually see the value of the LastAccessTime property. Consequently, we instead pipe the data retrieved by Get-ChildItem to the Select-Object cmdlet, asking Select-Object to grab just the FullName<\/b> and LastAccessTime properties:<\/p>\n
Select-Object FullName, LastAccessTime\n<\/pre>\n
Yes, we know: this is one of the more confusing things about Windows PowerShell. For better or worse (usually better, but sometimes worse), PowerShell has a number of default formatters built into it, formatters that decide \u2013 in advance \u2013 which property values should be returned, and how those property values should be displayed. Is that a problem? Well, it can be. After all, if you just call the Get-ChildItem cmdlet you might not know that files even have<\/i> a property named LastAccessTime. That\u2019s why, any time you\u2019re working with an unfamiliar cmdlet, you might try passing the returned data to the Get-Member<\/b> cmdlet, like so:<\/p>\n
Get-ChildItem C:\\Scripts | Get-Member \u2013membertype property\n<\/pre>\n
That\u2019s going to return information about all the properties available to an object; in other words information like this:<\/p>\n
Name              MemberType Definition\n----              ---------- ----------\nAttributes        Property   System.IO.FileAttributes Attributes {get;set;}\nCreationTime      Property   System.DateTime CreationTime {get;set;}\nCreationTimeUtc   Property   System.DateTime CreationTimeUtc {get;set;}\nDirectory         Property   System.IO.DirectoryInfo Directory {get;}\nDirectoryName     Property   System.String DirectoryName {get;}\nExists            Property   System.Boolean Exists {get;}\nExtension         Property   System.String Extension {get;}\nFullName          Property   System.String FullName {get;}\nIsReadOnly        Property   System.Boolean IsReadOnly {get;set;}\nLastAccessTime    Property   System.DateTime LastAccessTime {get;set;}\nLastAccessTimeUtc Property   System.DateTime LastAccessTimeUtc {get;set;}\nLastWriteTime     Property   System.DateTime LastWriteTime {get;set;}\nLastWriteTimeUtc  Property   System.DateTime LastWriteTimeUtc {get;set;}\nLength            Property   System.Int64 Length {get;}\nName              Property   System.String Name {get;}\n<\/pre>\n\n<\/thead>\n\n\n
\n
Note<\/b>. To return a list of all the methods for an object set the \u2013membertype<\/b> parameter to method<\/i>. Or just leave the parameter off altogether to return both methods and properties.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
The point is, Windows PowerShell doesn\u2019t always show you all the properties and property values for a given object. Fortunately, though, you can always get at all of those values by using Select-Object and the wildcard character:<\/p>\n
Get-ChildItem C:\\Scripts | Select-Object *\n<\/pre>\n
Or, if you want only a few specified properties, do what we did in our sample script and indicate the desired properties by name:<\/p>\n
Select-Object FullName, LastAccessTime\n<\/pre>\n
Stuff like that is just good to know, even if you never plan on looking at the LastAccessTime of a file.<\/p>\n
So will our script actually return<\/i> the last access time for all the Microsoft Office documents in C:\\Scripts and its subfolders? Well, there\u2019s only one way to find out:<\/p>\n
FullName                                                    LastAccessTime\n--------                                                    --------------\nC:\\Scripts\\New Folder\\challenge.mdb                         4\/9\/2008 10:22:32 AM\nC:\\Scripts\\New Folder\\scores.mdb                            3\/12\/2008 10:55:40 AM\nC:\\Scripts\\New Folder\\Scores.xls                            4\/3\/2008 8:31:37 AM\nC:\\Scripts\\New Folder\\test.mdb                              3\/12\/2008 10:55:42 AM\nC:\\Scripts\\New Folder\\test.ppt                              4\/3\/2008 8:31:39 AM\nC:\\Scripts\\New Folder\\test.xls                              4\/3\/2008 8:31:40 AM\nC:\\Scripts\\New Folder\\Test1.doc                             4\/3\/2008 8:31:40 AM\nC:\\Scripts\\New Folder\\test2.xls                             4\/3\/2008 8:31:40 AM\nC:\\Scripts\\pool.mdb                                         4\/10\/2008 12:48:57 PM\nC:\\Scripts\\Test.doc                                         4\/10\/2008 12:48:59 PM\nC:\\Scripts\\test.xls                                         4\/10\/2008 12:48:59 PM\n<\/pre>\n
Well, whatta ya know?<\/p>\n
That\u2019s all we have time for today, KM; we hope that answers your question and we hope that will help you get the information you need. If it doesn\u2019t, well, don\u2019t worry about; we\u2019ll get back to you. And how will we know<\/i> that we need to get back to you? Hey, when you\u2019re a Scripting Guy you just have a feel for this sort of thing, you know?<\/p>\n
Well, that plus a database of everything you\u2019re doing, have done, ever will do, and have ever even thought<\/i> about doing. <\/p>\n
Remember, too that certificates and non-bobblehead prizes are on their way; bobbleheads will be shipped out shortly. If you forget, well, don\u2019t worry about that, either. We\u2019ll know to remind you.<\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! I am trying to use Windows PowerShell to look at files on my computer; what I want to do is get back a history (that is, the last time a document was accessed) for all the Microsoft Office files in a folder and its subfolders. However, I\u2019m not having much luck. Any […]<\/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":[38,49,3,12,45],"class_list":["post-55793","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-files","tag-office","tag-scripting-guy","tag-storage","tag-windows-powershell"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! I am trying to use Windows PowerShell to look at files on my computer; what I want to do is get back a history (that is, the last time a document was accessed) for all the Microsoft Office files in a folder and its subfolders. However, I\u2019m not having much luck. Any […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55793","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=55793"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55793\/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=55793"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=55793"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=55793"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}