Detroit Coder

Coding in the D and other interesting things

Video: Call Python from Excel Formulas

In this post I am going to quickly explain how to create Excel formulas that call class methods that are written completely in Python. I will be using the rottenTom class created in my previous post to create some Excel formulas such as ‘=getyear(“space jam”)’ and have it return 1996 in the Cell.

To do this I use pywin32 and register my rottenTom class as a COM server. COM is Microsoft’s way of exposing interfaces for code that are language agnostic. This allows you to create a Python class and instantiate it within VBA, as we will see in this example. If you have not worked with COM before, Activestate has a good overview of setting up a COM server and Mark Hammond (the guy you thank at the end of every Python install on Windows) has an old but still very pertinent book on Python on Windows. Chapter 12 is specifically dedicated to Python and COM.

About these ads

8 comments on “Video: Call Python from Excel Formulas

  1. robert
    November 28, 2013

    excellent job here. thanks
    Can you also put up one about calling Python code from VBA

    • Michael David Watson
      November 29, 2013

      Absolutely, is there anything in particular that you would be most interested in if I make a post just about calling Python from VBA?

  2. Bryan Hee
    October 31, 2014

    Almost there, but when I try “import rottentom” I get an error message saying there is no module named rottentom. and when i try to register it via the cmd prompt window I am getting an error message telling me that the file I am looking for doesn’t exist. So I am wondering where I should save these .py files. Thank you so much, you are a lifesaver and truly awesome!

    • Michael David Watson
      October 31, 2014

      You actually shouldn’t need to import rottentom anywhere. Once you modify the RottenTom class by adding in the _reg_clsid_ and _public_methods_ attributes you can register the class with the following call

      win32com.server.register.UseCommandLine(rottenTom). Feel free to message me back if you can’t get it to work. Also you can save the .py file anywhere on your computer.

      • Bryan Hee
        October 31, 2014

        so when I try to run the updated script, it is getting hung up on import urllib2 which is saying that there is no module named urllib2. Sorry I am pretty new to this so my expertise is very minimal.

      • Michael David Watson
        October 31, 2014

        Bryan,

        You must be using Python3. You need to change the code to import urllib.request instead: http://stackoverflow.com/questions/2792650/python3-error-import-error-no-module-name-urllib

      • Bryan Hee
        October 31, 2014

        Okay now I am getting an error message saying DLL load failed: %1 is not a valid Win32 application

  3. Bryan Hee
    November 5, 2014

    Hey so the last issue was a compatibility issue between 64 bit and 32 bit programs, as I’m sure you knew. However I am now running into an issue that I believe is just a diction issue. the following error message is what I am getting when I try to set tom = rottentom.rottenTom().

    NameError: name ‘setproxy’ is not defined

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Information

This entry was posted on November 26, 2013 by .
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: