Updating Links
 

We have an Excell addin. On one machine the addin is in one directory but on
another a different directory. When a shared workbook is accessed then if
saved on the first machine the second will always ask to update links. What
is the best way to update these links or for the workbook to automatically
identify the addin so that this message does not appear. If this is to be
done through VBA then what is the correct code for this.

Thanks

Updating Links
 

Kt,

I'm assuming you mean that there are functions in the addin and the path is
messed up. If so, there's no good way, but you can include a utility in the
addin that will patch the references to the addin functions. e.g. this sub
would be called by the addin utility to patch a specific sheet

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

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("Function1", "Function2", "etc")

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 0
rngReplace.Replace strReplace, ""

End If

End If

Next vFunction

Next rngCell

End Sub

Robin Hammond
www.enhanceddatasystems.com

"kt" wrote in message
...
We have an Excell addin. On one machine the addin is in one directory but
on
another a different directory. When a shared workbook is accessed then if
saved on the first machine the second will always ask to update links.
What
is the best way to update these links or for the workbook to automatically
identify the addin so that this message does not appear. If this is to be
done through VBA then what is the correct code for this.

Thanks