O'Reilly logo

Python Programming On Win32 by Mark Hammond, Andy Robinson

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required