{"id":69703,"date":"2005-05-31T14:35:00","date_gmt":"2005-05-31T14:35:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2005\/05\/31\/how-can-i-open-an-excel-workbook-and-retrieve-the-names-of-all-the-worksheets\/"},"modified":"2005-05-31T14:35:00","modified_gmt":"2005-05-31T14:35:00","slug":"how-can-i-open-an-excel-workbook-and-retrieve-the-names-of-all-the-worksheets","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-open-an-excel-workbook-and-retrieve-the-names-of-all-the-worksheets\/","title":{"rendered":"How Can I Open an Excel Workbook and Retrieve the Names of All the Worksheets?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! How can I open an Excel workbook and retrieve the names of all the worksheets?

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

Hey, JG. It\u2019s the first day back after a three-day weekend, and this morning all the Scripting Guys stumbled into the office and just stared blankly into space. (All in all, pretty much business as usual.) Eventually one of us spoke up and said, \u201cDidn\u2019t we used to do some kind of daily question-and-answer column or something?\u201d After much debate we realized that, yes, not only did we used<\/I> to do this column, we still do<\/I> this column. That meant we had to find a useful scripting question and provide an answer for it, hopefully without having to do much work. <\/P>\n

To tell you the truth, JG, your question was a lifesaver: it\u2019s a useful scripting task, it\u2019s something a number of people have asked about, and, best of all, answering this question required very little effort on our part. You wanted a script that could open an Excel workbook and return the names of all the worksheets? All we had to do was toss this little baby together:<\/P>

Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True\nSet objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)<\/p>\n
For Each objWorksheet in objWorkbook.Worksheets\n    Wscript.Echo objWorksheet.Name\nNext\n<\/PRE>\n
Nothing to it, right? We start off by creating an instance of the Excel.Application<\/B> object; we then set the Visible<\/B> property to true, to watch the excitement unfold. We then use the Open<\/B> method to open the file C:\\Scripts\\Test.xls.<\/P>\n
That\u2019s pretty easy, but getting the names of all the worksheets is even easier. That\u2019s because the Workbook object (which corresponds to a .xls file) includes a property named Worksheets<\/B>; this property consists of a collection of all the worksheets found in the workbook. To determine the names of all the worksheets we merely set up a For Each loop that cycles through the worksheets collection and echoes back the name of each sheet. That\u2019s what we do here:<\/P>
For Each objWorksheet in objWorkbook.Worksheets\n    Wscript.Echo objWorksheet.Name\nNext\n<\/PRE>\n
That\u2019s it. You can now easily get the names of all the worksheets in a workbook, and we can go back to staring blankly into space, mentally calculating the number of days between now and July 4th<\/SUP>, our next scheduled three-day weekend. In the meantime, you might want to check out the Office Space<\/B><\/A> column for even more ways to script Microsoft Office.<\/P>\n
P.S.<\/B> Here\u2019s a one line script that will tell you the number of days between now and the Fourth of July:<\/P>
Wscript.Echo DateDiff(“d”, Date, #7\/4\/2005#)\n<\/PRE>\n
No need to thank us; this happens to be one script we Scripting Guys actually use ourselves.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I open an Excel workbook and retrieve the names of all the worksheets?— JG Hey, JG. It\u2019s the first day back after a three-day weekend, and this morning all the Scripting Guys stumbled into the office and just stared blankly into space. (All in all, pretty much business as usual.) […]<\/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-69703","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 open an Excel workbook and retrieve the names of all the worksheets?— JG Hey, JG. It\u2019s the first day back after a three-day weekend, and this morning all the Scripting Guys stumbled into the office and just stared blankly into space. (All in all, pretty much business as usual.) […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/69703","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=69703"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/69703\/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=69703"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=69703"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=69703"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}