Navigating Through Collections
The Excel object hierarchy basically
Range is an arbitrary
region on a
Sheet. You can assign variables to the
various items in the hierarchy or drill down in one long statement.
If you want to modify the top left cell of Sheet1 in our new
workbook, you can get to it in any of the following ways:
>>> xlApp.ActiveSheet.Cells(1,1).Value = 'Python Rules!' >>> xlApp.ActiveWorkbook.ActiveSheet.Cells(1,1).Value = 'Python Rules!' >>> xlApp.Workbooks("Book1").Sheets("Sheet1").Cells(1,1).Value = "Python Rules!" >>> xlApp.Workbooks(1).Sheets(1).Cells(1,1).Value = "Python Rules!" >>> xlApp.Workbooks(1).Sheets(1).Cells(1,1).Value = "Python Rules!" >>> >>> xlBook = xlApp.Workbooks(1) >>> xlSheet = xlApp.Sheets(1) >>> xlSheet.Cells(1,1).Value = "Python Rules!" >>>
We recommend getting a reference to the sheet you want and working with that.
The last few examples illustrate a common feature of Microsoft object models—their dependence on collections. A collection can be viewed as a cross between a list and a dictionary; it can be accessed by a numeric index or a named string key. Python allows you to access items via numeric indexes with both a function-call and an array syntax: in other words, with parentheses or square brackets. If you are using a string key, you must use parentheses:
>>> xlBook.Sheets(1) <win32com.gen_py.Microsoft Excel 8.0 Object Library._Worksheet> >>> xlBook.Sheets <win32com.gen_py.Microsoft Excel ...