Basic working with Excel workbooks with Python

Manipulating Excel with Python feels very much like manipulating Excel from within the VBA language. In fact, it uses the same objects, and the only major differences are that you don’t have access to autocompletion, and the syntax for function calls look a bit more ‘pythonic’.

As an example, this code out in the interpreter and watch Excel change with each command.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

import win32com
import win32com.client
xl = win32com.client.Dispatch("excel.application")
xl.visible=True#make the application visible
xl.WorkBooks.Add()#call the WorkBook collection, and tell it to Add a new one.
book = xl.ActiveWorkBook#store the current active workbook (the one we added) as book
book.ActiveSheet.Name="mySheet"#set the name of the active worksheet 'Sheet1' to 'mySheet'
book.Activesheet.Range("A1").value=100#put 100 into the cell "A1"#then we can close the book
book.close()#this will prompt Excel to ask you about saving the edited file.
xl.quit()#this will cause excel to quit out (prompting for saving as needed of course)del xl #we delete the xl object, allowing python to let go of the COM reference, allowing the Excel process to close finally.

As you can see, this looks 95% like VBA code, Range(“A1″) and so on, are all the same. If you need to do something, it’s possible to use the macro recorder, record the code, then translate it into python with a bit of modification. Then it should more or less work. The hardest part is figuring out exactly which objects you need to manipulate, and then how. When in doubt, read the object model documentation that comes with the macro editor.