Calling python script from excel/vba

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

Do you have to call the Python code as a macro? You could use COM hooks within the Python script to direct Excel and avoid having to use another language:

import win32com.client# Start ExcelxlApp = win32com.client.Dispatch( "Excel.Application" )workbook = xlApp.Workbooks.Open( <some-file> )sheet = workbook.Sheets( <some-sheet> )sheet.Activate( )# Get valuesspam = sheet.Cells( 1, 1 ).Value# Process values...# Write valuessheet.Cells( ..., ... ).Value = <result># Goodbye Excelworkbook.Save( )workbook.Close( )xlApp.Quit( )

Here is a good link for Excel from/to Python usage:

continuum.io/using-excel

mentions xlwings, DataNitro, ExPy, PyXLL, XLLoop, openpyxl, xlrd, xlsxwriter, xlwt

Also I found that ExcelPython is under active development.

1. https://github.com/ericremoreynolds/excelpython

2.

What you can do with VBA + Python is following:

Compile your py scripts that take inputs and generate outputs as text files or from console. Then VBA will prepare input for py, call the pre-compiled py script and read back its output.

3.

Consider Google Docs, OpenOffice or LibreOffice which support Python scripts.

This is assuming that available options with COM or MS script interfaces do not satisfy your needs.

This is not free approach, but worth mentioning (featured in Forbes and New York Times):

https://datanitro.com

This is not free for commercial use:

PyXLL - Excel addin that enables functions written in Python to be called in Excel.