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