Hyperlinks Index – Get all links with address, cell – Whole Workbook

Get list of all Hyperlinks in Excel file

This macro produces a hyperlink index sheet.

To get list of hyperlinks in each worksheet, the Vba code in this page follow these steps.

1. Add a new sheet for Output
2. Loop through each Worksheet
3. Loop thru each hyperlink
4. Get hyperlink address, path, cell etc.,
5. Print hyperlink details to output sheet.

Here is the vba code.

Sub ListAllHyperlinks()
    'Get list of all hyperlink in Workbook - officetricks
    Dim hLink As Hyperlink  'Hyperlink Object
    Dim iSh As Worksheet    'Input Worksheet
    Dim oSh As Worksheet    'Output Worksheet
    Dim oRow As Double      'Output row index
    
    'Create a new Worksheet
    Set oSh = ThisWorkbook.Sheets.Add
    
    'Loop thru Each sheet
    oRow = 0
    For Each iSh In ThisWorkbook.Sheets
        
        'Loop Thru each Hyperlink
        For Each hLink In iSh.Hyperlinks
            oRow = oRow + 1
            oSh.Cells(oRow, 1) = iSh.Name
            oSh.Cells(oRow, 2) = hLink.Range.Address
            oSh.Cells(oRow, 3) = hLink.Name
            oSh.Cells(oRow, 4) = hLink.Address
        Next
    Next
    
    'Autoalign Column Width
    oSh.Columns.AutoFit
End Sub

Find all Hyperlinks with Address, Cell name in whole Workbook

List hyperlinks address in whole workbook with cell address.

This macro not only lists the hyperlinks address and name, but also in creates an index for all the hyperlinks present in the sheet, along with sheet name, cell range.

So, it will be very usefull if your Excel file has too many hyperlinks. This way it is easy to maintain all the links.