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

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

Spacer Hey, Scripting Guy! Answer Script 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:

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:

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:

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:

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:

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:

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:

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

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:

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

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:

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.

Follow    

No Comment.