Unanswered: Help required to Open Excel with Add-ins from Access?
I'm trying to open an instance of Excel from a button in a Access Form menu together with a specific file.
I can do this by using the code below BUT I need to open an instance of Excel that installs some add-ins on opening. I have a macro within Excel that requires the add-ins to run properly - I have tried editing the excel macro to install the add-ins first but this doesn't seems to work properly.
To open Excel outside of Access I have a shortcut that installs the add-ins when opening Excel by supplying the locations in the target path - I wondered if I can refer to this shortcut or create something similar in Access?
You are correct in your assumptions - The Add-in is an xla files and does appear in the add-in list.
I have been rather puzzled though as the add-in should create a toolbar menu and prompt the user for a login name/password - It's for an external package that allows excel links.
On opening excel this is not happening and therefore ignores the code linking the two packages and returns blank values.
If I manually uncheck the add-in and recheck it the password prompt appears and in runs successfully. I have managed to add some code in Excel that looks to see if the add-in is referenced/checked at the start of the macro that un/rechecks the add-in or loads it if it doesn't exist, which appears to kick start it. I'm still having some teething problems with this as stepping through the code it works perfectly but running it through a button seems to ignore the uncheck/recheck at the start.
I may try running two separate subs to try and resolve this - Looks like it'll be trial and error to solve it.
I have investigated further & can make (add) a toolbar when the xla file loads. Both as an Add-in in the MS Office library directory and referanced in the Excel Addi-ins OR just reference form another xls file in it’s VBA project references.
The only think that did’t work was when the code to load the toolbar was in the ThisWorkbook module, but if the code is in a separate (added) module and called from the Workbook_Open() event in the ThisWorkbook module it runs OK.
All this is provided Macros are not disabled!
I do not know if this helps but if not, then perhaps a sample of code and where the code is will help.
Thanks for your time on this - After trying various install/uninstall attempts I think I have just found a solution in Access after all which as it happens is rather obvious....... but so much so that I glared straight past it.
As you correctly pointed out before the xla files are associated with the .xls file.
Rather than open the file via the CreateObject("Excel.Appliaction") method I used the Hyperlink Address in the format tab of the button to open the file.
This loads the file into an open session of Excel (if one exists) or opens both Excel and prompts for the password and loads the add-ins correctly.