Writing a VBA macro to call a python script using xlwings

Question

I want to write a VBA macro in Excel to call a python script. The python script will execute some commands then return a pandas dataframe. I have code that writes to the excel spreadsheet and that is inside the python script.

I have installed xlwings and read through the VBA macro tutorial but am still confused. Should I use a User Defined Function? Do I need to "return anything" in the script?

Richard Fitzhugh · Accepted Answer · 2017-08-03 17:16:09Z

2

If your Python script writes to the workbook directly, this is not difficult. First, you don't need to return anything from either the VBA macro or the Python if what you write to the workbook is sufficient for you.

You don't need a UDF. All you need is the following (which I've basically copied from the xlwings official docs here):

1) Make sure your excel book can talk to the file you have your script in (best done using the command xlwings quickstart <project_name> on the command line, then copying your script to the resulting Python file.

2) Make sure you can call everything you need from a single function like __main__() (or be prepared to run execfile or something similar)

3) Go to your excel book and make a command button, then assign it the macro SampleCall which xlwings has kindly provided for you

4) Now open the VBA editor. You should see the SampleCall macro immediately:

Sub SampleCall()
    RunPython ("import testproj;testproj.world()")
End Sub

Just edit the names of testproj and world appropriately.

You can now click your CommandButton to execute your script!

answered Aug 3, 2017 at 17:16

Richard Fitzhugh

4223 silver badges6 bronze badges

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

2 Comments

jenkelblankel Over a year ago

testproj being name of project and world being name of python file. I'll give it a shot!

Richard Fitzhugh Over a year ago

testproj is the name of the Python file and world is the name of the function you want to execute from the file. The RunPython function will actually execute Python code, so the import statement there is working exactly as it would in a normal python file. Good luck!

Collectives™ on Stack Overflow

Writing a VBA macro to call a python script using xlwings

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related