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.
7 comments:
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".
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.
worksheet1.Copy(nil, $workbook.Worksheets($workbook.Worksheets.Count))
With a nil as the first parameters then it's considered "After"
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.
Well, but how to copy/move worksheets between different workbooks?
@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))
Hi,
Please find my code below.
new_book = Spreadsheet.open '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.
Thanks
Shiva.A
Post a Comment