How Can I Open All the Excel Spreadsheets in a Folder and Run a Specified Macro Found in Each of Those Spreadsheets?

ScriptingGuy1

Hey, Scripting Guy! How can I loop through a folder, open all the Excel files in that folder, then run a specified macro in each of those Excel files?

— BR

Hey, BR. So, did you watch college football this past Saturday? In particular, did you watch as the Washington Huskies dismantled Boise State 24-10, snapping the Bronco’s nation’s-best winning streak at 14 games? If so, did you see Jake Locker score the Huskies’ first touchdown?

Oh, you say that you didn’t see Jake Locker score the Huskies’ first touchdown? That’s too bad; neither did the Scripting Guy who writes this column. Thanks to a comedy of errors (none of which seemed particularly funny at the time), the Scripting Guy who writes this column showed up just after the Locker scored his touchdown, and just as the Huskies were kicking the extra point.

And no, he was not pleased about that.

Because we know you’re all dying to heat the story, we’ll start by noting that the morning got off to a somewhat rocky start. When the Scripting Family (and Kyle) arrived at their favorite breakfast spot, the restaurant was a little busy; that mean they had to wait a few minutes before they could be seated. In turn, that also meant that service was a tad bit slower than usual. But that was OK; the Scripting Guy who writes this column wanted to be at the Park ‘n Ride by 11:15, and the Scripting Family (and Kyle) still managed to get there at 11:20.

Considering the fact that the game didn’t start until 12:30, and that Husky Stadium is less than a half hour’s drive away, the Scripting Family was in pretty good shape. Or at least they would have been had one of the special Game Day express buses actually shown up at the Park ‘n Ride. In 19 years of attending Husky football games, the Scripting Guy who writes this column has never had to wait more than a few minutes before he was on the bus on his way; usually there are 2 or 3 buses lined up ready to take on riders. This time there were 0 buses lined up, and the increasingly-agitated Husky fans had to wait 25 minutes before one finally arrived.

Eventually the bus arrived and the Scripting Family (and Kyle) climbed aboard. They took off, merged onto the freeway, and then came to a dead stop. Welcome to Seattle, the traffic jam capital of the world.

After a slow and tedious crawl, the bus finally made it to its stop a few blocks from Husky Stadium. Taking into account wind velocity, the rotation of the earth, and the fact that games scheduled to start at 12:30 hardly ever start exactly at 12:30, the Scripting Guy who writes this column calculated that the Scripting Crew still had an outside chance at getting to their seats before kickoff. But then came the final indignity: as they hurried towards the stadium, the Scripting Son suddenly said, “Hey, look, Dad: the drawbridge is going up.”

Sure enough, the drawbridge spanning the Montlake Cut was going up, slowly and tediously, the better to let some dumb sailboat pass underneath. How did the Scripting Guy who writes this column handle this turn of events? Let’s put it this way: it’s probably a good thing that he didn’t have a Manmelter 3600 ZX on him.

Anyway, if you’re wondering how the Scripting Guy who writes this column – who considers himself as big a fan of UW football as there is – managed to miss the Huskies’ first touchdown in their biggest win in years, well, now you know: between the crowded restaurant, the bus that runs “every 10 minutes,” the Seattle-area traffic, and the Montlake Cut drawbridge, well ….. And if you’re wondering how you can write a script that loops through a folder, opens all the Excel files in that folder, then runs a specified macro in each of those Excel files, well, now you know that, too:

strComputer = “tvsfrank”

Set objWMIService = GetObject(“winmgmts:\\” & strComputer & “\root\cimv2”)
Set colFileList = objWMIService.ExecQuery _
    (“ASSOCIATORS OF {Win32_Directory.Name=’C:\Test’} Where ” _
        & “ResultClass = CIM_DataFile”)
Set objExcel = CreateObject(“Excel.Application”, strComputer)
objExcel.DisplayAlerts = False
For Each objFile In colFileList
    If objFile.Extension = “xls” Then
        Set objWorkbook = objExcel.Workbooks.Open(objFile.Name)
        Set objWorksheet = objWorkbook.Worksheets(1)
        objExcel.Run(“BoldfaceHeadings”)
  objWorkbook.SaveAs objFile.Name
        objWorkbook.Close
    End If
Next
objExcel.Quit

Good observation: this script does look a little different than our usual WMI script, doesn’t it? As everyone probably knows by now, any time we write a script that uses WMI (and, in this script, we use WMI to retrieve a collection of all the files found in the folder C:\Test) we invariably start things out with this line of code:

strComputer = “.”

Why? Well, in our WMI scripts we typically assign the name of the computer we want to run the script against to the variable strComputer. Granted, a dot (.) might not look like a computer name, but, in WMI, at least, the dot is short for “the local computer.” We start our scripts with strComputer = “.” because that allows us to post scripts that, without any additional coding, will run against the local computer, regardless of the actual name of that computer. We then point out that you can run that same script against a remote computer simply by assigning the name of that computer to the variable strComputer. You know, like this:

strComputer = “atl-ws-01”

So then why didn’t we start this script out by using strComputer = “.”? Well, we wanted to create a script that – with minimal fuss – could run macros against Excel files found on the local computer or against Excel files found on a remote machine. As we noted, WMI uses the dot as a shorthand method for designating the local computer; for better or worse, however, Excel does not use the dot as a stand-in for the local computer. When we create an instance of Excel, we need to be able to specify which computer we want to run the script against. To do that, however, we need to specify the actual computer name, even if we’re working on the local computer.

By the way, if you aren’t totally sure what were talking about here (which wouldn’t be a first for this column), well, don’t worry too much about it. Just assign a computer name to the variable strComputer (even if you want to work with spreadsheets found on the local computer) and everything will be fine.

After we make our connection to the WMI service, we then execute the following query in order to return a collection of all the files found in the folder C:\Test:

Set colFileList = objWMIService.ExecQuery _
    (“ASSOCIATORS OF {Win32_Directory.Name=’C:\Test’} Where ” _
        & “ResultClass = CIM_DataFile”)

At this point, we’re ready to start running some macros.

Or at least we will be after we create an instance of the Excel.Application object:

Set objExcel = CreateObject(“Excel.Application”, strComputer)

You might have noticed that our call to the CreateObject method also looks a little different than it usually does. (Talk about Scripting Guys Gone Wild, huh?!?) As you can see, we pass CreateObject two parameters: Excel.Application (the ProgID of the object we want to create), and the variable strComputer. (Typically our CreateObject calls only include the ProgID.) When we pass CreateObject two parameters we’ll get a new instance of our object (in this case, Excel.Application); in addition, that instance will be created on the specified in the second parameter. Because we assigned the value atl-ws-01 to the variable strComputer, our instance of Excel is going to be created on the computer atl-ws-01. And that will happen regardless of whether atl-ws-01 is the local computer or a remote computer.

After we’ve created an instance of Excel, our next step is to set the application’s DisplayAlerts property to False; this tells Excel not to display any messages or dialog boxes when we go to save our file. If we don’t do this we’re going to get a message similar to this one when we try to save the file:

If we set DisplayAlerts to False, Excel will suppress message boxes like that and, instead, use the default operation. In this case, the default operation is clicking the Yes button, as in, “Yes, I do want to replace this file.”

You might note as well that we didn’t bother setting Excel’s Visible property to True. If you want to, go ahead and add the following line of code to the script, right after the line that configures the DisplayAlerts property:

objExcel.Visible = False

Keep in mind, however, that Excel will appear onscreen only when it is being run locally; no matter what value you assign to the Visible property, Excel will always run in a hidden window any time you run this script against a remote computer. The script will work just fine, but you won’t see Excel pop up on your screen, and Excel won’t pop up on the remote computer’s screen either.

That’s a security measure, and there’s no way around it.

OK, now we’re ready to start running some macros. To begin with, we set up a For Each loop to run through all the files in the returned collection. Inside that loop, the first thing we do is check the value of the Extension property to make sure that we’re working with an Excel file, making sure that the Extension is just xls, without the period:

If objFile.Extension = “xls” Then

Assuming that we do have an Excel speadsheet, we then use the Open method and the following line of code to open the first file in the collection:

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

Note that we don’t have to know the path to the file; that’s what the Name property is for. We go ahead and bind to the first worksheet in the file, then use this line of code to run a macro named BoldfaceHeadings (needless to say, this macro must be available within each of the spreadsheets):

objExcel.Run(“BoldfaceHeadings”)

After the macro runs, we call the SaveAs method to save the newly-modified file, then call the Close method to close this particular workbook:

objWorkbook.SaveAs objFile.Name
objWorkbook.Close

And then we loop around and repeat this process with the next file in the collection. When we’re all done we exit the loop, call the Quit method to terminate our instance of Excel, and call it a day.

That should do it, BR. Incidentally, don’t worry about the Scripting Guy who writes this column; he – oh, you say you weren’t worrying about the Scripting Guy who writes this column? Well, that’s good, because he’s already taking steps to ensure that he’ll never be late for another football game: he’s hard at work on a machine that will enable him to stop time. That way he can stop time at, say, 12:25, take his seat at the stadium, and then start time up again.

Well, we’re not saying that creating a machine like that is going to be easy. But it’s still easier than getting up half an hour earlier on a Saturday morning. That’s never going to happen.

ScriptingGuy1

Follow