{"id":64043,"date":"2007-09-12T01:59:00","date_gmt":"2007-09-12T01:59:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/09\/12\/how-can-i-open-all-the-excel-spreadsheets-in-a-folder-and-run-a-specified-macro-found-in-each-of-those-spreadsheets\/"},"modified":"2007-09-12T01:59:00","modified_gmt":"2007-09-12T01:59:00","slug":"how-can-i-open-all-the-excel-spreadsheets-in-a-folder-and-run-a-specified-macro-found-in-each-of-those-spreadsheets","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-open-all-the-excel-spreadsheets-in-a-folder-and-run-a-specified-macro-found-in-each-of-those-spreadsheets\/","title":{"rendered":"How Can I Open All the Excel Spreadsheets in a Folder and Run a Specified Macro Found in Each of Those Spreadsheets?"},"content":{"rendered":"

\"Hey, \n

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<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

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\u2019s nation\u2019s-best winning streak at 14 games? If so, did you see Jake Locker score the Huskies\u2019 first touchdown?<\/P>\n

Oh, you say that you didn\u2019t<\/I> see Jake Locker score the Huskies\u2019 first touchdown? That\u2019s 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.<\/P>\n

And no, he was not<\/I> pleased about that. <\/P>\n

Because we know you\u2019re all dying to heat the story, we\u2019ll 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 \u2018n Ride by 11:15, and the Scripting Family (and Kyle) still managed to get there at 11:20.<\/P>\n

Considering the fact that the game didn\u2019t start until 12:30, and that Husky Stadium is less than a half hour\u2019s 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 \u2018n 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.<\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. Strangely enough, no, it didn\u2019t<\/I> help to have a representative from the bus company assuring everyone that buses ran every 10 minutes, even though more than 20 minutes had not passed and not a single bus had shown up. On the other hand, maybe the buses did<\/I> run every 10 minutes; it\u2019s just that they weren\u2019t running between Husky Stadium and the Park \u2018n Ride.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

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.<\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. Contrary to popular belief, the Scripting Guy who writes this column was not<\/I> in favor of using a sub-atomic disintegrator pistol, like the Manmelter 3600 ZX<\/B><\/A>, to dispose of all the cars that weren\u2019t going to the game, but were simply clogging up the freeway. Instead, he\u2019d be perfectly satisfied if all those people were simply arrested and thrown into jail.<\/P>\n

And yes, the Scripting Editor is<\/I> on vacation this week. How did you know? <\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

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, \u201cHey, look, Dad: the drawbridge is going up.\u201d<\/P>\n

Sure enough, the drawbridge spanning the Montlake Cut was<\/I> 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\u2019s put it this way: it\u2019s probably a good thing that he didn\u2019t<\/I> have a Manmelter 3600 ZX on him. <\/P>\n

Anyway, if you\u2019re wondering how the Scripting Guy who writes this column \u2013 who considers himself as big a fan of UW football as there is \u2013 managed to miss the Huskies\u2019 first touchdown in their biggest win in years, well, now you know: between the crowded restaurant, the bus that runs \u201cevery 10 minutes,\u201d the Seattle-area traffic, and the Montlake Cut drawbridge, well \u2026.. And if you\u2019re 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:<\/P>

strComputer = “tvsfrank”<\/p>\n
Set objWMIService = GetObject(“winmgmts:\\\\” & strComputer & “\\root\\cimv2”)<\/p>\n
Set colFileList = objWMIService.ExecQuery _\n    (“ASSOCIATORS OF {Win32_Directory.Name=’C:\\Test’} Where ” _\n        & “ResultClass = CIM_DataFile”)<\/p>\n
Set objExcel = CreateObject(“Excel.Application”, strComputer)\nobjExcel.DisplayAlerts = False<\/p>\n
For Each objFile In colFileList\n    If objFile.Extension = “xls” Then\n        Set objWorkbook = objExcel.Workbooks.Open(objFile.Name)\n        Set objWorksheet = objWorkbook.Worksheets(1)<\/p>\n
        objExcel.Run(“BoldfaceHeadings”)<\/p>\n
  objWorkbook.SaveAs objFile.Name\n        objWorkbook.Close\n    End If\nNext<\/p>\n
objExcel.Quit\n<\/PRE>\n
Good observation: this script does<\/I> look a little different than our usual WMI script, doesn\u2019t 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:<\/P>
strComputer = “.”\n<\/PRE>\n
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<\/I> like a computer name, but, in WMI, at least, the dot is short for \u201cthe local computer.\u201d We start our scripts with strComputer = “.” <\/B>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<\/I>. 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:<\/P>
strComputer = “atl-ws-01”\n<\/PRE>\n
So then why didn\u2019t we start this<\/I> script out by using strComputer = “.”<\/B>? Well, we wanted to create a script that \u2013 with minimal fuss \u2013 could run macros against Excel files found on the local computer or<\/I> 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<\/I> 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\u2019re working on the local computer.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. OK, that\u2019s not entirely true. We could<\/I> leave the computer name off altogether; in that case, Excel would default to the local machine. Like we said, however, we wanted a single script that \u2013with minimal fuss \u2013 could work against either a remote computer or a local computer. Assigning an actual computer name to the variable strComputer was the easiest way to do that.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
By the way, if you aren\u2019t totally sure what were talking about here (which wouldn\u2019t be a first for this column), well, don\u2019t 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.<\/P>\n
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:<\/P>
Set colFileList = objWMIService.ExecQuery _\n    (“ASSOCIATORS OF {Win32_Directory.Name=’C:\\Test’} Where ” _\n        & “ResultClass = CIM_DataFile”)\n<\/PRE>\n
At this point, we\u2019re ready to start running some macros.<\/P>\n
Or at least we will be after we create an instance of the Excel.Application<\/B> object:<\/P>
Set objExcel = CreateObject(“Excel.Application”, strComputer)\n<\/PRE>\n
You might have noticed that our call to the CreateObject<\/B> 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\u2019ll 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<\/B> 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.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. We should probably add that this script will fail if Excel has not been installed on atl-ws-01. But you already knew that, didn\u2019t you?<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
After we\u2019ve created an instance of Excel, our next step is to set the application\u2019s DisplayAlerts<\/B> property to False; this tells Excel not to display any messages or dialog boxes when we go to save our file. If we don\u2019t<\/I> do this we\u2019re going to get a message similar to this one when we try to save the file:<\/P>\n\n<\/THEAD>\n\n\n
\n
A file named C:\\Test\\Spreadsheet1.xls already exists in this location. Do you want to replace it?<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
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<\/B> button, as in, \u201cYes, I do<\/I> want to replace this file.\u201d<\/P>\n
You might note as well that we didn\u2019t bother setting Excel\u2019s Visible<\/B> 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:<\/P>
objExcel.Visible = False\n<\/PRE>\n
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\u2019t see Excel pop up on your screen, and Excel won\u2019t pop up on the remote computer\u2019s screen either.<\/P>\n
That\u2019s a security measure, and there\u2019s no way around it.<\/P>\n
OK, now<\/I> we\u2019re 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<\/B> property to make sure that we\u2019re working with an Excel file, making sure that the Extension is just xls<\/B>, without<\/I> the period:<\/P>
If objFile.Extension = “xls” Then\n<\/PRE>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. In Office 2007 you\u2019ll need to check for the file extension xlsm<\/B>: macro-enabled spreadsheet.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
Assuming that we do have an Excel speadsheet, we then use the Open<\/B> method and the following line of code to open the first file in the collection:<\/P>
Set objWorkbook = objExcel.Workbooks.Open(objFile.Name)\n<\/PRE>\n
Note that we don\u2019t have to know the path to the file; that\u2019s what the Name<\/B> 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):<\/P>
objExcel.Run(“BoldfaceHeadings”)\n<\/PRE>\n
After the macro runs, we call the SaveAs<\/B> method to save the newly-modified file, then call the Close<\/B> method to close this particular workbook:<\/P>
objWorkbook.SaveAs objFile.Name\nobjWorkbook.Close\n<\/PRE>\n
And then we loop around and repeat this process with the next file in the collection. When we\u2019re all done we exit the loop, call the Quit<\/B> method to terminate our instance of Excel, and call it a day.<\/P>\n
That should do it, BR. Incidentally, don\u2019t worry about the Scripting Guy who writes this column; he \u2013 oh, you say you weren\u2019t<\/I> worrying about the Scripting Guy who writes this column? Well, that\u2019s good, because he\u2019s already taking steps to ensure that he\u2019ll never be late for another football game: he\u2019s 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. <\/P>\n
Well, we\u2019re not saying that creating a machine like that is going to be easy<\/I>. But it\u2019s still easier than getting up half an hour earlier on a Saturday morning. That\u2019s never<\/I> going to happen.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[710,711,48,49,3,5],"class_list":["post-64043","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-excel-application","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64043","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=64043"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64043\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=64043"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=64043"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=64043"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}