Sunday, April 1, 2007

Automating Excel with Ruby: The Worksheet Object

Yesterday, we looked at the Excel Workbook object. Today, we'll work with the Worksheets collection and Worksheet objects.

The Workbook object contains a (1-based index) collection of all open worksheets. You could think of the workbook.Worksheets() statement as a method which returns the collection of Worksheets (if called with no argument) or a single Worksheet (if called with an index or name argument).

To create a new worksheet, call the Add method of the Worksheets collection:

worksheet = workbook.Worksheets.Add

You can reference a single worksheet by index:

worksheet = workbook.Worksheets(1)

...or by name:

worksheet = workbook.Worksheets('Sheet1')

... or you can reference the currently active (selected) Worksheet:

worksheet = workbook.ActiveSheet

The Worksheets collection is not like a standard Ruby array. It's has a 1-based index, has no Size property, and does not recognize the '-1' index value. But it has a Count property in lieu of the Size property, so to reference the last worksheet in the collection, you could do this:

worksheet = workbook.Worksheets(workbook.Worksheets.Count)

You can, however, iterate over the Worksheets collections:

for worksheet in workbook.Worksheets
# ...code...

Note that the Worksheets collections may contain hidden worksheets. To hide or unhide a Worksheet, set its Visible property:

worksheet.Visible = false
workbook.Worksheets(1).Visible = true

You can get or set the name of the worksheet:

worksheet.Name = 'Amish Computer Scientists'

Note that the worksheet name cannot exceed 31 characters, and cannot include these characters: \ / ? : * [ ]

To move a worksheet, call its Move method, passing the Worksheet object that you want to move this worksheet in front of. For example, to move 'Sheet1' to the slot before 'Sheet3':


...or to move the third worksheet to the first slot:


To copy a worksheet, use the Copy command as you would the Move command:


To print a worksheet, call the Worksheet object's PrintOut method:


To delete a worksheet, call its Delete method:


This will normally result in an Alert box being displayed, asking you to confirm the deletion. To disable such alerts, set the Excel Application object's DisplayAlerts property:

excel.DisplayAlerts = false

That's all for now. Soon we'll look at rows, columns, and cells. As always, feel free to leave a comment or send me email if there are specific items you would like to see discussed here.


Walker said...

In order to learn about scripting Excel, I've been recording macros and trying to translate them into methods for ruby - as you recommended. Your post gives an answer to my question, but I would still like to ask it. When moving a worksheet, excel creates this macro:

Sheets("Sheet2").Move After:=Sheets(2)

How do you translate the parameter after .Move (After:=, or Before:=) into a ruby command?

It looks like .Move in ruby defaults to "before".

David Mullet said...

Good question! As you said, Walker, the Move method defaults to 'Before'...


But I am unaware of how to specify the 'After' parameter. You could use the methods above to move a worksheet to any position but the last. In that case, you may have to move a worksheet Before the last worksheet, then move the last worksheet Before the next-to-last worksheet.

Plumm said...

worksheet1.Copy(nil, $workbook.Worksheets($workbook.Worksheets.Count))

With a nil as the first parameters then it's considered "After"

mez said...

To move sheet "foo" to the end:

excel.sheets("foo").move({'after' => excel.sheets(excel.sheets.count)})

I think it returns true if it's a success.

Anonymous said...

Well, but how to copy/move worksheets between different workbooks?

David Mullet said...


To move the worksheet between workbooks, use the same method, but pass it a worksheet object from the second workbook. Example:

require 'win32ole'

xl = WIN32OLE.connect('Excel.Application')
wb1 = xl.Workbooks('Book1.xls')
wb2 = xl.Workbooks('Book2.xls')

shivareddy said...


Please find my code below.

new_book = 'sample.xls'

worksheet = new_book.worksheets

have 3 sheets(sheet1,sheet2,sheet3)
I want to add sheet4 to new_book.

How can do can you help me.

Thanks in Advance.