OPENING SEVERAL HYPERLINKS

[Santi]

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''s Student]

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

 

[Santi]

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]

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

Are all the "visible" links clickable manually??

 

[Santi]

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

 

[Gary''s Student]

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.

 

[Santi]

THIS WORKED THANK YOU SOO MUCH!!!

Do you know where I can find more information on how to use and learn VBA?

 

[Gary''s Student]

See:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or perhaps:

http://www.excel-vba.com/index.htm