{"id":56123,"date":"2008-02-26T23:17:00","date_gmt":"2008-02-26T23:17:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2008\/02\/26\/hey-scripting-guy-how-can-i-check-the-value-of-a-specific-cell-in-a-bunch-of-office-excel-spreadsheets\/"},"modified":"2008-02-26T23:17:00","modified_gmt":"2008-02-26T23:17:00","slug":"hey-scripting-guy-how-can-i-check-the-value-of-a-specific-cell-in-a-bunch-of-office-excel-spreadsheets","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-check-the-value-of-a-specific-cell-in-a-bunch-of-office-excel-spreadsheets\/","title":{"rendered":"Hey, Scripting Guy! How Can I Check the Value of a Specific Cell in a Bunch of Office Excel Spreadsheets?"},"content":{"rendered":"

\"Hey,<\/p>\n

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?<\/p>\n

— RDC<\/p>\n

\"Spacer\"\"Hey,\"Script<\/a><\/p>\n

Hey, RDC. Now that we’re finally beginning to get a bunch of scores posted for the 2008 Winter Scripting Games<\/b><\/a> 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<\/i> get a 0 in Event 2?!?<\/p>\n

Well, as it turns out there are a lot of ways you could get a 0 in Event 2:<\/p>\n\n\n\n\n\n\n
•<\/td>\n\n

Maybe your script just didn’t work. For example, maybe the correct answer to Event 2 is red<\/i> but your script reported back blue<\/i>. That’s going to result in a 0.<\/p>\n<\/td>\n<\/tr>\n

•<\/td>\n\n

Maybe your script got munged up in-transit. We’ve had a couple of scripts where a few lines at the bottom were cut off. Because we had no way of knowing that those lines were cut off all of those scripts got a 0, too.<\/p>\n<\/td>\n<\/tr>\n

•<\/td>\n\n

Maybe there was some weird configuration issue. In at least one case a competitor got a 0 because his Language and Regional settings were different from the Scripting Guys computer. How different? Different enough that a script that worked flawlessly on his computer gave absolutely bizarre data on the Scripting Guys’ computer. (For example, no matter what month you gave the script it insisted that you gave it January, 2008. It was like trying to hold a conversation with Scripting Guy Peter Costantini!)<\/p>\n<\/td>\n<\/tr>\n

•<\/td>\n\n

Maybe the Scripting Guys just plain screwed-up. Well, Scripting Guy Jean Ross, anyway. We can’t recall Scripting Guy Greg Stemp ever making a mistake.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Anyway, the point of all this is that you might have deserved<\/i> 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<\/i> 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.<\/p>\n

Wouldn’t it be nice if the folks who ran the SAT test would let you do that?<\/p>\n

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

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<\/i> get back to you. And you will<\/i> have an opportunity to fix the problem.<\/p>\n

And what if you don’t<\/i> 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:<\/p>\n

strComputer = \"atl-fs-001\"\n\nSet objExcel = CreateObject(\"Excel.Application\", strComputer)\n\nSet objWMIService = GetObject(\"winmgmts:\\\\\" & strComputer & \"\\root\\cimv2\")\n\nSet FileList = objWMIService.ExecQuery _\n    (\"ASSOCIATORS OF {Win32_Directory.Name='c:\\Temp'} Where \" _\n        & \"ResultClass = CIM_DataFile\")\n\nFor Each objFile In FileList\n    If objFile.Extension = \"xls\" Then\n        Set objWorkbook = objExcel.Workbooks.Open(objFile.Name)\n        Set objWorksheet = objWorkbook.Worksheets(1)\n        If objExcel.Cells(4,1) = \"Test\" Then\n            Wscript.Echo objFile.FileName\n        End If\n        objWorkbook.Close\n    End If\nNext\n\nobjExcel.Quit<\/pre>\n
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:<\/p>\n
strComputer = \".\"<\/pre>\n
So why don’t we assign a dot to strComputer in this<\/i> script? Well, the dot does<\/i> 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 = “.”<\/b> 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<\/i> running this against the local computer.<\/p>\n
Oh, and do unto others as you would have them do unto you. That’s a good moral, too.<\/p>\n
After assigning a value to strComputer we then use this line of code to create an instance of the Excel.Application<\/b> object:<\/p>\n
Set objExcel = CreateObject(\"Excel.Application\", strComputer)<\/pre>\n
Now, admittedly, you’re used to seeing a CreateObject<\/b> command that looks more like this:<\/p>\n
Set objExcel = CreateObject(\"Excel.Application\")<\/pre>\n
That<\/i> 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.<\/p>\n
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<\/b> 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<\/i> 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<\/i> make Excel visible onscreen; that’s due to security precautions. Any process you start on a remote machine will always<\/i> run in a hidden window. So we just decided to skip the whole make-excel-visible-onscreen thing altogether.<\/p>\n
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:<\/p>\n
Set FileList = objWMIService.ExecQuery _
    (\"ASSOCIATORS OF {Win32_Directory.Name='c:\\Temp'} Where \" _
        & \"ResultClass = CIM_DataFile\")<\/pre>\n
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:<\/p>\n
If objFile.Extension = \"xls\" Then<\/pre>\n\n<\/thead>\n\n\n
\n
Note<\/b>. In WMI the dot is not<\/i> considered part of the file extension. That’s why we’re looking for an extension equal to XLS rather than .<\/b>XLS.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
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<\/i> have a .XLS file extension, well, then we get to do all sorts of cool things.<\/p>\n
Like what? Well, for starters, we use this line of code and Excel’s Open<\/b> method to open the file in question:<\/p>\n
Set objWorkbook = objExcel.Workbooks.Open(objFile.Name)<\/pre>\n\n<\/thead>\n\n\n
\n
Note<\/b>. What’s that? Don’t we need to specify the full path when opening this file? Yes, we do, and, well, yes, we are. In WMI-speak, the file Name<\/b> is equivalent to the full file path.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
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.<\/p>\n
As soon as the file is open we use this line of code to bind to the worksheet in that file:<\/p>\n
Set objWorksheet = objWorkbook.Worksheets(1)<\/pre>\n
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<\/i>:<\/p>\n
If objExcel.Cells(4,1) = \"Test\" Then<\/pre>\n
If it is, we’re going to echo back the name (FileName<\/b>) 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.<\/p>\n
By the time we’re finished we should have a nice little report of all the files where cell A4 is equal to Test<\/i>:<\/p>\n
file1\nfile3\nfile4<\/pre>\n
Cool, huh?<\/p>\n
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<\/i> 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<\/i>; if it is, the script will then change the value of cell A4 to Modified<\/i>.<\/p>\n
Here’s the code:<\/p>\n
strComputer = \"atl-fs-001\"\n\nSet objExcel = CreateObject(\"Excel.Application\", strComputer)\n\nSet objWMIService = GetObject(\"winmgmts:\\\\\" & strComputer & \"\\root\\cimv2\")\n\nSet FileList = objWMIService.ExecQuery _\n    (\"ASSOCIATORS OF {Win32_Directory.Name='c:\\Temp'} Where \" _\n        & \"ResultClass = CIM_DataFile\")\n\nFor Each objFile In FileList\n    If objFile.Extension = \"xls\" Then\n        Set objWorkbook = objExcel.Workbooks.Open(objFile.Name)\n        Set objWorksheet = objWorkbook.Worksheets(1)\n        If objExcel.Cells(4,1) = \"Test\" Then\n            Wscript.Echo objFile.FileName \n            objExcel.Cells(4,1) = \"Modified\"\n        End If\n        objWorkbook.Save\n        objWorkbook.Close\n    End If\nNext\n\nobjExcel.Quit\n<\/pre>\n
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<\/i> we use this line of code to change the value of that cell to Modified<\/i>:<\/p>\n
objExcel.Cells(4,1) = \"Modified\"<\/pre>\n
And then, before we close the file, we use the Save<\/b> method to save that change:<\/p>\n
objWorkbook.Save<\/pre>\n
That’s all we have to do.<\/p>\n
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.<\/p>\n\n<\/thead>\n\n\n
\n
Note<\/b>. 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.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/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,48,49,3,5],"class_list":["post-56123","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/56123","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=56123"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/56123\/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=56123"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=56123"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=56123"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}