Open Multiple Hyperlinks

[How_Do_I]

Hello all, I have a workbook that contains multiple hyperlinks in a column... I've looked online but none of the VBA code that I've found will open my links... An ideas please?

A working hyperlink from my workbook is: "=HYPERLINK(INDEX(List!B:B,MATCH(D11,List!C:C,0)),D11)"

As I mentioned, on this particular worksheet all the hyperlinks are in the same column and all the hyperlinks open .pdf files...

 

[COwen]

Here you go. I do not fully understand what it is you are trying to do with the Index/Match functions.

Sub Open_Link()
Dim iCell As Range

For Each iCell In Range("A2:A1000")
If Not IsEmpty(iCell) Then
ThisWorkbook.FollowHyperlink iCell.Value
End If
Next iCell

End Sub

 

[How_Do_I]

Here you go. I do not fully understand what it is you are trying to do with the Index/Match functions.

Thanks for that COwen, the Index and Match are getting the File Path from a different sheet... If I use your code on the cells contain the File Path it works...

I'll have to think about amending my workbook but is there a way of specifying the range that I want to open please?

 

[COwen]

What is the range you want to open?

 

[COwen]

If I use your code on the cells contain the File Path it works..

Yes, for my code to work, the cell needs to contain the full file path.

 

[COwen]

If the cell does not contain the file path, but all the files are in the same folder, then you can use code like:

Sub Open_Link()
Dim iCell As Range

For Each iCell In Range("A2:A1000")
If Not IsEmpty(iCell) Then
ThisWorkbook.FollowHyperlink "C:\Users\Documents\" & iCell.Value & ".pdf"
End If
Next iCell

End Sub

 

[COwen]

The range (in this case) is A2:A1000. You could make the same range dynamic, if you like, with the following formula. First, go to the formulas tab, and click on name manager. Click "New..." and then enter a name (e.g. LinkCells) then copy and paste the following formula: =OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A$2:$A$1000),1). The "1" at the end of the formula is the number of columns to the right of your selection (which should contain only one column [e.g. A, B, C...]). If you would like to make the range A2:B1000, the formula follows: =OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A$2:$A$1000),2).

The syntax to reference this in the code is "...For Each iCell In Range("[Name]")...

 

[How_Do_I]

What is the range you want to open?

Hello COwen… I used your code and something else I found... That is working however the issue I have now is I'm using a filter to get the exact files that I want to open however, the code is opening all the files... So if the full pathname is in B7 and the last one in B10 due to the filter, the code is opening B7, B8, B9 and B10...

This is the code I'm using:


Sub Test_Open_Link()
Dim WorkRng As Range
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each iCell In WorkRng
If Not IsEmpty(iCell) Then
ThisWorkbook.FollowHyperlink iCell.Value
End If
Next iCell
End Sub

 

[COwen]

Sure. The code is opening all of the cells in the specified range. I will work on something for filtered cells.

 

[COwen]

Do you want to use an inputbox, or is that an attempt to work around the filtered cells?