Calling python script from excel/vba

Question

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?

Answer 1

Follow these steps carefully

1. Go to Activestate and get ActivePython 2.5.7 MSI installer.
   I had DLL hell problems with 2.6.x
2. Install in your Windows machine

3. once install is complete open Command Prompt and go to

   C:Python25libsite-packageswin32comextaxscriptclient

4. execute > python pyscript.py you should see message Registered: Python

5. Go to ms office excel and open worksheet

6. Go to Tools > Macros > Visual Basic Editor
7. Add a reference to the Microsoft Script control
8. Add a new User Form. In the UserForm add a CommandButton

9. 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