13

I have Python code that reads three arguments (scalars) and a text file, and then it 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 sheets. I wanted to know what was the easiest way to do it. Here is some stuff 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?

Improve this question
2

6 Answers 6

Reset to default
18

Follow these steps carefully

  1. Go to ActiveState's website 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 Microsoft Office Excel and open a worksheet

  6. Go to menu 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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

this is not free for production use
5

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()
Improve this answer

1 Comment

Hi Katrielalex, well I need to be able to run this code from excel just clicking a button to which I would assign a macro. This allows me to call excel from python but I want to do the opposite.
4

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

continuum.io/using-excel

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

Also, I found that ExcelPython is under active development.

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

You can do the following with VBA + Python:

Compile your Python scripts that take inputs and generate outputs as text files or from the console. Then VBA will prepare input for Python, call the precompiled Python 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 Microsoft script interfaces do not satisfy your needs.

This is not a free approach, but it is worth mentioning (featured in Forbes and The 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.

Improve this answer

Comments

1

As of 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

Improve this answer

Comments

0

There's a tutorial on The Code Project on how to do this.

See Calling Python code from Excel with ExcelPython

Improve this answer

1 Comment

The Code Project allegedly closed down in October 2024, though it is still in read-only mode. Thus that link isn't broken yet. But it may happen soon. Some pages are already unavailable.
0

Here is another open source Python-Excel in-process COM tool. This allows executing Python scripts from Excel in a tightly integrated manner.

Python-For-Excel

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.