Hey, Scripting Guy! How can I tell if a specified worksheet exists in an Excel workbook?
— MVK
Hey, MVK. Back when he was in high school (that was about 3 or 4 years ago, of course), the Scripting Guy who writes this column was kind of half-watching a variety show on TV. The show featured a man and his dogs: “The Smartest Dogs in the World!” The man would show the dogs a cue card that had a multiple choice question on it. The dogs would – allegedly – read the cue card and then bark out the answer: one bark for A, two barks for B, etc.
Note. Sure, that sounds impressive. But, to tell you the truth, the questions were pretty easy; they were all questions like “Who was the first President of the United States?” Come on; even a cat could get that one right. |
At any rate, as this future Scripting Guy watched the show he suddenly realized how the trick worked: he figured out how the guy was telling his dogs which answer to bark out. Smelling an opportunity, the Scripting Guy recruited his brother and put together a similar act, one in which the two would lay an entire deck of cards down on a table. They would ask someone to select a card, and then – using his telekinetic powers – the Scripting Guy would beam the answer into his brother’s mind. One hundred percent of the time the Scripting Brother would come up with the correct answer! The Scripting Guy and his brother spent many happy hours enthralling friends and relatives with their act. And no one ever came close to figuring out how they did it.
The point: after mulling this over a bit, we’ve decided that psychic powers are the best way to determine whether a specified worksheet exists in an Excel spreadsheet.
What’s that? No, we aren’t going to tell you how the trick worked, even if you do promise not to tell anyone else. (What, you don’t think we’ve heard that before?) In fact, maybe it would be best to forego telekinesis altogether; now that we think about it, it might be better to use a script like this one instead:
Set objExcel = CreateObject(“Excel.Application”) Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)x = 0
For Each objWorksheet in objWorkbook.Worksheets If objWorksheet.Name = “Budget” Then x = 1 Exit For End If Next
objExcel.Quit
If x = 1 Then Wscript.Echo “The specified worksheet was found.” Else Wscript.Echo “The specified worksheet was not found.” End If
Granted, this script probably won’t get you on any variety shows, but – never mind, forget we said that. After all, if the guy with The World’s Smartest Dogs can get on a variety show, well who knows what you could do with this?
As you probably already guessed, there isn’t much magic to the script. We start out by creating an instance of the Excel.Application object, then use the Open method to open the file C:\Scripts\Test.xls.
Note. Isn’t this about the time we usually set the Visible property of Excel to True? Yes, it is. This time around, however, we decided to leave Excel running in an invisible window: that enables us to quickly open the workbook, look for the specified worksheet (a worksheet named Budget), and then close Excel, all without anyone knowing what we were doing or how we were doing it. (No, that’s not magic: after all, when you’re a Scripting Guy no one ever knows what you’re doing, let alone how – or why – you’re doing it.) |
We then have this seemingly-random little line of code:
x = 0
Why are we, totally out of the blue, assigning the value 0 to a variable named x? We don’t want to spoil the suspense, so hang tight; we’ll explain why in a minute.
Promise.
Our next step is to set up a For Each loop that loops through the collection of all the worksheets found in Test.xls; that’s what this line of code is for:
For Each objWorksheet in objWorkbook.Worksheets
Inside that For Each loop we simply check each worksheet to see if the sheet has a Name equal to Budget. If it doesn’t, we skip past the worksheet and check the next item in the collection. If the Name is equal to Budget, we then assign x the value 1:
x = 1
Now you can probably see what we’re doing here: we’re just using x as a simple way to track whether or not we’ve found a worksheet named Budget. This variable begins life with a value of 0; that value will never change … unless, of course, we find a worksheet named Budget. In turn, that means that we can determine whether or not the target worksheet was found simply by examining the value of x. If x is 0 then the worksheet wasn’t found. But if x is 1 ….
Note. Yes, by making a change here or there we could have reduced the size of this script by 3 or 4 lines. We chose not to do that simply because variables like our friend x can be very useful in scripting; instead, we decided to take the opportunity to show you a simple example of using a variable to keep track of some condition within the script. It might not seem like much, but it’s a good little trick to have in your repertoire. |
And one other thing: if a worksheet named Budget is found we also execute the Exit For statement in order to exit our For Each loop. After all, once we find a worksheet named Budget we don’t need to look through all the other worksheets in the collection; we’ve already found what we were looking for. So why not exit the loop and get on with things?
Good question: what does it mean to “get on with things,” at least in this script? Well, to begin with, we use this line of code to close Excel:
objExcel.Quit
And then we use this block of code to examine the value if x and report back whether or not the worksheet was found:
If x = 1 Then Wscript.Echo “The specified worksheet was found.” Else Wscript.Echo “The specified worksheet was not found.” End If
Yes, it’s like magic. But it’s not really magic.
And don’t bother asking that question: we aren’t telling you how the mind-reading trick worked. (How did we know you were going to ask that question? Sorry; we can’t tell you that, either.)
0 comments