Calling python script from excel/vba

Question

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:

• 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?

Answer 1

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