Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    UK
    Posts
    17

    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?

    Can anyone help?

    Thanks

    Exisiting code refered to above is:

    Dim oApp As Object
    Dim Filename As String

    Filename = "\\Pathname.xls"

    Set oApp = CreateObject("Excel.Application")

    oApp.Visible = True
    oApp.Workbooks.Open Filename

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I am not sure what your add-ins are, but if they are Excel ‘xla’ files, then the Excel ‘xls’ file(s) that uses them should open the ‘xla’ file at the same time as the ‘xls’ file.

    This assumes they are correctly reference in the ‘xls’ file and have not been moved!

    If they are standard excel Add-ins then, for example, the code

    AddIns("AutoSave").Installed = True

    should add the Autosave add-in (in the Workbook_Open event !)

    This would assume that Macros are enabled in the Execel application (which I understand has even more complicated option in Office 2003!).


    Am I missing something ?



    MTB

  3. #3
    Join Date
    Feb 2004
    Location
    UK
    Posts
    17
    Thanks for the reply.

    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.

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi Again

    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.

    (Maybe this should be in the Excel forum?)

    MTB

  5. #5
    Join Date
    Feb 2004
    Location
    UK
    Posts
    17

    Red face

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •