Office Space: Tips and Tricks for Scripting Microsoft Office Applications

Welcome to Office Space, the column that offers tips and tricks for scripting Microsoft® Office applications. We’ll post new tips every Tuesday and Thursday; to see an archive of previous tips, visit the Office Space Archive. And if you have particular questions about Microsoft Office scripting, feel free to send them to scripter@microsoft.com (in English, if possible). We can’t promise to answer all the questions we receive, but we’ll do our best.

Search for Files Using Microsoft Excel: Episode II

Now that people no longer seem to care who would win in a fight between Alien and Predator, a new debate has seized the world’s attention: which makes for the better series, the six-part Stars Wars saga or the two-part Office Space chronicle on using Microsoft Excel as a way to search for files? Modesty prevents us from offering an opinion as to which is better, but we can tell you which is faster: after all, while it took nearly 30 years to find out how Anakin Skywalker became Darth Vader, it took only a week to find out how to search for text inside Microsoft Office documents.

But, hey, don’t feel too bad, George Lucas; after all, not everything can be the Office Space column.

In our last exciting episode (now titled Episode 1) we introduced you to the notion of using Microsoft Excel (or any other Microsoft Office application) as a way to search for files on a computer. We then had one of those cliff-hanger endings, one which tantalized you with the prospect of using Excel to search for text inside Microsoft Office documents or to search for Office files that have been modified in the last x number of days. For those of you who have been sleeping in front of your computer, hoping to be the first to see the answers to those questions, the long wait is over. Welcome to Searching for Files, Episode 2: A More-or-Less New Beginning.

Let’s begin by taking a look at a script that searches for text inside all the Office documents found in C:\Scripts and its subfolders. (Note that the searches we’re conducting today are limited to Office documents; you can’t use this technique to search for text files or other types of files.) What we’re looking for are all the documents that have the name Ken Myer either in the document itself or in the document properties (e.g., Author, Title, Keywords, etc.):

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objSearch = objExcel.FileSearch
objSearch.Lookin = "C:\Scripts"
objSearch.SearchSubfolders = TRUE
objSearch.TextOrProperty = "Ken Myer"
objSearch.Execute

For Each strFile in objSearch.FoundFiles
    Wscript.Echo strFile
Next

objExcel.Quit

If you recall the exciting search scene from Episode 1 then this script should be very familiar. We begin by creating an instance of the Excel.Application object and then set the Visible property to True. That last step, by the way, is optional; we set Visible to True just so you can see Excel onscreen. We then create an instance of the FileSearch object, configure the search object parameters, and then call the Execute method. This returns a collection of all the documents that include the name Ken Myer. We then simply echo the file path of each document and then dismiss Excel.

The one difference between this script and the scripts from Episode 1 is this line of code:

objSearch.TextOrProperty = "Ken Myer"

In Episode 1 we were looking for particular file names or file extensions; thus we configured the FileName property. Here we’re looking for a particular text string so we configure the TextOrProperty property instead; all we need to do is assign TextOrProperty the value we’re looking for.

Incidentally, you can use wildcard characters when configuring the TextOrProperty value. For example, this line of code will cause your script to search for Ken Myer, Ken Meyer, Ken Meier, or any other string beginning with Ken M:

objSearch.TextOrProperty = "Ken M*"

If that’s not exciting enough for you, you can also use the FileType property to limit your file search to specific document types (for example, your script will search only Microsoft Word documents and not PowerPoint slides and Excel spreadsheets). This next script searches for Ken Myer in Microsoft Word documents only. To perform that amazing feat we simply defined a constant named msoFileTypeWordDocuments and set the value to 3. We then set the value of the FileType property to this constant:

objSearch.FileType = msoFileTypeWordDocuments

That’s pretty much it. The completed script looks like this:

Const msoFileTypeWordDocuments = 3

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objSearch = objExcel.FileSearch
objSearch.Lookin = "C:\Scripts"
objSearch.SearchSubfolders = TRUE
objSearch.TextOrProperty = "Ken Myer"
objSearch.FileType = msoFileTypeWordDocuments
objSearch.Execute

For Each strFile in objSearch.FoundFiles
    Wscript.Echo strFile
Next

objExcel.Quit

By the way, various document types constants (such as msoFileTypeWordDocuments) and their values are shown in the following table:

And of course you can search for more than one document type in a single search. To do that first make sure you define all the necessary constants. Set the FileType property to the first document type, then use the Add method to add additional document types to the FileTypes collection. For example, this code first assigns Microsoft Word documents to the FileType, then adds Excel workbooks as well:

objSearch.FileType = msoFileTypeWordDocuments
objSearch.FileTypes.Add msoFileTypeExcelWorkbooks

A completed script that searches for the value Ken Myer in both Word and Excel documents looks like this:

Const msoFileTypeWordDocuments = 3
Const msoFileTypeExcelWorkbooks = 4

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objSearch = objExcel.FileSearch
objSearch.Lookin = "C:\Scripts"
objSearch.SearchSubfolders = TRUE
objSearch.TextOrProperty = "Ken Myer"
objSearch.FileType = msoFileTypeWordDocuments
objSearch.FileTypes.Add msoFileTypeExcelWorkbooks
objSearch.Execute

For Each strFile in objSearch.FoundFiles
    Wscript.Echo strFile
Next

objExcel.Quit

You’re right: we already do have more action than Star Wars, Episode 2. But we’re not done just yet. We want to show you one other script, a very handy one that can search for Microsoft Office documents based on their last modification date. As you’ll see, this is far easier than using WMI to search for files based on their last modification date; that’s because the FileSearch object includes a parameter (LastModified) that can be configured using a constant. For example, here’s a script that searches for all the Office documents in C:\Scripts (and its subfolders) that were last modified in the previous month:

Const msoLastModifiedLastMonth = 5

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objSearch = objExcel.FileSearch
objSearch.Lookin = "C:\Scripts"
objSearch.SearchSubfolders = TRUE
objSearch.LastModified = msoLastModifiedLastMonth
objSearch.Execute

For Each strFile in objSearch.FoundFiles
    Wscript.Echo strFile
Next

objExcel.Quit

All we did was take our basic search script and:

• Define a constant named msoLastModifiedLastMonth and set the value to 5.

• Assign this constant to the LastModified property.

That’s it; it’s that easy to get back a list of all the Office documents that were modified in the previous month.

Two quick notes. First, this script works with only Microsoft Office documents; you can’t search for text files or scripts or any other kind of file other than an Office document. Second, you can’t do highly specific searches, like looking for documents modified on April 17, 1938. Instead you are limited to the constants (and time intervals) in the following table:

If you need to search for any kind of file or for a specific modification date you’ll need to use a WMI script instead.

So does this bring the saga to a close? Well, maybe…but then again, maybe not. We still have a trick or two up our sleeves when it comes to searching for files using Microsoft Excel; whether or not we’ll get to those tricks any time soon is another question. In the meantime, we’ll leave you with this question to debate: who would win in a fight between Alien and the Scripting Guys?

Well, that question got settled a lot faster than we expected….