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

6 comments:

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

excel.Sheets(3).Move(excel.Sheets(1))
workbook.Workheets(3).Move(workbook.Worksheets(1))

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

@Anonymous:

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')
wb1.Worksheets(1).Move(wb2.Worksheets(1))