Gary's Student,
While I was waiting for a response, I wrote this macro as an add-in.
Excuse the sloppy code (I am over 60 and the old basic language keeps
showing up in my code). It seems to work, but if there are more than one
workbooks linked it only hyperlinks to the first workbook. I soes not go
through the whole workbook, only the selected range.
Don
Sub Fixit()
Set myrange = Application.InputBox(prompt:="Select the range", Type:=8)
If myrange Is Nothing Then End
On Error GoTo 0
Application.ScreenUpdating = False
For Each cell In myrange
c = 0
On Error Resume Next
ad = cell.Address
a = cell.Formula
m = Left(a, 2)
If Left(a, 2) <> "=+" Then
b = Replace(a, "='", "")
Else
b = Replace(a, "=+'", "")
End If
b = Replace(b, "[", "")
l = Len(b)
For i = 1 To l
If Mid(b, i, 1) = "]" Then GoTo 100
c = c + 1
Next i
100 If b = "" Then GoTo 200
b = Left(b, c)
If Right(b, 4) <> ".xls" Then GoTo 200
cell.Hyperlinks.Add anchor:=cell, Address:=b
200 Next cell
Application.ScreenUpdating = True
End Sub
Gary''s Student said:
which one ?
In general, a formula can contain links to many, many different files:
='C:\Documents and
Settings\Owner\Desktop\[Book2.xls]Sheet1'!$A$17+'C:\Documents and
Settings\Owner\Desktop\[Book3.xls]Sheet1'!$J$21
How would the macro know which link to use??
--
Gary''s Student
gsnu200710
Don said:
Excel 2000
Does anyone have a macro that will go through a workbook, find all
external
links, and then create a hyperlink to the linked workbook in each cell?
That
way one could click on the cell to open the linked workbook.
