I'd like to be able to write functions in python to interface with PSSE and then call them from a VBA macro in MS Excel. Has anyone attempted this? Perhaps it could be done by writing a python class and compiling it to some form that can be imported into VBA, just a guess.
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?
You have a couple options gluing your python scripts to your MSOffice VBA:
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
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.
Helpful functions on the VBA side to call external programs:
Thanks for the info, very useful. I already have a VBA Excel GUI developed for handling some power flow related data. I'd like to expand the capabilities of the VBA program by calling up python written PSSE interface to get data from cases or even update cases with data from Excel. more...
This may also provide a way around my issue with not being able to reliably stop or halt PSSE within a python script. Ideally, I would have VBA call one script to read in dyre data, save a snapshot, and compile usermodel code, then call another script to bring up PSSE with the compiled DLL. V32:(
@chip this idea is great "Instead treat your Python program as a separate tool that does one thing, and does it well." - When scripts are simplified down and given a laser focus like this - the bugs that you get will be less complicated, and development time is much shorter.
Comments
Is there anything stopping you from using ``shell()`` to call the python script (compiled as an .exe if needed) and use command-line args + fileio to move data back and forth?
I saw a startup that swapped VBA for python in the macros: https://datanitro.com/, the commercial packages aren't cheap though. Apart from that, I'd follow chip's idea of using `shell` to make system calls. If you can find a more elegant solution I'd love to hear about it.
One final idea, Python can call VBA macros. So you could do the reverse. Call the macro from Python. Of course - that is the exact opposite of what you asked :)
I have not used shell() before but I'll definitely give it a try. It sounds a bit clunky and maybe slow using fileio if there is to be a lot of data moving back and forth. I'm still curious if there is a way to instantiate python-written classes directly in VBA or if they are incompatible. Thanks