Many packages exist for working with Python and Excel, but xlwings may be the most powerful all-around due to its many reading and writing features. This versatility, however, comes at the cost of being relatively more difficult to set up.
For example, one of the most useful features of xlwings is the ability to write Python user-defined functions (UDFs) right from Excel. To do so, however, requires enabling the xlwings add-in. Here are the steps for PC:
Install Python & Excel
I suggest a desktop version of Excel for 365 and the Anaconda distribution of Python (Download for free here. Follow all suggested prompts.).
Trust access to VBA project object model
This will make sure that Excel and Python can fully communicate via the VBA object model.
From File, go to Options > Trust Center > Trust Center Settings:
Next, head to Macro Settings, then under Developer Macro Settings make sure “Trust access to the VBA project object model” is checked on:
Install the xlwings add-in for Excel
This can be done by launching Anaconda Prompt. Type xlwings addin install into the command line, then hit Enter. If successful, you should see a message like the following (version numbers may vary):
Open Excel & confirm access
Once you’ve installed the add-in, open Excel. Do you see a new tab in the ribbon called xlwings? Success! You now have one more tool for making the most of Excel with Python.
If you do not see this tab on your ribbon, make sure you’ve fully restarted Excel. Check the xlwings documentation for other potential blockers.
Making Excel fly
It’s already amazing how much Excel is capable. Add Python to the mix and you’ll be an unstoppable analyze. Be sure to check the blog for other posts on combining these tools.
Leave a Reply