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:
Is there a method that returns the index of a given workbook?
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
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.
What about closing without saving changes? Any way to do that without it popping up the prompt to save?
"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
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.
@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
Post a Comment