Add-in path difficulties (2003)

Topic

New Reply

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,

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

You could include code in your add-in that fires when a user opens a workbook and then runs this sub:

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

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

Reply | Quote