Office Space: Tips and Tricks for Scripting Microsoft Office Applications

Welcome to Office Space, the new column that offers tips and tricks for scripting Microsoft® Office applications. We’ll post new tips every Tuesday and Thursday; to see an archive of previous tips, visit the Office Space Archive. And if you have particular questions about Microsoft Office scripting, feel free to send them to scripter@microsoft.com (in English, if possible). We can’t promise to answer all the questions we receive, but we’ll do our best.

Working with Other Worksheets in an Excel Workbook

By default, all new Excel workbooks, whether they’re created using a script or by selecting File – New from within Excel, come with three worksheets. If you’re manually working with Excel, switching from one worksheet to another is easy; you just click the appropriate worksheet tab. But how can you access and work with other worksheets from within a script?

Surprisingly enough, this is the programmatic equivalent of clicking the appropriate worksheet tab from within Excel. To explain, let’s first look at a rudimentary Excel script, one that creates a new workbook and then writes the letter A into cell A1 of the first worksheet (Sheet1):

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = TRUE
objExcel.Workbooks.Add

Set objWorksheet = objExcel.Worksheets(1)

objWorksheet.Cells(1, 1).Value = "A"

The secret to accessing other worksheets lies in this line of code, the line where we create a reference to the worksheet object:

Set objWorksheet = objExcel.Worksheets(1)

The number 1 in parentheses simply tells the script to work with the first worksheet in the workbook. What if we wanted to work with the second worksheet in the workbook (Sheet2)? Hey, no fair: you peeked at the answer! That’s right: all we have to do is reference worksheet number 2. Thus:

Set objWorksheet = objExcel.Worksheets(2)

In general, that’s all you need to do. However, we recommend that you toss in an additional line of code, one that makes the new worksheet the active worksheet. In fooling around a bit with Excel scripting, we found out that we could easily write data to a worksheet without making that the active worksheet. However, we occasionally encountered errors when trying to do some fancy formatting or something other than just writing data to a cell. Making worksheet 2 the active worksheet took care of those problems.

Here’s a fully-functional example, a script that adds a new workbook, creates an object reference to sheet 2, makes sheet 2 the active worksheet, and then writes the letter A to cell A1 on worksheet 2:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = TRUE
objExcel.Workbooks.Add

Set objWorksheet = objExcel.Worksheets(2)
objWorksheet.Activate

objWorksheet.Cells(1, 1).Value = "A"

To switch back to sheet 1, toss in these two lines of code:

Set objWorksheet = objExcel.Worksheets(1)
objWorksheet.Activate

The preceding example works just fine, but the code can be a little bit cryptic. After all, suppose you had 12 worksheets in a workbook, one for each month of the year. You could reference the August worksheet using code like this:

Set objWorksheet = objExcel.Worksheets(8)

That’s fine, but your script would be easier to read and easier to debug if you could refer to the worksheet by name rather than by number. This is especially true if, say, your worksheets are arranged by fiscal year rather than calendar year. If the fiscal year doesn’t match the calendar year, who the heck knows, off the top of their head, what month number is assigned to February when we’re talking fiscal year? Fortunately, you can refer to worksheets by number or by name; here’s a line of code that references a worksheet named August:

Set objWorksheet = objExcel.Worksheets("August")

Much nicer, huh? Just be sure to enclose the name in double quotation marks. If you want a generic sample you can try out on your own, here’s another script that adds a new workbook, creates an object reference to sheet 2, makes sheet 2 the active worksheet, and then writes the letter A to cell A1 on worksheet 2. The one difference here? The script references Sheet2 rather than worksheet number 2:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = TRUE
objExcel.Workbooks.Add

Set objWorksheet = objExcel.Worksheets("Sheet2")
objWorksheet.Activate

objWorksheet.Cells(1, 1).Value = "A"