Revision history [back]
 | 1 | initial version |
Calling python from VBA-land
You asked about calling python from a macro, but anything that's in VBA you should be able to get to from a macro or provide a functional equivalent.
Using win32com you can implement a COM object and through some machinations add this as a reference to your VBA code. Even thinking about this is a bit painful for me.
Instead treat your python program as a separate tool that does one thing, and does it well. The excel spreadsheet is a easy-to-use front-end for you python script. Maybe one day you'll want to grab data directly from a PSSE run and shove it into your script and just skip excel?
Moving data around
You have a couple options gluing your python scripts to your MSOffice VBA:
- Pass data in on the command-line invocation of your script
- Temporary files
- STDIO pipes
From past investigations, STDIO is too much of a pain to use in VBA so we can ignore that option.
Options 1 and 2 are good ways to get data into your script. For example if you want to run some python math on data in excel you might invoke your python program like this
uber_math.py "C:\TEMP\excel_data.csv" "C:\TEMP\excel_prime.csv"
I like to use standard windows environment variables like %TEMP% so the VBA will work anywhere. If you haven't yet, spend some time learning about argparse
For other users
If you are the only one using your VBA-python spreadsheet, then you can invoke the python script with python. You may also be able to get away with this if everyone you are working with has a similar environment.
If your spreadsheet users don't have python installed you can build your python script as an executable and place it in a shared location.
Reference
Helpful functions on the VBA side to call external programs:
