Calling python script from excel/vba
excel, python, vba
Solution
Follow these steps carefully
- Go to Activestate and get ActivePython 2.5.7 MSI installer. I had DLL hell problems with 2.6.x
- Install in your Windows machine
once install is complete open Command Prompt and go to
C:\Python25\lib\site-packages\win32comext\axscript\client
execute `\> python pyscript.py` you should see message Registered: Python
Go to ms office excel and open worksheet
- Go to Tools > Macros > Visual Basic Editor
- Add a reference to the Microsoft Script control
- Add a new User Form. In the UserForm add a CommandButton
Switch to the code editor and Insert the following code
Dim WithEvents PyScript As MSScriptControl.ScriptControl
Private Sub CommandButton1_Click()
If PyScript Is Nothing Then
Set PyScript = New MSScriptControl.ScriptControl
PyScript.Language = "python"
PyScript.AddObject "Sheet", Workbooks(1).Sheets(1)
PyScript.AllowUI = True
End If
PyScript.ExecuteStatement "Sheet.cells(1,1).value='Hello'"
End Sub
Execute. Enjoy and expand as necessary
Problem
I have a python code that reads 3 arguments (scalars) and a text files and then returns me a vector of double. I want to write a macro in vba to call this python code and write the results in one of the same excel sheet. I wanted to know what was the easiest way to do it, here are some stuffs that I found: call the shell() function in vba but it doesn't seem so easy to get the return value. register the python code as a COM object and call it from vba--> i don't know how to do that so if you have some examples it would be more than welcome create a custom tool in a custom toolbox, in vba create a geoprocessing object and then addtoolbox and then we can use the custom tool directly via the geoprocessing object but this is something as well that I don't know how to do.. Any tips?