Create hyperlinks from external links

[Don]

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.

 

[Guest]

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??

 

[Don]

Yes, you are right. It would be difficult if there were more than one
referenced link.

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

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.

 

[Don]

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

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

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.

 

[Don]

Thnaks Simon

 

[Don]

I left out an error line in the first post:


Sub Fixit()
On Error Resume Next
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,

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

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

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.