Hey, Scripting Guy! How Can I Use Windows PowerShell to Look at All the Microsoft Office Documents in a Folder?

ScriptingGuy1

Hey, Scripting Guy! Question

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’m not having much luck. Any ideas would be appreciated.
— KM

SpacerHey, Scripting Guy! AnswerScript Center

Hey, KM. We don’t know if you participated in the 2008 Winter Scripting Games – well, OK, actually that’s 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’ve ever used any non-Microsoft software. However, we’re not supposed to let anyone know that we track that information, so kind of keep that to yourself, OK?

Note. Actually, we don’t keep track of information like that; in fact, we don’t keep track of any 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 – and analyzing – reams of data about its customers. The truth is, we at Microsoft have enough trouble keeping track of our own information (anyone seen any archived Scripting Guys webcasts lately?), let alone anyone else’s.

At any rate, if you participated in the 2008 Winter Scripting Games – and we’re not saying that you did, only saying if you did, well, then we have some good news for you: we’re 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.

Note. Ever wonder why Scripting Guy Peter Costantini decided to become a Microsoft PM? It had nothing to do with advancing his career; it’s because he couldn’t stand the thought of signing Joseph Peter Costantini 700+ times. Thank goodness Jean and Greg have very short names.

In fact, with the exception of a few for a handful of people who sent their address in late, all the certificates have been signed and mailed; we’ll 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.

As for the bobbleheads, well, we’ll start shipping those out very soon: we’re just waiting on the boxes we need to pack the things. As soon as those boxes arrive we’ll start sending out bobbleheads.

And no, there’s no need to drop us a line when you actually receive your prize. We’ll know when you receive your prize.

Well, not that we’ll actually know mind you, because we don’t keep track of information like that. But we will hire a psychic to keep us informed of when the prizes get delivered; that will save you the trouble of notifying us.

Note. 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 “You may be trying too hard, or your ‘rational’ mind may be blocking your intuitive mind.” Which would make sense if he actually had a rational mind.

Incidentally, the Psychics for Hire Web site also uses cookies to keep track of whether you’ve visited the site or not. You’d think that someone there would just know whether or not you’ve visited the site.

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’t take part in the Scripting Games? Sadly, no; we don’t. The truth is, if you didn’t take part in the Scripting Games you’ll probably never get any good news ever again. By not participating in the Scripting Games you’ve pretty much thrown your life away.

Oh, wait; never mind. Here’s some good news after all: a Windows PowerShell script that reports back the LastAccessTime property for all the Microsoft Office documents in a folder and its subfolders:

Get-ChildItem C:\Scripts -recurse -include *.doc, *.xls, *.ppt, *.mdb | 
Select-Object FullName, LastAccessTime

Before we go any further we should note that we cheated a little bit here. There might be a really easy way to determine whether a file is a Microsoft Office document or not; however, we weren’t 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’s orders, we’re 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.

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:

Set colFiles = objWMIService._
    ("SELECT * FROM CIM_DataFile WHERE Path = '\\Scripts\\' " & _
        "AND Drive = 'C:' AND (Extension = 'doc'" OR Extension = 'xls' OR Extension = 'ppt' OR Extension = 'mdb'"))

Etc., etc.

Fortunately, though, Windows PowerShell – and the Get-ChildItem cmdlet – helps you avoid that sort of craziness; as it is, it’s much easier to specify multiple file extensions using Get-ChildItem’s –include parameter than it is to write some gargantuan Where clause. In fact, if you look closely at our call to Get-ChildItem you’ll see how simple it really is:

Get-ChildItem C:\Scripts -recurse -include *.doc, *.xls, *.ppt, *.mdb

All we’re doing here us asking Get-ChildItem to return all the items found in the C:\Scripts folder. Because we’re also interested in the items found in any subfolders of C:\Scripts (and any subfolders of those subfolders) we also tack on the –recurse parameter; that causes Get-ChildItem to search the entire directory tree, and without us having to write a complicated recursive function.

Oh, and because we care about only certain files (or, more correctly, certain file extensions), we also tossed in the –include parameter, followed by the file extensions of interest:

-include *.doc, *.xls, *.ppt, *.mdb

Note. Needless to say, we aren’t 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), and make sure you separate the extensions using a comma.

You might have noticed that we don’t use only the Get-ChildItem cmdlet; we also use the Select-Object cmdlet. Why do we need two cmdlets to carry out a single task? Well, by default, Get-ChildItem returns data in the following format:

Mode                LastWriteTime     Length Name
----                -------------     ------ ----
-a---        12/17/2007   9:33 PM     905216 challenge.mdb
-a---          1/3/2007   8:00 AM     266240 scores.mdb
-a---          1/3/2007   8:00 AM      92160 Scores.xls
-a---         1/28/2008  10:50 AM     274432 test.mdb
-a---          1/3/2007   8:00 AM      94720 test.ppt
-a---          2/5/2008  10:27 PM      14848 test.xls
-a---          1/8/2008  12:55 PM      21504 Test1.doc
-a---         1/18/2008   9:24 AM      13824 test2.xls

That’s nice, but it doesn’t include the property we’re really interested in: LastAccessTime. That’s we don’t immediately display the information returned by Get-ChildItem; if we do, we won’t 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 and LastAccessTime properties:

Select-Object FullName, LastAccessTime

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 – in advance – 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 a property named LastAccessTime. That’s why, any time you’re working with an unfamiliar cmdlet, you might try passing the returned data to the Get-Member cmdlet, like so:

Get-ChildItem C:\Scripts | Get-Member –membertype property

That’s going to return information about all the properties available to an object; in other words information like this:

Name              MemberType Definition
----              ---------- ----------
Attributes        Property   System.IO.FileAttributes Attributes {get;set;}
CreationTime      Property   System.DateTime CreationTime {get;set;}
CreationTimeUtc   Property   System.DateTime CreationTimeUtc {get;set;}
Directory         Property   System.IO.DirectoryInfo Directory {get;}
DirectoryName     Property   System.String DirectoryName {get;}
Exists            Property   System.Boolean Exists {get;}
Extension         Property   System.String Extension {get;}
FullName          Property   System.String FullName {get;}
IsReadOnly        Property   System.Boolean IsReadOnly {get;set;}
LastAccessTime    Property   System.DateTime LastAccessTime {get;set;}
LastAccessTimeUtc Property   System.DateTime LastAccessTimeUtc {get;set;}
LastWriteTime     Property   System.DateTime LastWriteTime {get;set;}
LastWriteTimeUtc  Property   System.DateTime LastWriteTimeUtc {get;set;}
Length            Property   System.Int64 Length {get;}
Name              Property   System.String Name {get;}

Note. To return a list of all the methods for an object set the –membertype parameter to method. Or just leave the parameter off altogether to return both methods and properties.

The point is, Windows PowerShell doesn’t 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:

Get-ChildItem C:\Scripts | Select-Object *

Or, if you want only a few specified properties, do what we did in our sample script and indicate the desired properties by name:

Select-Object FullName, LastAccessTime

Stuff like that is just good to know, even if you never plan on looking at the LastAccessTime of a file.

So will our script actually return the last access time for all the Microsoft Office documents in C:\Scripts and its subfolders? Well, there’s only one way to find out:

FullName                                                    LastAccessTime
--------                                                    --------------
C:\Scripts\New Folder\challenge.mdb                         4/9/2008 10:22:32 AM
C:\Scripts\New Folder\scores.mdb                            3/12/2008 10:55:40 AM
C:\Scripts\New Folder\Scores.xls                            4/3/2008 8:31:37 AM
C:\Scripts\New Folder\test.mdb                              3/12/2008 10:55:42 AM
C:\Scripts\New Folder\test.ppt                              4/3/2008 8:31:39 AM
C:\Scripts\New Folder\test.xls                              4/3/2008 8:31:40 AM
C:\Scripts\New Folder\Test1.doc                             4/3/2008 8:31:40 AM
C:\Scripts\New Folder\test2.xls                             4/3/2008 8:31:40 AM
C:\Scripts\pool.mdb                                         4/10/2008 12:48:57 PM
C:\Scripts\Test.doc                                         4/10/2008 12:48:59 PM
C:\Scripts\test.xls                                         4/10/2008 12:48:59 PM

Well, whatta ya know?

That’s 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’t, well, don’t worry about; we’ll get back to you. And how will we know that we need to get back to you? Hey, when you’re a Scripting Guy you just have a feel for this sort of thing, you know?

Well, that plus a database of everything you’re doing, have done, ever will do, and have ever even thought about doing.

Remember, too that certificates and non-bobblehead prizes are on their way; bobbleheads will be shipped out shortly. If you forget, well, don’t worry about that, either. We’ll know to remind you.

0 comments

Discussion is closed.

Feedback usabilla icon