Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Auto Install Addins (All Excel above 97)

    Hi

    I have a large spreadsheet with lots of formulas some of which require the the toolpaks installed, is there a way to get a macro to run automaticaly when the workbook opens. I believe its a matter of naming the macro, but I am not sure

    Many thanks

    Braddy


    Sub autoInstallAddIns()
    '
    ' autoInstallAddIns
    ' Macro recorded 03/04/2005 by Alan Bradshaw
    '

    '
    AddIns("Analysis ToolPak").Installed = True
    AddIns("Analysis ToolPak - VBA").Installed = True
    End Sub
    If you are a fool at forty, you will always be a fool

  2. #2
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Auto Install Addins (All Excel above 97)

    HI Hans

    I could no get it to work, please see attached

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Install Addins (All Excel above 97)

    Typo corrected by Hansv

    Open the Visual Basic Editor (Alt+F11)
    Double click the 'ThisWorkbook' node for the relevant workbook in the treeview on the left hand side.
    Enter or paste the following code into the module window:

    Private Sub Workbook_Open()
    Call autoInstallAddins
    End Sub

    The Workbook_Open event occurs each time the workbook is opened. The above code MUST be in ThisWorkbook, not in a standard module.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Install Addins (All Excel above 97)

    You forgot to include the module with the sub autoInstallAddins (or whatever it was you called it <smile>)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Install Addins (All Excel above 97)

    Sorry, my reply contained a typo. The name of the procedure should be Workbook_Open, not Worbook_Open.

    (BTW, the workbook you attached does not contain a module with a macro named autoInstallAddins.)

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Auto Install Addins (All Excel above 97)

    HI Hans

    No need for an apology need I collect rareities.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Auto Install Addins (All Excel above 97)

    Hi Braddy & Hans,

    If this woorkbook is being run on different PCs then it might be prudent to check that the addins required have actually been installed on the PC, first, and then if they have been installed but not loaded, to load them automatically.

    Am I correct in understanding that you have addressed the second part of this situation, but not the first.

    If the required addins have not been installed then this is required before the workbook can be used, and may need an appropriate error message to the user.

    Good Luck!

    Peter Moran

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Auto Install Addins (All Excel above 97)

    Hi Peter

    Thanks for your reply I take on board your ref to checking if the remote PC has addins installed, the enable addins macro works fine on the PC I used for checking, what kind of error will I get if they are not installed?

    Many Thanks

    Braddy

    NB Could explain what you mean by and appropriate error message, and how would I define this?
    If you are a fool at forty, you will always be a fool

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Install Addins (All Excel above 97)

    You could use something like this:

    Sub autoInstallAddIns()
    LoadOneAddIn "Analysis ToolPak"
    LoadOneAddIn "Analysis ToolPak - VBA"
    End Sub

    Sub LoadOneAddIn(strName As String)
    On Error GoTo ErrHandler
    AddIns(strName).Installed = True
    Exit Sub
    ErrHandler:
    Select Case Err
    Case 9
    MsgBox "Add-in '" & strName & "' not found." & vbCrLf & _
    "It must be installed on this PC before it can be used.", vbExclamation
    Case Else
    MsgBox Err.Description, vbExclamation
    End Select
    End Sub

    To see what happens if an add-in is not available, specify a name of a non-existent add-in:

    Sub autoInstallAddIns()
    LoadOneAddIn "Analysis ToolPak"
    LoadOneAddIn "IDoNotExist"
    End Sub

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Auto Install Addins (All Excel above 97)

    Hi Hans

    Sorry for the late reply and thank you for your reply.


    Braddy
    If you are a fool at forty, you will always be a fool

  11. #11
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Auto Install Addins (All Excel above 97)

    Hi Braddy and HansV,

    Sorry for the delay in getting back to this, but it seems that we have the solution as per usual from HansV.

    It always looks so simple when an expert does it!

    In Australia I have had to code to be able to handle the situation where machines are installed and setup as US date formats rather than Australian (d/m/y) - and here you also need to issue a message and terminate Excel so the appropriate changes can be made to Windows.

    <pre>' Check that the date format is d-m-y - NOT m-d-y (problems!!)
    If Application.International(xlMDY) = True Then
    Message = "The Windows date format is 'mm-dd-yy' instead of 'dd-mm-yy'. " & _
    (Chr(13)) & _
    "Please go to Control Panel and change Regional Settings to " & (Chr(13)) & _
    "** English(Australian) ** and then restart Excel."
    Title = "Windows Date Format"
    MyValue = MsgBox(Message, , Title)
    End
    End If
    </pre>



    Good Luck!

    Peter Moran

Posting Permissions

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