hope someone have had the same problem and managed to solve it
I have set the COM add in to load at start up as following: File --> Options --> Add-ins --> Manage COM add-ins --> GO --> checked the box for the relevant Add-in --> Load Behaviour --> At Startup.
So far so good. I can close Excel, reopen it and the Add-in loads properly.
The strange behaviour happens when I open excel as an object via a VBA script running under Access. After Excel is opened as an object via a VBA script the COM add-in gets dropped. After Access has finished running the VBA script if I open Excel the COM add-in do not get loaded. If I go to File --> Options --> Add-ins --> Manage COM add-ins --> GO --> I can see the box for the COM add-in is unchecked despite was checked before.
Is there a way to avoid Excel dropping the COM Add-in when opened as an object? Or not to uncheck the COM Add in box so that if Excel is launched manually the COM Add-in will still be available?
This a strange and quite annoying behaviour as I use the COM add-in to download data and every morning I need to go back into Excel and load again the COM add-in.
Note that when Excel opens as an object via VBA I do not require to download any data. I just would love to have Excel not dropping the add in if opened manually after it has been opened and run as an object
Thanks for your help