32

I've recently taken it as a project to teach myself how to program in Python. Overall, I must say that I'm impressed with it.

In the past I've typically stuck to programming in VBA mostly for MS Excel (but also a bit in MS Access and Word) and have struggled to find ways to make it do things that Python can easily do with a single command.

I was wondering if there were a reasonable way to harness the programming power and ease of Python while at the same time make use of the various tools in Office (mostly Excel)?

ZygD
  • 22,092
  • 39
  • 79
  • 102
Jesse
  • 383
  • 1
  • 3
  • 9
  • 1
    I always wondered if it was possible to do list/dicts to rows/columns or include numpy and excel, or perhaps pyqt + numpy as an excel addin – dassouki Jan 26 '10 at 19:26
  • See http://stackoverflow.com/questions/441758/driving-excel-from-python-in-windows/445961#445961 – codeape Jan 26 '10 at 19:46
  • This is all great information, it definitely gives me a direction (actually more then one) to go in. I couldn't find any similar answers elsewhere on SO, but obviously was not using the right keywords. – Jesse Jan 27 '10 at 05:55
  • It looks like [pyxll](http://www.pyxll.com/docs/macros.html) can create and call macros. – Emmanuel May 24 '12 at 16:38
  • 1
    Have a look at this book, "Python Programming On Win32". It really tells you everything you need to know. And it's a good book. http://oreilly.com/catalog/9781565926219 – Erik A. Brandstadmoen Jan 26 '10 at 22:16
  • Sample chapter: http://oreilly.com/catalog/pythonwin32/chapter/ch12.html – Craig McQueen Jan 27 '10 at 03:11
  • See answers in http://stackoverflow.com/questions/3567365/calling-python-script-from-excel-vba/15917398#15917398 – denfromufa Oct 12 '13 at 08:31

6 Answers6

24

Yes, absolutely. You want to use win32com module, which is part of pywin32 (get it here).

I've found you can really simplify Python integration by writing a macro in VBA for Python to use, and then just have Python call the macro. It will look something like this:

from win32com.client import Dispatch as comDispatch

xl = comDispatch('Excel.Application')
xl.Workbooks.Open("Macros.xls", False, True)
xl.Run("Macros.xls!Macro_1")

I'm sure there are plently of examples on SO... Like this one.

Community
  • 1
  • 1
Jason Coon
  • 17,601
  • 10
  • 42
  • 50
10

There is a set of cross platform Python utilities - called xlrd, xlwt, and xlutils - for reading & writing Excel files. There are some limitations (e.g. I don't think they can process macros), but they do allow you to work with Excel files on non-Windows platforms, if that's of use to you. See: http://www.python-excel.org/

Also, there are SO questions already dealing with this sort of topic, including this: Is there a better way (besides COM) to remote-control Excel?

Community
  • 1
  • 1
GreenMatt
  • 18,244
  • 7
  • 53
  • 79
7

Or have a look at IronPython. IPy is a native .NET implementation of Python 2.6, you can find it at http://www.codeplex.com/ironpython.

We have used it for several projects. You can use it "from the outside" using COM or - as we do - write a Excel AddIn with a ScriptHost, which calls out to IronPython code giving you an environment similar to VBA.

Being a .NET dll, IPy integrates extremely well into the modern Windows .NET stack.

raindog
  • 91
  • 3
  • I also think this is actually the best solution. If you go this route, you can download the [Visual Studio Shell(integrated)](http://www.microsoft.com/downloads/en/details.aspx?FamilyID=8e5aa7b6-8436-43f0-b778-00c3bca733d3&displaylang=en) for free and then download the [iron python tools](http://ironpython.net/tools/) for visual studio. – oob Mar 05 '11 at 21:07
  • I know this is dated, but could you please provide some examples? – jhexp Feb 08 '13 at 23:20
5

The xlrd, xlwt, and xlutils packages mentioned above can only read and write .xls files which have size limitations of 65,000 rows and 256 columns. Aside from that it is a good tool.

But I have moved on to another python-excel package, OpenPyXL, which can read and write .xlsx files. Also I find it easy to use and the documentation is good.

OpenPyXL: http://packages.python.org/openpyxl/index.html

sequoia
  • 3,025
  • 8
  • 33
  • 41
3

here is a useful link:

http://continuum.io/using-excel

  1. Try Pyvot – A Python to/from Excel Connector from Microsoft:

http://pytools.codeplex.com/wikipage?title=Pyvot

I also really like PTVS from the same dev. team, which provides best debugging in Python that I experienced so far.

2.

What you can do with VBA + Python is following:

Compile your py scripts that take inputs and generate outputs as text files or from console. Then VBA will prepare input for py, call the pre-compiled py script and read back its output.

3.

Consider OpenOffice or LibreOffice which support Python scripts.

This is assuming that available options with COM or MS script interfaces do not satisfy your needs.

4.

This is not free approach, but worth mentioning (featured in Forbes and New York Times):

https://datanitro.com

5.

This is not free for commercial use:

PyXLL - Excel addin that enables functions written in Python to be called in Excel.

denfromufa
  • 5,610
  • 13
  • 81
  • 138
3

This is a question from a long time ago, but I'm putting it here so others can point to it in their search.

One option not discussed here, which I use all the time, is to create a COM server from Python and call it from VBA in any Office application. There is a good tutorial of doing COM server with Python at: http://timgolden.me.uk/pywin32-docs/html/com/win32com/HTML/QuickStartServerCom.html

What you end up with is a COM server (don't forget to make it In Process) that can be created by a call to CreateObject() in VBA. You then call the methods on it as you do with an COM object created with CreateObject(). You can't single step in your Python script, but you can intercept logging with win32traceutil from Pywin32 distribution. Works flawlessly.

ZygD
  • 22,092
  • 39
  • 79
  • 102
user7969
  • 41
  • 2