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...
end

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':

workbook.Worksheets('Sheet1').Move(workbook.Worksheets('Sheet3'))

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

workbook.Worksheets(3).Move(workbook.Worksheets(1))

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

workbook.Worksheets(3).Copy(workbook.Worksheets(1))

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

worksheet.PrintOut

To delete a worksheet, call its Delete method:

worksheet.Delete

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.