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?

1
10
12/1/2015 10:06:54 PM

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:\Python25\lib\site-packages\win32comext\axscript\client

  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 alt text
  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

17
8/25/2010 8:48:30 PM

Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon