A little help opening a range of hyperlinks..

[Ste_Moore01]

Hi,

I've made myself a little code..

Sub Macro1()
    Dim HyperlinkRange As Range
    Set HyperlinkRange = Range("A5:A65536")
    For Each c In HyperlinkRange
    If c.Value <> "" Then ActiveWorkbook.FollowHyperlink c
    Next c
End Sub

It looks in the range A5:A65536 to find the Hyperlinks, and when it finds them it should open them.

The problem is, it finds the first Hyperlink and opens it and then stops because it's in a new workbook.

I know I need to add in the code to get back to my original workbook which would be..

Windows("FILES.xls").Activate

But I'm unsure as where to put it as I've tried putting it before the "Next c" and bit and after.

Does anyone have any suggestions as I'm quite new to this.

Thanks!

 

[GlennUK]

Put Thisworkbook.Activate before the Next.

By the way, please put a subject that's more meaningful than A little help .. next time you post.

 

[Ste_Moore01]

Sorry, I didn't really know what to call it.

Thanks for the reply but that code didn't seem to fix it, it just does the same thing.

This is the code now

Sub Macro1()
    Dim HyperlinkRange As Range
    Set HyperlinkRange = Range("A5:A65536")
    For Each c In HyperlinkRange
    If c.Value <> "" Then ActiveWorkbook.FollowHyperlink c
    ThisWorkbook.Activate
    Next c
End Sub

**EDIT**

I've editted the title now.

 

[GlennUK]

Excuse me, I've just read your posting properly ( now that you've got a proper subject title, it caught my eye ) ... and want to know what logic you are trying to follow? Activating a hyperlink "goes" to the subject in question ... so what is the point of a loop "going" to subject after subject, when you are wishing to return to your own workbook after each iteration of the loop? Maybe you ought to be thinking of just opening workbooks from a list instead?

 

[Ste_Moore01]

I was hoping to open a series of hyperlinks that are in a table so it updates some information in another part of the table.

I can't put in specific cell data saying follow the hyperlink in A5 & A6 because my table automatically makes the hyperlinks and the number of hyperlinks will increase over time.

All I really want is for my table to be updated and without certain workbooks being open they don't update, so I was think that maybe if the workbooks automatically opened using a macro and then closed when the data had updated.

 

[GlennUK]

So you don't need the hyperlink in order to "go" to a file, you need to simply open a file ... except you've also said:

All I really want is for my table to be updated and without certain workbooks being open they don't update, so I was think that maybe if the workbooks automatically opened using a macro and then closed when the data had updated.
[/code]

What do you mean "without certain workbooks being open they don't update"? What doesn't update exactly? If you are referring to external links, then when your workbook opens you should be able to choose to Update External links when prompted ... so I can't really guess what you are on about.

 

[Ste_Moore01]

I use the Indirect code to work out the filenames and paths and the Indirect code doesn't update data when the other workbooks are closed.

That's why I was creating this code to open each hyperlink so it updated the information until I either refreshed the file or closed the workbook.

 

[GlennUK]

So, if this is just to open the file, simply use the Open method on the filename instead of "going" to the file via a hyperlink.

 

[Ste_Moore01]

I doesn't matter whether I use the open method or the hyperlink method as I still need the macro to go back to the original workbook so it can open the next file.

The hyperlink method and open method both act the exact same way.

 

[GlennUK]

No they don't act the same way! Your macro will be in control after opening another workbook, and can activate whatever book is required after performing that action. Totally different to following a hyperlink.

Did you actually try this? Or did you make assumptions?

This is the code I've just used to confirm that I'm right:

Workbooks.Open "Basis Adjustment.xls"
ThisWorkbook.Activate