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.

Renaming an Excel Worksheet

In a previous column we noted that you can reference (and thus work with) any worksheet within an Excel workbook simply be specifying the name of that worksheet. For example, here’s a simple little script that types some text in cell A1 of the worksheet named Sheet2:

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

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

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

Brings back memories, doesn’t it? What we didn’t mention, however, is that you can also rename a worksheet using a script. Here’s a script that binds to Sheet2 and then renames the worksheet March 2005 Inventory Report:

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

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

objWorksheet.Name = "March 2005 Inventory Report"

We start off by creating an instance of Microsoft Excel and setting the Visible property to TRUE; that simply allows you to watch as the script does its thing. We create a new workbook, and then bind to Sheet2 and make that worksheet the active worksheet.

At that point it takes just one line of code to rename the worksheet. All we have to do is set the Name property to the desired name:

objWorksheet.Name = "March 2005 Inventory Report"