Assigning Excel add-in macros to ribbon using a relative path

Question

I am trying to create a basic Excel GUI for a Python software which uses Excel spreadsheets as inputs.

I have managed to create a basic add-in with a macro which runs the Python code for me.

To make it more user friendly to trigger the macro I thought it would be nice and scalable to create a ribbon for my package. So far it only has the "run" option but in future I will add other pre-and post- processing options.

I realised pretty quickly that ribbons and add-ins are not bound to a specific workbook but are Excel level settings.

To save the user some headaches in having to follow many steps I've managed to bundle all the steps in a simple Excel UI file which can be imported to create the ribbon.

The trick here, is that the ribbon refers to the add-in (and hence macro) using a path that looks like this:

./myaddin.xlam

So, all my colleagues have to do (in theory) is to copy their Excel workbook in the folder where they saved the add-in xlam file, install the ribbon once, and voila!

In practice however, I've noticed some very odd behaviour:

• Immediately after installing the ribbon, if I open a workbook and click 'run' the code runs just fine.
• If I now close and open another workbook, and try to do the same I get a pop-up message saying the macro file can't be found in : 'user/mydocument/myaddin.xlma' Of course, this is normal as the add-in is not saved here. What I'm confused about is why Excel seems to search the 'mydocument' as the current working directory rather than the directory where the workbook is located. Even stranger, if I re-save my workbook using 'save as' it then runs fine again. It seems that in some instances, Excel redefines its working directory and hence when it searches the relative path it gets it wrong.

Where I'm stuck is that all the help I've seen online uses VBA code to change the working directory ... but in my case Excel can't even access the add-in.xlma file where that code would be located in the first place.

If you know a way of resolving this issue I would be most grateful!

PS: I'm on a different computer at the moment as I am travelling for work. But I will share more details with screenshots next week if that is requested