Opening hyperlinks with macro??

F

Frazer

Hi, im not too great at excel so was wondering if anyone out there could help
me.
I cant really say what this is actually for, but ill give you the main idea.
Every day, i generate a long list of hyperlinks in excel which i have to open
and check. Obviously as there are so many, it takes a long time to open one,
check it, close it, open the next, etc, scrolling down all the time. I was
wondering if there was any way, by use of macros or otherwise, that i could
open all the links together automatically so i wouldnt have to keep going
back to excel. Any help anyone can give me would be very much appreciated.
Thanks!
 
F

Frazer

oh and i should also mention that clicking on the links whilst recording a
macro does not work- this simply selects the cell rather than clicking the
link
 
S

StinkeyPete

This is a quick and dirty way to do it:

Sub Macro1()
'You will need to adjust the Range("A1:A10") to include your entire list.

Dim cnt As Integer
cnt = 1

For Each c In Worksheets("Sheet1").Range("A1:A10").Cells
Range("A" & cnt).Select
On Error Resume Next
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
cnt = cnt + 1
Next

End Sub
 
F

Frazer

Thanks for the reply, but as i said i am not too great at excel so could
anyone please give a bit more information as to what that actually means??
 
F

Frazer

OK i think i know what to do with it. maybe. probably wrong though.....i put
that code stuff there into a visual basic module, and it comes up as a macro,
but it doesnt actually open a new window for any of the links, i think all it
is doing is selecting each cell rather than actually opening the hyperlink in
each cell

any help??
 
S

StinkeyPete

Right click on the tab you are working on and click on view code. THe copy
and paste from Sub Macro1() to End sub. Then change the code Range("A1:A10")
to include your entire list (assuming it is in the A coulmn). If it is not in
the "A" column you will have to change the line "Range("A" & cnt).Select"
change the A to the correct column. Once this is done run the macro.

Tools>Macro>Macros and select Macro1 and run.
 
F

Frazer

Ive done all that but its not working......I think all thats happening is
that each cell is being selected, but i need the hyperlink to open, which is
not happening at the moment. I have copied all the code exactly so i dont
think that is the problem.....any more ideas??
 
S

StinkeyPete

The code worked for me fine. This code assumes that your hyperlinks are in
column in rows 1 through 10. Where are your hyperlinks located in your
spreadsheet?
 
F

Frazer

There in sheet 3, cells C1:C70


I changed all the code to correcpond with this though.....

What exactly happens when you run your macro? Could it be that some of my
settings are not correct??
 
S

StinkeyPete

Right click on sheet3 and click view code. Then copy and paste this code and
the run the macro (Tools>Macro>Macros.



Sub Macro1()
Dim cnt As Integer
cnt = 1

For Each c In Worksheets("Sheet1").Range("C1:C70").Cells
Range("C" & cnt).Select
On Error Resume Next
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
cnt = cnt + 1
Next

End Sub
 
F

Frazer

Ive done that exactly and still no luck....maybe its something wrong with my
settings, i dunno, but thanks for the help anyway
 
S

StinkeyPete

Use this code instead....

Sub Macro1()
Dim cnt As Integer
cnt = 1

For Each c In Worksheets("Sheet3").Range("C1:C70").Cells
Range("C" & cnt).Select
On Error Resume Next
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
cnt = cnt + 1
Next

End Sub
 
Top