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.

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" wrote:

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

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



"Gary''s Student" <(E-Mail Removed)> wrote in message
news:4F1C7EEA-9742-4C3C-ACC7-(E-Mail Removed)...
>
>
>
> 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" wrote:
>
>> 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.
>>
>>
>>

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" <(E-Mail Removed)> wrote in message
news:4F1C7EEA-9742-4C3C-ACC7-(E-Mail Removed)...
>
>
>
> 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" wrote:
>
>> 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.
>>
>>
>>

Thnaks Simon


"Simon Lloyd" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
>
> Don, I came across this a little while ago, it should suit your needs!
>
> Code:
> --------------------
> Sub ListExternalLinks()
>
> 'Written by OzGrid Business Applications
> 'www.ozgrid.com
>
> '''''''''''''''''''''''''''''''''''''''''''''''''''''
> 'Creates a Worsheet called "Link List" and lists ALL _
> external links in the Workbook.
> '''''''''''''''''''''''''''''''''''''''''''''''''''''
>
> Dim sht As Worksheet
> Dim LinkCells As Range, Cell As Range
> 'Add a new sheet to list all external links.
> On Error Resume Next
> Sheets.Add().Name = "Link List"
> Application.DisplayAlerts = False
> 'If name does NOT = "Link List" then it already exists
> If ActiveSheet.Name <> "Link List" Then ActiveSheet.Delete
> Application.DisplayAlerts = True
> 'Clear column A and format as text.
> Sheets("Link List").Columns(1).Clear
> Sheets("Link List").Columns(1).NumberFormat = "@"
> 'Loop through each worksheet
> For Each sht In ThisWorkbook.Worksheets
> 'Set "LinkCells" to range that has formulas
> Set LinkCells = Nothing
> Set LinkCells = sht.Cells.SpecialCells(xlCellTypeFormulas)
> If Not LinkCells Is Nothing Then
> 'Loop through each cell in "LinkCells"
> For Each Cell In LinkCells
> 'See if if an external link or not.
> If Cell.Formula Like "[*" Then
> 'It is, so copy the formula to column A of "Link List"
> Sheets("Link List").Cells _
> (65536, 1).End(xlUp).Offset(1, 0) = Cell.Formula
> End If
> Next Cell
> End If 'Not LinkCells Is Nothing
> Next sht
> End Sub
> --------------------
>
> To use it, push Alt+F11 then go to Insert>module and paste
> in the code. Then Push Alt+Q and then Alt+F8, click "ListExternalLinks"
>
> and then Run.
>
> Regards,
> Simon
>
>
> --
> Simon Lloyd

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


"Don" <(E-Mail Removed)> wrote in message
news:eDQhY$%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in
> message news:4F1C7EEA-9742-4C3C-ACC7-(E-Mail Removed)...
>>
>>
>>
>> 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" wrote:
>>
>>> 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.
>>>
>>>
>>>
>
>