Calling python script from excel/vba

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 t开发者_StackOverflow社区o 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?

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

   

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 Excel
xlApp = win32com.client.Dispatch( "Excel.Application" )
workbook = xlApp.Workbooks.Open( <some-file> )
sheet = workbook.Sheets( <some-sheet> )
sheet.Activate( )

# Get values
spam = sheet.Cells( 1, 1 ).Value

# Process values
...

# Write values
sheet.Cells( ..., ... ).Value = <result>

# Goodbye Excel
workbook.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.

Updated 2018

xlwings is a BSD-licensed Python library that makes it easy to call Python from Excel and vice versa.

• Scripting: Automate/interact with Excel from Python using a syntax that is close to VBA.
• Macros: Replace your messy VBA macros with clean and powerful Python code.

• UDFs: Write User Defined Functions (UDFs) in Python (Windows only).

• Installation

• Quickstart

There's a tutorial on CodeProject on how to do this.

See http://www.codeproject.com/Articles/639887/Calling-Python-code-from-Excel-with-ExcelPython.

Another open source python-excel in process com tool. This allows executing python scripts from excel in a tightly integrated manner.

https://pypi.python.org/pypi/Python-For-Excel/beta,%201.1