Saturday, March 31, 2007

Automating Excel with Ruby: The Workbook Object

Previously, we looked at the Excel Application object. Today, we'll take it down a level and investigate the Workbooks collection and Workbook objects.

The Excel application object contains a (1-based index) collection of all open workbooks. You could think of the excel.Workbooks() statement as a method which returns the collection of Workbooks (if called with no argument) or a single Workbook (if called with an index or name argument).

To create a new workbook, call the Add method of the Workbooks collection:


workbook = excel.Workbooks.Add

To open an existing workbook, call the Open method of the Workbooks collection and pass it the filename of the workbook:

workbook = excel.Workbooks.Open('c:\temp\MyWorkbook.xls')

You can reference a single workbook (that's already open) by index:

workbook = excel.Workbooks(1)

...or by filename:

workbook = excel.Workbooks('c:\temp\MyWorkbook.xls')

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

workbook = excel.ActiveWorkbook

You can, of course, iterate over the Workbooks collection:

for workbook in excel.Workbooks
# ...code...
end

To save a new workbook, call its SaveAs method, supplying a filename:

workbook.SaveAs('c:\temp\YourWorkbook.xls')

To save changes to a previously-saved workbook, just call its Save method:

workbook.Save

To close a workbook after saving changes:

workbook.Close

Next, we'll move down to the next logical level and work with the Excel Worksheet object.

7 comments:

Walker said...

Is there a method that returns the index of a given workbook?

David Mullet said...

walker-

I'm not aware of such a method/property. You could, however, iterate over the excel.Workbooks collection and compare the Workbook.Name value. For example:

def get_index_from_name(wb_name)
i = 0
for workbook in excel.Workbooks
i = i + 1
if workbook.Name == [value]
return i
end
end
end

index = get_index_from_name('workbook.xls')

There's probably more elegant code to do this, but you get the idea.

Keep in mind that there could be hidden workbooks in the collection, such as the PERSONAL.XLS workbook that is normally used to store macros.

-David

Walker said...

My google searching has also returned no method that does this. There is an .Index method on most objects, but not one for Workbook. I think your solution would be best.

Anonymous said...

What about closing without saving changes? Any way to do that without it popping up the prompt to save?

David Mullet said...

"What about closing without saving changes?"

workbook.Close(false)

Also, you can turn off alerts by setting the Application object's DisplayAlerts property...

excel.DisplayAlerts = false

David

Unknown said...

One caveat of the following line:
workbook.SaveAs('c:\temp\YourWorkbook.xls')
Notice that you use single quotes so that you can use the \ between directories. If, however you need variable substitution in your filename, you must use / (unix-like) between your directory
entries.(i.eworkbook.SaveAs("c:/temp/YourWorkbook#{Date.today.to_s}.xls")
I spent quite a bit of time with this, thinking that I spelled the
name SaveAS wrong. The error (at least in my case) was method_missing from Win32::OLE, which was very misleading.

David Mullet said...

@Brian:

That's a good point. I usually do use single-quotes, to allow me to use the standard backslash separator ('\'). When using double-quotes, you'll need to either switch to the forward slash ("/") or escape the backslash with another backslash ("\\").

David