Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jul 2006
    Location
    New York City, New York, USA
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Add-in path difficulties (2003)

    Hi,
    We had an add-in mistakenly installed under Documents and Settings for a few users, when it is usually installed under Program Files.
    When the user's with the add-in installed incorrectly send files to the users with the correct installation, the users with the correct installation are getting NAME errors wherever the add-in's functions are used. The formula will show the complete path to the incorrect install location before each function.
    Is this normally the case - does the path to the add-in on the computer of origin for the file trump the path on the computer that the file is opened on?
    (Of course, we ultimately need to reinstall the add-in correctly, but it doesn't look like that's going to be a simple matter.)
    Thanks,

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

    Re: Add-in path difficulties (2003)

    Formulas using functions from an installed add-in shouldn't include the path to the add-in, just the name of the function should be sufficient. It shouldn't matter then in which path the add-in has been installed.
    So try removing the path from the formulas.

  3. #3
    Lounger
    Join Date
    Jul 2006
    Location
    New York City, New York, USA
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add-in path difficulties (2003)

    Thanks, Hans. On the computer where the files are created, the formulas are typed in correctly, the results are correct, and the paths are not displayed. But when such a file is then sent to another user with the other install path, the path from the initial machine shows up before each add-in function in a formula, and the functions result in errors.
    I tried your suggestion - deleting the paths then does allow the formulas to work. So that's great, at least that's something for us to work with. But I just want to be sure: according to the book, this sort of thing shouldn't be happening, right?
    Thanks again.

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

    Re: Add-in path difficulties (2003)

    It's strange that the formulas show the path to the add-in on the original PC at all. If you enter a formula manually, you don't need to enter the path, and if you use the function wizard, it should insert only the name of the function, not the path of the add-in. So I don't understand how the path got there in the first place.

  5. #5
    Lounger
    Join Date
    Jul 2006
    Location
    New York City, New York, USA
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add-in path difficulties (2003)

    Hi Hans,
    Sorry if I was unclear. They never show the path on the original PC, and always show it on the other PCs that have the different install location for the add-in. Very Strange.

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

    Re: Add-in path difficulties (2003)

    In the long term, it's no doubt best to reinstall the add-in(s) correctly, for otherwise you'll have to keep on repairing workbooks created on the problem PC.

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

    Re: Add-in path difficulties (2003)

    You could include code in your add-in that fires when a user opens a workbook and then runs this sub:
    <pre>Sub CheckAndFixLinks(oBook As Workbook)
    '-------------------------------------------------------------------------
    ' Procedure : CheckAndFixLinks Created by Jan Karel Pieterse
    ' Company : JKP Application Development Services 2006
    ' Author : Jan Karel Pieterse
    ' Created : 2-5-2006
    ' Purpose : Checks for links to addin and fixes them
    ' if they are not pointing to proper location
    '-------------------------------------------------------------------------
    Dim vLink As Variant
    Dim vLinks As Variant
    On Error GoTo LocErr
    vLinks = oBook.LinkSources(xlExcelLinks)
    If IsEmpty(vLinks) Then Exit Sub
    For Each vLink In vLinks
    If vLink Like "*NameOfYourXLA*" Then
    Application.DisplayAlerts = False
    oBook.ChangeLink vLink, ThisWorkbook.FullName, xlLinkTypeExcelLinks
    Application.DisplayAlerts = True
    End If
    Next
    On Error GoTo 0
    End Sub</pre>


    To make this work, you need ot set up an Application level event handler in a class module, with a App_WorkbookOpen event in it which calls the above routine
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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