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.

Using Microsoft Excel to – What’s That? Search for Files? (Can That Be Right?)

At least one of the Scripting Guys is – and always will be – a huge baseball fan. Now, that doesn’t mean that he thinks everything about the game is perfect. For example, this Scripting Guy is definitely not a fan of the specialization that is a part of baseball these days: designated hitters (players who hit but never play in the field); left-handed relief pitchers who only pitch to left-handed batters, and even then only in the 7th inning; managers who calculate longitude, latitude, barometric pressure, and Tibetan astrological charts before making out their starting lineup. Can’t anybody just go out and play baseball anymore?

Tthis manic obsession with specialization often shown in baseball helps explain why this same Scripting Guy finds it almost comforting to use Microsoft Office. (Hey, we said almost comforting.) Unlike modern-day baseball players Microsoft Office applications can do all sorts of things. It should come as no surprise that Microsoft PowerPoint can be used to create slides. No one would be shocked to hear that you can use Microsoft Word to do word processing. That’s a given.

No, what’s really cool is the fact that you can often use Office applications to accomplish other tasks, tasks that are much harder to carry out without Office. For example, suppose you have a folder on your computer named D:\Music. Inside that folder are all sorts of subfolders, each one containing a number of Windows Media files. You’d like to write a script that can retrieve a list of all those media files. What’s the best scripting technology to use for that task?

You’re absolutely right: Microsoft Excel. (We would also have accepted Microsoft Word of Microsoft PowerPoint.) Couldn’t we use the FileSystemObject to search for files in D:\Music? Sure; the only problem is that we’d need to write a recursive function in order to search all the subfolders as well, and recursive functions can be tricky to write, at best. (For an example of searching using a recursive function see this Hey, Scripting Guy! column.) OK, but couldn’t we use WMI to search for files? Of course. But, then again, we want to search for specific files in a specific set of folders and – as we’ll see in a second – the WMI syntax for doing a finely-targeted search like that can get pretty complicated. (WMI is best at doing a blanket search of an entire computer.)

By contrast, here’s a simple, easy-to-decipher Microsoft Excel script that returns a list of all the .WMA files found in D:\Music and its subfolders:

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

Set objSearch = objExcel.FileSearch
objSearch.Lookin = "D:\Music"
objSearch.SearchSubfolders = TRUE
objSearch.FileName = "*.wma"
objSearch.Execute

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

objExcel.Quit

Pretty nice, especially when you compare it to this simplified WMI script that lists all the files in D:\Music and its subfolders (this is simplified because it lists all the files, not just those with a .wma file extension):

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
 
strFolderName = "D:\Music"
 
Set colSubfolders = objWMIService.ExecQuery _
    ("Associators of {Win32_Directory.Name='" & strFolderName & "'} " _
        & "Where AssocClass = Win32_Subdirectory " _
            & "ResultRole = PartComponent")
 
Wscript.Echo strFolderName
 
arrFolderPath = Split(strFolderName, "\")
strNewPath = ""
For i = 1 to Ubound(arrFolderPath)
    strNewPath = strNewPath & "\\" & arrFolderPath(i)
Next
strPath = strNewPath & "\\"
 
Set colFiles = objWMIService.ExecQuery _
    ("Select * from CIM_DataFile where Path = '" & strPath & "'")
 
For Each objFile in colFiles
    Wscript.Echo objFile.Name 
Next
 
For Each objFolder in colSubfolders
    GetSubFolders strFolderName
Next
 
Sub GetSubFolders(strFolderName)
    Set colSubfolders2 = objWMIService.ExecQuery _
        ("Associators of {Win32_Directory.Name='" & strFolderName & "'} " _
            & "Where AssocClass = Win32_Subdirectory " _
                & "ResultRole = PartComponent")
 
    For Each objFolder2 in colSubfolders2
        strFolderName = objFolder2.Name
        Wscript.Echo
        Wscript.Echo objFolder2.Name
        arrFolderPath = Split(strFolderName, "\")
        strNewPath = ""
        For i = 1 to Ubound(arrFolderPath)
            strNewPath = strNewPath & "\\" & arrFolderPath(i)
        Next
        strPath = strNewPath & "\\"
 
        Set colFiles = objWMIService.ExecQuery _
            ("Select * from CIM_DataFile where Path = '" & strPath & "'")
 
        For Each objFile in colFiles
            Wscript.Echo objFile.Name 
        Next
 
        GetSubFolders strFolderName
    Next
End Sub

And, yes, we apologize: we should have recommended that you put on protective goggles before looking at that code.

So how does our Excel search script work? Well, we begin by creating an instance of the Excel.Application object and then setting the Visible property to True. Note that we set Visible to True simply so that Excel will appear on screen and you’ll have a visual indication that the script is working. If you leave that line of code out the script will work just fine, and no one will ever know that you’re cheating and using Excel as your search engine.

Note. We should also point out that the FileSearch object is actually a Microsoft Office object and is thus available in other Office applications. If you’d rather do file searches using PowerPoint, hey, do file searches using PowerPoint. You’ll find the FileSearch object there as well.

After instantiating Excel we create an instance of the FileSearch object, then specify the following properties of that object:

Property	Description
Lookin	The starting point for our search. In this case we want to search only D:\Music and its subfolders, so we specify D:\Music as the value for the Lookin property. What if we wanted to search all the folders on drive D? In that case, we’d specify D:\ as the value for the Lookin property. Ah, you say, but I want to search two different locations. Hey, no problem: just include both locations when assigning a value to the Lookin property, separating the locations using a semicolon. For example, this line of code tells Excel to search the folder D:\Music and the folder D:\Archive: objSearch.Lookin = "D:\Music;D:\Archive"
SearchSubfolders	OK, but what if we want to search all the subfolders of a folder? Do we have to write some kind of crazy recursive function? Nope; just set the value of SearchSubfolders to True. Want to search just D:\Music without searching any of its subfolders? Then set SearchSubfolders to False. It’s that easy.
FileName	FileName is – surprise – the name of the file we want to search for. You can use standard wildcard characters in your file names; thus we search for *.wma, which returns any file with a .wma file extension. If we wanted to look for a specific file we could specify that file name. Thus this code looks only for the file named mysong.wma: objSearch.FileName = "mysong.wma" Oh, and did we mention that you can search for more than one file or file type at the same time? Well we should have, because you can. Again, just specify the different file names/types, separating them using a semicolon. For example, this code tells Excel to search for both .wma and .mp3 files: objSearch.FileName = "*.wma;*.mp3"

All in all a bit easier than configuring a WMI script to do the same thing.

After setting the property values we call the Execute method to kick off the search. The Execute method returns a collection (referred to as the FoundFiles collection) that consists of the file paths for all the files that meet the search criteria. To list those files and file paths all we have to do is set up a For Each loop that iterates through the FoundFiles collection:

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

That’s it. When we run the script we get back output similar to this (and, depending on the size of the D:\Music folder, you’ll get this output back in a matter of seconds):

D:\Music\John Prine\Lost Dogs and Mixed Blessings\01 New Train.wma
D:\Music\John Prine\Lost Dogs and Mixed Blessings\10 Same Thing Happened to Me.wma
D:\Music\Dire Straits\Brothers in Arms\01 So Far Away.wma
D:\Music\Dire Straits\Brothers in Arms\02 Money for Nothing.wma
D:\Music\Dire Straits\Brothers in Arms\03 Walk of Life.wma

Yes, very nice. But here’s the really nice part. Microsoft Office isn’t some left-handed relief pitcher that can do only one thing (in this case, search for a particular file name or file extension). No, Microsoft Office has some more general search capabilities, and next week we’ll look at how you can use Office to search for things like text within a file, files last modified within a specific time period, or, say, files authored by a specific person. Stay tuned! (Yes, it will be hard to wait until then, won’t it?)