xlwings for Excel for Mac 2016

2
votes

I am using mac OS X Sierra with Mac for Ecel 2016 and python 3.6. I am trying to launch a python script from excel because:

  • I already have an excel with a lot of functionalities and I would like to add some features to this product using Python.
  • The users of my product won't be comfortable launching python scripts from a python console, so I'd like to have some kind of excel user interface.

I am providing this background because I am open to suggestions on how to do differently. From my researches so far, it seems that this can be done using the xlwings library. I have tried using it following the instructions on:

http://docs.xlwings.org/en/stable/vba.html#vba

I am somehow stuck at this stage:

Open the VBA editor with Alt-F11 Then go to File > Import File... and import the xlwings.bas file. It can be found in the directory of your xlwings installation.

As alt + F11 doesn't open the macro editor, I have understood the instructions as follows:

  • click on the 'macros' button in the 'developper' tab of excel
  • create a new macro / edit an existing one
  • go to 'add references' on the bottom left corner
  • open the file mentioned

The issue is that I can't open the xlwings.bas file ; I can only open some of the files. I managed to open a file called xlwings.xlsm ; that creates a new tab called 'xlwings' on Excel, in which there is one button: 'import Python UDFs'. When I press this button it throws the error :

Run-time error '53': File not found: xlwings64.dll

In one of my previous attempts using xlwings, I had a different error at this stage. I cant remember it, but from my researches the solution implied doing something on the trust center of excel 2016 which I couldn't do with Excel for Mac 2016.

Alternatively, when I try to run a macro using RunPython, it throws the following error :

Compile error: Sub or Function not defined

Pointing at RunPython...

If someone knows what I did wrong and/or how I can manage to use xlwings, that would be great.

Thank you for the help,

2
pythonexcelvbaxlwings
Have you tried before to download and run one of the examples here: xlwings.org/examples ? The excel files in these examples should already include all the requisite VBA code. It won't directly solve your problem, but it might give some useful clues. – Xukrao
Thanks for the help ; not all functionalities are working (in fact it's not possible to use RunFrozenPython which is not supported by Mac), but at least some are... I guess that the brutal approach is then for me to take that already set up excel and change it for the purposes of my project... – Vincent
From what you're describing I don't think you were in the right screen for loading the 'xlwings.bas' file. Try looking for a button in the Developer tab labeled 'Visual Basic', this should open the correct screen (namely the VBA Editor screen). – Xukrao

2 Answers

1
votes

The fastest way to create a new project according to https://docs.xlwings.org/en/stable/command_line.html is to use the CLI

xlwings quickstart myproject

This will create an .xslm and a .py file.

0
votes

For your information, the best solution that I found (thanks to Xukrao) was to download an example from xlwingsexamples, go to the macro editor, copy the "xlwings" module that is in the example worksheet and paste it in the macros of my worksheet.