Saturday, May 26, 2018

How to use Python in Excel

Here is a simple way to use Python in Excel which most would like since Python is a very quick and easy way to get things done.

Pre-requisite:

MS Excel
Excel Python
Windows extension for python

There are many tutorial which guides you to how this can be done. Please refer to the link in the reference.

After installation, you may encounter a bunch of issues, please refer to the troubleshooting page, I managed resolve everything from information found in this page.

You will see the Excel Python Tab available in Excel:


There are only 2 buttons:

1) Setup ExcelPython: Gets you started by creating a Python file and link it to your current workbook
2) Import Python UDF: Extract Python functions you created to Excel so you can use them directly without writing any VBA.

Click the first button to setup things. You will find a <worksheetfilename>.py file created in the same folder. What you have to do is to code functions in that Python file. E.g.

Workbook name: //path/Book1.xlsm
Associated Python file: //path/Book1.py

Next,click on the 2nd button to import the functions you have written and you are done!
Now you just need to use the same function name to execute your python functions.

Please find the attached example here.

Reference:
https://github.com/ericremoreynolds/excelpython/blob/master/docs/tutorials/Addin01.md