Distributing xlwings-powered Excel files. 'ActiveX component can't create object' error

Question

I have an Excel file which relies heavily on UDFs programmed in python (xlwings). It works just great on a PC where python is installed along with the add-in. The problem is I have to send the file to a bunch of people who don't even know what python is. Because of UDFs, a frozen executable is out of the question. So what I did:

Downloaded an embeddable python installation (Windows x86 embeddable zip file) from python.org, extracted it to the folder where the xlsm file is (in a separate folder named 'Python').
Downloaded get-pip.py from bootstrap.pypa.io, uncommented the line 'import site' in the file python36._pth, ran 'python get.pip.py'.
Downloaded pywin32 and comtypes wheels from Christoph Gohlke's great site, installed them with 'pip install [filename].whl'. Installed xlwings ('pip install xlwings'). The total size of this installation is 50Mb.
Created a new file with a xlwings module installed ('xlwings quickstart myproject --standalone'). Renamed the '_xlwings.conf' sheet to 'xlwings.conf', entered the following formula in the 'Interpreter' field: =LEFT(CELL("filename";A1);FIND("[";CELL("filename";A1))-1) & "Python\pythonw.exe" It automatically calculates the correct path to the python interpreter wherever the file is opened.

Macros and UDFs seem to be working, but each time when I open the file all UDF formulas throw an 'ActiveX component can't create object' error. If I force a recalculation ([CTRL]-[ALT]-[SHIFT]-F9) everything starts working again, but I can't expect that my end users will know how to do this. Is there a trick to make UDFs work out of the box? Or maybe there's a more convenient way to distribute xlwings-powered Excel files?

If you replace the formula under point 4 with a hardcoded path for testing, do you see the same behaviour? — Felix Zumstein
– Felix Zumstein, Commented Oct 30, 2017 at 16:29
Sorry, forgot to mention that, of course I tried it, still got the same ActiveX error. — Vateaux
– Vateaux, Commented Oct 30, 2017 at 17:11
I'm not very familiar with xlwings, but have you tried running recalculation under the Workbook_Open event in ThisWorkbook? — pheeper
– pheeper, Commented Nov 6, 2017 at 22:31
Yes that did the trick. Thanks! Added the following VBA code, now it automatically runs a full recalculation as soon as the workbook is opened: Private Sub Workbook_Open() Application.CalculateFull End Sub Still feels like a workaround, but most importantly it gets the job done — Vateaux
– Vateaux, Commented Nov 7, 2017 at 19:10

Vateaux · Accepted Answer · 2017-11-07 22:44:54Z

0

As Phillip suggested in the comment section, the solid workaround is to add the following VBA code to the 'ThisWorkbook' module:

Private Sub Workbook_Open()
Application.CalculateFull
End Sub

It launches a full recalculation as soon as the workbook is opened. Still, it would be great if xlwings worked smoothly without any tinkering. Such a great package, in my opinion, the only thing that stops it from more widespread adoption is problems with distribution.

Unfortunately, I can't upvote Phillip's comment, can't thank him enough.

answered Nov 7, 2017 at 22:44

Vateaux

1434 bronze badges

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

Collectives™ on Stack Overflow

Distributing xlwings-powered Excel files. 'ActiveX component can't create object' error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related