{"id":66863,"date":"2006-07-20T15:04:00","date_gmt":"2006-07-20T15:04:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2006\/07\/20\/how-can-i-tell-if-a-specified-worksheet-exists-in-an-excel-workbook\/"},"modified":"2006-07-20T15:04:00","modified_gmt":"2006-07-20T15:04:00","slug":"how-can-i-tell-if-a-specified-worksheet-exists-in-an-excel-workbook","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-tell-if-a-specified-worksheet-exists-in-an-excel-workbook\/","title":{"rendered":"How Can I Tell If a Specified Worksheet Exists in an Excel Workbook?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! How can I tell if a specified worksheet exists in an Excel workbook?

— MVK<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

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: \u201cThe Smartest Dogs in the World!\u201d 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.<\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. Sure, that sounds<\/I> impressive. But, to tell you the truth, the questions were pretty easy; they were all questions like \u201cWho was the first President of the United States?\u201d Come on; even a cat<\/I> could get that one right.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

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\u2019s mind. One hundred percent of the time<\/I> 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. <\/P>\n

The point: after mulling this over a bit, we\u2019ve decided that psychic powers are the best way to determine whether a specified worksheet exists in an Excel spreadsheet.<\/P>\n

What\u2019s that? No, we aren\u2019t going to tell you how the trick worked, even if you do<\/I> promise not to tell anyone else. (What, you don\u2019t think we\u2019ve heard that<\/I> 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:<\/P>

Set objExcel = CreateObject(“Excel.Application”)\nSet objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)<\/p>\n
x = 0<\/p>\n
For Each objWorksheet in objWorkbook.Worksheets\n    If objWorksheet.Name = “Budget” Then\n        x = 1\n        Exit For\n    End If\nNext<\/p>\n
objExcel.Quit<\/p>\n
If x = 1 Then\n    Wscript.Echo “The specified worksheet was found.”\nElse\n    Wscript.Echo “The specified worksheet was not found.”\nEnd If\n<\/PRE>\n
Granted, this script probably won\u2019t get you on any variety shows, but – never mind, forget we said that. After all, if the guy with The World\u2019s Smartest Dogs can get on a variety show, well who knows what you could do with this?<\/P>\n
As you probably already guessed, there isn\u2019t much magic to the script. We start out by creating an instance of the Excel.Application<\/B> object, then use the Open<\/B> method to open the file C:\\Scripts\\Test.xls.
<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. Isn\u2019t this about the time we usually set the Visible<\/B> 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<\/I>), and then close Excel, all without anyone knowing what we were doing or how we were doing it. (No, that\u2019s not magic: after all, when you\u2019re a Scripting Guy no one ever<\/I> knows what you\u2019re doing, let alone how – or why – you\u2019re doing it.)<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
We then have this seemingly-random little line of code:<\/P>
x = 0\n<\/PRE>\n
Why are we, totally out of the blue, assigning the value 0 to a variable named x? We don\u2019t want to spoil the suspense, so hang tight; we\u2019ll explain why in a minute.<\/P>\n
Promise.<\/P>\n
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\u2019s what this line of code is for:<\/P>
For Each objWorksheet in objWorkbook.Worksheets\n<\/PRE>\n
Inside that For Each loop we simply check each worksheet to see if the sheet has a Name<\/B> equal to Budget<\/I>. If it doesn\u2019t, we skip past the worksheet and check the next item in the collection. If the Name is<\/I> equal to Budget, we then assign x the value 1:<\/P>
x = 1\n<\/PRE>\n
Now you can probably see what we\u2019re doing here: we\u2019re just using x as a simple way to track whether or not we\u2019ve found a worksheet named Budget. This variable begins life with a value of 0; that value will never change \u2026 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\u2019t found. But if x is 1 \u2026.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. 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\u2019s a good little trick to have in your repertoire.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
And one other thing: if a worksheet named Budget is found we also execute the Exit For<\/B> statement in order to exit our For Each loop. After all, once we find a worksheet named Budget we don\u2019t need to look through all the other worksheets in the collection; we\u2019ve already found what we were looking for. So why not exit the loop and get on with things?<\/P>\n
Good question: what does<\/I> it mean to \u201cget on with things,\u201d at least in this script? Well, to begin with, we use this line of code to close Excel:<\/P>
objExcel.Quit\n<\/PRE>\n
And then we use this block of code to examine the value if x and report back whether or not the worksheet was found:<\/P>
If x = 1 Then\n    Wscript.Echo “The specified worksheet was found.”\nElse\n    Wscript.Echo “The specified worksheet was not found.”\nEnd If\n<\/PRE>\n
Yes, it\u2019s like magic. But it\u2019s not really magic.<\/P>\n
And don\u2019t bother asking that<\/I> question: we aren\u2019t telling you how the mind-reading trick worked. (How did we know you were going to ask that question? Sorry; we can\u2019t tell you that, either.)<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/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-66863","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 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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66863","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=66863"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66863\/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=66863"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=66863"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=66863"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}