CODEWITHSUNDEEP
pythonvbaexcel
Anninha
Anninha

Reputation: 181

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 to know what was the easiest way to do it, here are some stuffs that I found:

Any tips?

Upvotes: 12

Views: 60713

Answers (6)

Christopher Peisert
Christopher Peisert

Reputation: 24094

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

Upvotes: 1

den.run.ai
den.run.ai

Reputation: 5933

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.

Upvotes: 3

Testautomation
Testautomation

Reputation: 21

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

Upvotes: 0

ehremo
ehremo

Reputation: 387

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.

Upvotes: 0

renick
renick

Reputation: 3881

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

Upvotes: 17

Katriel
Katriel

Reputation: 123612

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( )

Upvotes: 4

Related Questions