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..
Follow these steps carefully
once install is complete open Command Prompt and go to
\> python pyscript.py
you should see message Registered: Python
Go to ms office excel and open worksheet
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