We have developed an addin that is sometimes used at sites where people
have installed it at on different drives (most on the C: drive, some on
K: for example)

When users share excel sheets that use an add-in function between PCs
that have it installed in different locations, the functions and
controls are mapped to the path of the PC that created/saved the file.
We have to clean off the path to get the functions and controls to
point at the local version.

Anybody see this problem before and do you have a solution or some
insight?

Thanks

Aaron

Have you tried something like Microsoft Windows Installer
(part of the Visual Studio suite)
to install your addin on each PC?

This should allow you, the developer, to specify
where the addin is installed. - leading to consistency

"ACMosh" wrote:

We have developed an addin that is sometimes used at sites where people
have installed it at on different drives (most on the C: drive, some on
K: for example)

When users share excel sheets that use an add-in function between PCs
that have it installed in different locations, the functions and
controls are mapped to the path of the PC that created/saved the file.
We have to clean off the path to get the functions and controls to
point at the local version.

Anybody see this problem before and do you have a solution or some
insight?

Thanks

Aaron

Aaron,

It happens to everybody.

Add something like this to your add-in as a utility to be used by anybody
opening a workbook containing your custom functions.

If you want to take it a stage further you could theoretically add code to
your add-in that monitors each workbook opened, looking at a property of the
file to see if it was created with your add-in, and if it identifies it as
an add-in related file, launches this utility automatically, but I think
this would be overkill and hard to maintain.

Robin Hammond
www.enhanceddatasystems.com

Sub FixLinks()

Dim lOldCalc As Long
Dim shInput As Worksheet

If ActiveWorkbook Is Nothing Then Exit Sub

On Error GoTo FixLinks_Error

Application.ScreenUpdating = False
lOldCalc = Application.Calculation

With ActiveWorkbook

For Each shInput In .Sheets

PatchFunctions shInput

Next shInput

On Error Resume Next
If Val(Application.Version) < 10 Then

Application.Calculate

Else

#If VBA6 Then

Application.CalculateFull

#End If

End If
On Error GoTo 0

Call MsgBox("Your function references should have been updated", vbOKOnly,
"Patcher")
Application.ScreenUpdating = True
Application.Calculation = lOldCalc

On Error GoTo 0
Exit Sub

FixLinks_Error:
' handle your error here
End Sub

Private Sub PatchFunctions(shInput As Worksheet)

Dim rngTest As Range
Dim rngCell As Range
Dim rngReplace As Range
Dim vFunctions As Variant
Dim vFunction As Variant
Dim strFormula As String
Dim strReplace As String
Dim lFlStartPoint As Long
Dim lRefStartPoint As Long
Dim bReprotect As Boolean

On Error GoTo PatchFunctions_Error

If shInput.ProtectContents = True Then

bReprotect = True

On Error GoTo ProtectionError
shInput.Unprotect
On Error GoTo PatchFunctions_Error

End If

Set rngTest = Nothing
On Error Resume Next
Set rngTest = shInput.UsedRange.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If rngTest Is Nothing Then

On Error GoTo 0
Exit Sub

End If

If rngTest.Count = 0 Then

On Error GoTo 0
Exit Sub

End If

vFunctions = Array("CustomFunction1", "CustomFunction2")

For Each rngCell In rngTest

For Each vFunction In vFunctions

strFormula = rngCell.Formula
lFlStartPoint = InStr(strFormula, CStr(vFunction))

If lFlStartPoint 2 Then

'if it has a single quote exclamation mark preceding it,
'then we have an external reference that can be replaced
If Mid(strFormula, lFlStartPoint - 2, 2) = "'!" Then

'excel 97 doesn't do instrrev so do this the long way
lRefStartPoint = lFlStartPoint - Len(vFunction) - 2
Do Until (Mid(strFormula, lRefStartPoint, 1)) = "'"

lRefStartPoint = lRefStartPoint - 1

Loop

strReplace = Mid(strFormula, lRefStartPoint, lFlStartPoint -
_
lRefStartPoint)

On Error Resume Next
Set rngReplace = rngCell
Set rngReplace = rngCell.CurrentArray
On Error GoTo PatchFunctions_Error

rngReplace.Replace strReplace, ""

End If

End If

Next vFunction

Next rngCell

If bReprotect Then shInput.Protect , , True

EndRoutine:
On Error GoTo 0
Exit Sub

ProtectionError:
Err.Clear
On Error GoTo 0
Call MsgBox("Unable to unprotect: " & shInput.Name, vbOKOnly, "Patcher")
Resume EndRoutine

PatchFunctions_Error:
'handle error here
On Error GoTo 0
Resume EndRoutine
End Sub



"ACMosh" wrote in message
oups.com...
We have developed an addin that is sometimes used at sites where people
have installed it at on different drives (most on the C: drive, some on
K: for example)

When users share excel sheets that use an add-in function between PCs
that have it installed in different locations, the functions and
controls are mapped to the path of the PC that created/saved the file.
We have to clean off the path to get the functions and controls to
point at the local version.

Anybody see this problem before and do you have a solution or some
insight?

Thanks

Aaron

Another approach might be to search for links that are "Like" your your
addin name. If the link is not exactly the same as your addin's Fullname
then change it. The bare bones of a routine something like this:

sPart = "*" & myAppName
sFN = MyAddin.fullname
vLink = wb.LinkSources(xlLinkTypeExcelLinks)
If Not IsEmpty(vLink) Then
Erase vLink
For Each vLink In wb.LinkSources(xlLinkTypeExcelLinks)
If vLink Like sPart Then
If vLink < sFN Then
wb.ChangeLink vLink, sFN, xlLinkTypeExcelLinks
End If
End If
Next
End If

Regards,
Peter T

"ACMosh" wrote in message
oups.com...
We have developed an addin that is sometimes used at sites where people
have installed it at on different drives (most on the C: drive, some on
K: for example)

When users share excel sheets that use an add-in function between PCs
that have it installed in different locations, the functions and
controls are mapped to the path of the PC that created/saved the file.
We have to clean off the path to get the functions and controls to
point at the local version.

Anybody see this problem before and do you have a solution or some
insight?

Thanks

Aaron

Thanks everyone, I'll give these a try and see where they take me.