Python Programming On Win32

Navigating Through Collections

The Excel object hierarchy basically goes Application, Workbook, Sheet, Range, Cell. A 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[1] <win32com.gen_py.Microsoft Excel ...