Hey, Scripting Guy! How can I create a script that will list all the files in a directory that have a specific value in a specific cell of an Office Excel workbook? And can that be expanded into a find/replace function?

— RDC

Hey, RDC. Now that we’re finally beginning to get a bunch of scores posted for the 2008 Winter Scripting Games people are getting an opportunity to look upon those scores, and often looking upon those scores with horror: A 0 in Event 2?!? How could I possibly get a 0 in Event 2?!?

Well, as it turns out there are a lot of ways you could get a 0 in Event 2:

Anyway, the point of all this is that you might have deserved a 0; alternatively, you might have been a victim of fate, a victim of a less-than-perfect email system, or a victim of a less-than-perfect Scripting Guy. (Don’t worry, Jean; we won’t mention any names.) But guess what? Getting a 0 is not the end of the world, at least not in the Scripting Games. After all, in the Scripting Games, you have unlimited do-overs. Not sure why you got a 0? Then write to us and we’ll tell you what happened when we tested your script. If it turns out we made a mistake we’ll fix it right then and there. And if it turns out that you made a mistake (heaven forbid!), well, then you’ll have the chance to make the necessary changes and resubmit. And yes, you can do that even if the deadline for the event has already passed. Unlimited do-overs, even if time has already expired.

Wouldn’t it be nice if the folks who ran the SAT test would let you do that?

And no, that’s not cheating. After all, the Scripting Games are designed to let people have a little bit of fun, and to learn something along the way. If you’re able to take a broken script and fix it, well, that sure sounds like you learned something along the way. And we’ll make sure you get rewarded for that.

In other words, let us know if you have a question about your score. It might take us a day or so to get back to you with an answer, but we will get back to you. And you will have an opportunity to fix the problem.

And what if you don’t have any problems, what if everything is going perfectly for you? Well, in that case, you might want to take a few minutes to look at a script that can go through all the Microsoft Excel files in a folder and report back which files have a specific value in a specific cell:

strComputer = "atl-fs-001"

Set objExcel = CreateObject("Excel.Application", strComputer)

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

Set FileList = objWMIService.ExecQuery _
    ("ASSOCIATORS OF {Win32_Directory.Name='c:\Temp'} Where " _
        & "ResultClass = CIM_DataFile")

For Each objFile In FileList
    If objFile.Extension = "xls" Then
        Set objWorkbook = objExcel.Workbooks.Open(objFile.Name)
        Set objWorksheet = objWorkbook.Worksheets(1)
        If objExcel.Cells(4,1) = "Test" Then
            Wscript.Echo objFile.FileName
        End If
        objWorkbook.Close
    End If
Next

objExcel.Quit

OK, let’s see if we can figure out how this all works. As you can see, we start out by assigning the name of the computer where the Excel files live to a variable named strComputer. Now, usually when we do this we assign a dot (representing the local computer) to the variable strComputer:

strComputer = "."

So why don’t we assign a dot to strComputer in this script? Well, the dot does represent the local computer, but only if you’re using WMI. We’re going to use some WMI code in this script, but we’re also going to use some Excel code. Excel has no idea that the dot is supposed to represent the local computer; if you use strComputer = “.” this time around the script will fail when Excel tries to track down a computer named dot (.). The moral of the story? To prevent this script from failing make sure you set strComputer to an actual computer name even if you are running this against the local computer.

Oh, and do unto others as you would have them do unto you. That’s a good moral, too.

After assigning a value to strComputer we then use this line of code to create an instance of the Excel.Application object:

Set objExcel = CreateObject("Excel.Application", strComputer)

Now, admittedly, you’re used to seeing a CreateObject command that looks more like this:

Set objExcel = CreateObject("Excel.Application")

That command creates an instance of Excel on the local computer. The command we used, with its optional second parameter, creates an instance of Excel on the computer whose name is stored in the variable strComputer. And yes, that could be the local computer, or it could be a remote computer.

Two quick notes about this. First, you can’t run this script against a remote computer unless Excel is installed on that remote computer. Second, you’ll notice that we don’t bother setting the Visible property to True, something we typically do in order to make Excel visible onscreen. Why don’t we do that here? Well, if we’re going to run the script only locally we could do that. (Although each file will open and close so quickly there might not be much point.) However, if we run the script remotely we can’t make Excel visible onscreen; that’s due to security precautions. Any process you start on a remote machine will always run in a hidden window. So we just decided to skip the whole make-excel-visible-onscreen thing altogether.

After cranking up Excel we bind to the WMI service on the target computer, then use this line of code to return a collection of all the files found in the folder C:\Temp:

Set FileList = objWMIService.ExecQuery _
    ("ASSOCIATORS OF {Win32_Directory.Name='c:\Temp'} Where " _
        & "ResultClass = CIM_DataFile")

Once we have this collection in hand, we set up a For Each loop to walk us through each file in the folder C:\Temp. For each of these files, the first thing we do is check to see if we’re dealing with an Excel file; that is, are we dealing with a file that has a .XLS file extension:

If objFile.Extension = "xls" Then

If the file doesn’t have a .XLS file extension then we simply go back to the top of the loop and try again with the next file in the collection. If the file does have a .XLS file extension, well, then we get to do all sorts of cool things.

Like what? Well, for starters, we use this line of code and Excel’s Open method to open the file in question:

Set objWorkbook = objExcel.Workbooks.Open(objFile.Name)

Oh, and remember, if we’re running this script against a remote machine then the Name property represents the path on that remote computer. In other words, it’s the file C:\Temp\File1.xls on the computer atl-fs-001.

As soon as the file is open we use this line of code to bind to the worksheet in that file:

Set objWorksheet = objWorkbook.Worksheets(1)

At that point we check to see if the value of cell A4 (that is, row 4, column 1) is equal to the target value Test:

If objExcel.Cells(4,1) = "Test" Then

If it is, we’re going to echo back the name (FileName) of the file. If it’s not, we’re not going to do much of anything other than go back to the top of the loop and try again with the next file in the collection.

By the time we’re finished we should have a nice little report of all the files where cell A4 is equal to Test:

file1
file3
file4
Cool, huh?
Now, RDC also asked if this can be expanded into a find/replace function. To tell you the truth, we’re not 100% sure what you mean by that, RDC; however, we’re guessing that you’d like to be able to change the value of cell A4. (If that’s not correct just let us know.) The following script will check all the spreadsheets in a file to see if cell A4 is equal to Test; if it is, the script will then change the value of cell A4 to Modified.
Here’s the code:
strComputer = "atl-fs-001"

Set objExcel = CreateObject("Excel.Application", strComputer)

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

Set FileList = objWMIService.ExecQuery _
    ("ASSOCIATORS OF {Win32_Directory.Name='c:\Temp'} Where " _
        & "ResultClass = CIM_DataFile")

For Each objFile In FileList
    If objFile.Extension = "xls" Then
        Set objWorkbook = objExcel.Workbooks.Open(objFile.Name)
        Set objWorksheet = objWorkbook.Worksheets(1)
        If objExcel.Cells(4,1) = "Test" Then
            Wscript.Echo objFile.FileName 
            objExcel.Cells(4,1) = "Modified"
        End If
        objWorkbook.Save
        objWorkbook.Close
    End If
Next

objExcel.Quit

We’ve really made only a couple of changes here. For one, after we echo back the name of any file where cell A4 is equal to Test we use this line of code to change the value of that cell to Modified:
objExcel.Cells(4,1) = "Modified"
And then, before we close the file, we use the Save method to save that change:
objWorkbook.Save
That’s all we have to do.
That’s also all the time we have for today; after all, we’ve got scripts to test and emails to answer. Remember, if you have a question about your score please let us know; we’ll be sure and come up with an answer for you.





Note. OK, sure, usually that answer will be, “Apparently Jean made a mistake.” But we wouldn’t want to hurt her feelings by actually telling anyone that.