How Can I Open an Excel Workbook and Retrieve the Names of All the Worksheets?

ScriptingGuy1

Hey, Scripting Guy! Question

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

— JG

SpacerHey, Scripting Guy! AnswerScript Center

Hey, JG. It’s 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, “Didn’t we used to do some kind of daily question-and-answer column or something?” After much debate we realized that, yes, not only did we used to do this column, we still do 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.

To tell you the truth, JG, your question was a lifesaver: it’s a useful scripting task, it’s 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:

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)

For Each objWorksheet in objWorkbook.Worksheets Wscript.Echo objWorksheet.Name Next

Nothing to it, right? We start off by creating an instance of the Excel.Application object; we then set the Visible property to true, to watch the excitement unfold. We then use the Open method to open the file C:\Scripts\Test.xls.

That’s pretty easy, but getting the names of all the worksheets is even easier. That’s because the Workbook object (which corresponds to a .xls file) includes a property named Worksheets; 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’s what we do here:

For Each objWorksheet in objWorkbook.Worksheets
    Wscript.Echo objWorksheet.Name
Next

That’s 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, our next scheduled three-day weekend. In the meantime, you might want to check out the Office Space column for even more ways to script Microsoft Office.

P.S. Here’s a one line script that will tell you the number of days between now and the Fourth of July:

Wscript.Echo DateDiff(“d”, Date, #7/4/2005#)

No need to thank us; this happens to be one script we Scripting Guys actually use ourselves.

0 comments

Discussion is closed.

Feedback usabilla icon