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
To save a new workbook, call its SaveAs method, supplying a filename:
To save changes to a previously-saved workbook, just call its Save method:
To close a workbook after saving changes:
Next, we'll move down to the next logical level and work with the Excel Worksheet object.