List all hyperlinks in worksheet programmatically

8 Responses to “List all hyperlinks in worksheet programmatically”

1. Rick Rothstein (MVP - Excel) says:

   April 7, 2015 at 5:37 pm

   I have not had the occasion to work with Hyperlinks in the past, so I cannot be 100% sure this works perfectly, but in my tests, your code and my code seem to return identical results every time, so I have a lot of confidence in my macro. My code has less active lines of code than yours and I believe it will execute slightly faster as it does not look at every cell in the UsedRange, rather, it first looks directly at hyperlinks only, and then it looks at all formulas on the sheet testing them to see if they start with "=HYPERLINK(" or not. Also, my code works slightly different than yours, it is sort of a combination of your two macros. To use it, you first select the sheets you want to process, so if you want a single sheet, select it... if you want only a few sheets, Control+Click the tabs for the sheets you want... and if you want all the sheets, select the first sheet and then Shift+Click the last sheet. After you have selected the sheets you want to process, then run this macro.

   Sub ListHyperlinksToo()
     Dim N As Long, LastRow As Long, StartCells As Range, Cell As Range
     Dim NewSheet As Worksheet, WS As Worksheet, HypLnk As Hyperlink, SS As Object
     On Error GoTo NoHyperlinks
     Set SS = ActiveWindow.SelectedSheets
     Sheets(1).Select
     Set NewSheet = Sheets.Add
     Set StartCells = NewSheet.Range("A1:C1")
     StartCells = Array("Worksheet", "Address", "Hyperlink")
     StartCells.Font.Bold = True
     For Each WS In SS
       For Each HypLnk In WS.Hyperlinks
         N = N + 1
         StartCells.Offset(N) = Array(WS.Name, HypLnk.Range.Address, HypLnk.Address)
       Next
       N = N + 1
       For Each Cell In WS.Cells.SpecialCells(xlFormulas)
         If Left(Cell.Formula, 11) = "=HYPERLINK(" Then
           StartCells.Offset(N) = Array(WS.Name, Cell.Address, Split(Cell.Formula, """")(1))
           N = N + 1
         End If
       Next
     Next
     LastRow = Cells(Rows.Count, "A").End(xlUp).Row
     StartCells.EntireColumn.Sort Range("A2:A" & LastRow), xlAscending, Range("B2:B" & LastRow), , xlAscending, Header:=xlYes
   NoHyperlinks:
   End Sub
   

   Reply
   • Rick Rothstein (MVP - Excel) says:

     April 7, 2015 at 6:07 pm

     Sorry, the above code does not correctly handle interim selected sheets that contain no hyperlinks (it ends the macro too soon). This revised macro does work correctly for those situations, so use it instead of what I posted earlier...

     Sub ListHyperlinksToo()
       Dim N As Long, LastRow As Long, StartCells As Range, Cell As Range
       Dim NewSheet As Worksheet, WS As Worksheet, HypLnk As Hyperlink, SS As Object
       Set SS = ActiveWindow.SelectedSheets
       Sheets(1).Select
       Set NewSheet = Sheets.Add
       Set StartCells = NewSheet.Range("A1:C1")
       StartCells = Array("Worksheet", "Address", "Hyperlink")
       StartCells.Font.Bold = True
       On Error GoTo NoHyperlinks
       For Each WS In SS
         For Each HypLnk In WS.Hyperlinks
           N = N + 1
           StartCells.Offset(N) = Array(WS.Name, HypLnk.Range.Address, HypLnk.Address)
         Next
         N = N + 1
         For Each Cell In WS.Cells.SpecialCells(xlFormulas)
           If Left(Cell.Formula, 11) = "=HYPERLINK(" Then
             StartCells.Offset(N) = Array(WS.Name, Cell.Address, Split(Cell.Formula, """")(1))
             N = N + 1
           End If
         Next
     Continue:
       Next
       LastRow = Cells(Rows.Count, "A").End(xlUp).Row
       StartCells.EntireColumn.Sort Range("A2:A" & LastRow), xlAscending, Range("B2:B" & LastRow), , xlAscending, Header:=xlYes
       Exit Sub
     NoHyperlinks:
       Resume Continue
     End Sub
     

   • Oscar says:

     April 8, 2015 at 8:15 am

     Rick Rothstein (MVP - Excel)

     Thank you for your contribution. Always interesting to read your comments.

2. Yoma Ma says:

   October 14, 2017 at 8:18 pm

   I have a simple question:

   Why post code that has undeclared variables? Most of the time I can figure out what they should be declared as, but in some cases I don't even know that a certain type of variable exists. For example, your variable "lnk" should be declared as...?

   Again, my main point is, as a programmer who writes vba code to publish, why publish code that does not work simple because the variables are not declared and not everyone is going to know what to declare them as??

   I'd say 'Thanks for the code', but I don't believe that no respectable programmer would make the mistake of coding without variable declarations.

   Reply
   • Oscar says:

     October 16, 2017 at 8:55 am

     Yoma Ma,

     Undeclared variables are automatically variants, I can declare them for you but it won't make any difference except that your macro grows a line or more.
     If you think the variables will slow down your workbook feel free to change them accordingly.

3. Mirek says:

   September 26, 2019 at 8:25 am

   Hi Oscar, thank you for this article. It is really useful for me. I have just one question. Does exist a way how to update links based on this result? I have an excel where I have a lot of links but we migrated data to different place. So it means that URL address was changed. I used your macro to get all links, I updated all these to the new destination and now I am looking for a way how to update all cells when I have list of all cells where with a new link. Thank you for your tips :) Mirek

   Reply
   • Oscar says:

     September 27, 2019 at 12:42 pm

     Mirek

     You can use "Find & Replace" to update your cells with new hyperlinks.

     1. Press CTRL + H to open the "Find & Replace" dialog box.
     2. Find what: Old url
     3. Replace with: New url
     4. Click "Replace All" button to apply changes to all cells on worksheet.

4. RRK says:

   February 3, 2020 at 12:40 pm

   Hello Oscar,
   thank you for this article. It is really useful for me. I am looking for a macro which can search hyperlinks from many excel files which are in a folder. how I should amend this macro to search for files within a folder and give me file name and hyperlink name. Thank you in advanced.

   Reply

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes &lt; and > becomes &gt;

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form