CODEWITHSUNDEEP
pythonexcelvba
Craig Arnall
Craig Arnall

Reputation: 21

How can I run Python commands from within Excel VBA?

I'm struggling to access Python from Excel VBA (which I'm familiar with). There was a very useful answer here (reproduced below). I followed the steps very carefully - the same error occurs whether I'm using Python 3.3 or Python 2.7.

When I use the Command Button to execute the VBA code, it fails at this statement: PyScript.Language = "python" with error message "Run time error 380: A script engine for the specified language cannot be created"

Any ideas please?

The answer from stackoverflow that I'm trying to use follows.

Follow these steps carefully

  1. Go to Activestate and get [ActivePython 2.5.7][1] 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][2]
  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

Upvotes: 2

Views: 3199

Answers (1)

Doug Jenkins
Doug Jenkins

Reputation: 94

I'm running 32 bit Excel 2010 and Anaconda Python 2.7. I followed the procedure exactly as stated, except to keep things simple I didn't create the user form or button, and removed the "WithEvents" from the dim statement, and made the sub Public (see below). Running the macro from the worksheet with Alt-F8, it works with no problem.

I have also tested it in Excel 2013, and had no problem.

Public Sub TestPyScript()
Dim PyScript As MSScriptControl.ScriptControl
   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

Upvotes: 3

Related Questions