In an excel worksheet how can I open several hyperlinks at once without
having to click on each one? Taking into account that the hyperlinks are a
result of a €œVlookup€� formula. Can anyone help? I tried the macro listed
below which works but only when the hyperlink is not part of a formula.


Sub hypper()
Dim h As Hyperlink
For Each h In ActiveSheet.Hyperlinks
h.Follow
Next
End Sub

If hyperlinks were inserted using the HYPERLINK() function, then this may help:

Sub hypperrr()
'
' gsnuxx
'
Set ru = ActiveSheet.UsedRange
Set rf = ru.SpecialCells(xlCellTypeFormulas)

For Each r In rf
s = r.Formula
lk = r.Value
If InStr(s, "=HYPERLINK(") Then
ActiveWorkbook.FollowHyperlink Address:=lk
End If
Next
End Sub

--
Gary''s Student - gsnu200798


"Santi" wrote:

In an excel worksheet how can I open several hyperlinks at once without
having to click on each one? Taking into account that the hyperlinks are a
result of a €œVlookup€� formula. Can anyone help? I tried the macro listed
below which works but only when the hyperlink is not part of a formula.


Sub hypper()
Dim h As Hyperlink
For Each h In ActiveSheet.Hyperlinks
h.Follow
Next
End Sub

Gary,

Thank you for all your help, but I get the message below when I try that
macro. Do you know why?

€œRun-time error€”€˜2147221014 (800401 EA):

Cannot open the specified file



"Gary''s Student" wrote:

If hyperlinks were inserted using the HYPERLINK() function, then this may help:

Sub hypperrr()
'
' gsnuxx
'
Set ru = ActiveSheet.UsedRange
Set rf = ru.SpecialCells(xlCellTypeFormulas)

For Each r In rf
s = r.Formula
lk = r.Value
If InStr(s, "=HYPERLINK(") Then
ActiveWorkbook.FollowHyperlink Address:=lk
End If
Next
End Sub

--
Gary''s Student - gsnu200798


"Santi" wrote:

In an excel worksheet how can I open several hyperlinks at once without
having to click on each one? Taking into account that the hyperlinks are a
result of a €œVlookup€� formula. Can anyone help? I tried the macro listed
below which works but only when the hyperlink is not part of a formula.


Sub hypper()
Dim h As Hyperlink
For Each h In ActiveSheet.Hyperlinks
h.Follow
Next
End Sub

It means the system is having trouble openning one or more of the links.

Are all the "visible" links clickable manually??
--
Gary''s Student - gsnu200798


"Santi" wrote:

Gary,

Thank you for all your help, but I get the message below when I try that
macro. Do you know why?

€œRun-time error€”€˜2147221014 (800401 EA):

Cannot open the specified file



"Gary''s Student" wrote:

If hyperlinks were inserted using the HYPERLINK() function, then this may help:

Sub hypperrr()
'
' gsnuxx
'
Set ru = ActiveSheet.UsedRange
Set rf = ru.SpecialCells(xlCellTypeFormulas)

For Each r In rf
s = r.Formula
lk = r.Value
If InStr(s, "=HYPERLINK(") Then
ActiveWorkbook.FollowHyperlink Address:=lk
End If
Next
End Sub

--
Gary''s Student - gsnu200798


"Santi" wrote:

In an excel worksheet how can I open several hyperlinks at once without
having to click on each one? Taking into account that the hyperlinks are a
result of a €œVlookup€� formula. Can anyone help? I tried the macro listed
below which works but only when the hyperlink is not part of a formula.


Sub hypper()
Dim h As Hyperlink
For Each h In ActiveSheet.Hyperlinks
h.Follow
Next
End Sub

yes the original links work, I get this error when I enter the formula
"hyperlink" on a different worksheet.

"Gary''s Student" wrote:

It means the system is having trouble openning one or more of the links.

Are all the "visible" links clickable manually??
--
Gary''s Student - gsnu200798


"Santi" wrote:

Gary,

Thank you for all your help, but I get the message below when I try that
macro. Do you know why?

€œRun-time error€”€˜2147221014 (800401 EA):

Cannot open the specified file



"Gary''s Student" wrote:

If hyperlinks were inserted using the HYPERLINK() function, then this may help:

Sub hypperrr()
'
' gsnuxx
'
Set ru = ActiveSheet.UsedRange
Set rf = ru.SpecialCells(xlCellTypeFormulas)

For Each r In rf
s = r.Formula
lk = r.Value
If InStr(s, "=HYPERLINK(") Then
ActiveWorkbook.FollowHyperlink Address:=lk
End If
Next
End Sub

--
Gary''s Student - gsnu200798


"Santi" wrote:

In an excel worksheet how can I open several hyperlinks at once without
having to click on each one? Taking into account that the hyperlinks are a
result of a €œVlookup€� formula. Can anyone help? I tried the macro listed
below which works but only when the hyperlink is not part of a formula.


Sub hypper()
Dim h As Hyperlink
For Each h In ActiveSheet.Hyperlinks
h.Follow
Next
End Sub

This is my problem and not yours.

In the old post (the one about Inserted Hyperlinks), the VBA could easily
find the underlying URL and Follow it.

In this post (looking for the HYPERLINK function), the macro uses what you
see in the cell as the URL. This is O.K. for cells like:

=HYPERLINK("http://www.cnn.com")

But it fails for cells like:

=HYPERLINK("http://www.cnn.com","news")

because "news" is not a URL.

This means that my macro will not work on all hyperlinks. Sorry.
--
Gary''s Student - gsnu200798


"Santi" wrote:

yes the original links work, I get this error when I enter the formula
"hyperlink" on a different worksheet.

"Gary''s Student" wrote:

It means the system is having trouble openning one or more of the links.

Are all the "visible" links clickable manually??
--
Gary''s Student - gsnu200798


"Santi" wrote:

Gary,

Thank you for all your help, but I get the message below when I try that
macro. Do you know why?

€œRun-time error€”€˜2147221014 (800401 EA):

Cannot open the specified file



"Gary''s Student" wrote:

If hyperlinks were inserted using the HYPERLINK() function, then this may help:

Sub hypperrr()
'
' gsnuxx
'
Set ru = ActiveSheet.UsedRange
Set rf = ru.SpecialCells(xlCellTypeFormulas)

For Each r In rf
s = r.Formula
lk = r.Value
If InStr(s, "=HYPERLINK(") Then
ActiveWorkbook.FollowHyperlink Address:=lk
End If
Next
End Sub

--
Gary''s Student - gsnu200798


"Santi" wrote:

In an excel worksheet how can I open several hyperlinks at once without
having to click on each one? Taking into account that the hyperlinks are a
result of a €œVlookup€� formula. Can anyone help? I tried the macro listed
below which works but only when the hyperlink is not part of a formula.


Sub hypper()
Dim h As Hyperlink
For Each h In ActiveSheet.Hyperlinks
h.Follow
Next
End Sub