Forum Discussion

tridgill's avatar
tridgill
Copper Contributor
Apr 14, 2021

Checking validity of hyperlinks in Excel

Hi there - I was just wondering if MS Excel has feature in which you can check the validity of hyperlinked documents, similar to the feature Adobe has in their hyperlinking plug-in which creates Link Reports? Just to ensure all the links are working in a spreadsheet.

 

I do a lot of hyperlinking in Adobe and Excel but cannot check if all the Excel links are working without clicking each one. 

excel

1 Reply

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Apr 15, 2021

    tridgill 

    How do I find broken links in Excel?

    There are different ways to check whether the hyperlinks are OK or not.
    Here are two quick methods.

     

    Method 1:
    Cell Relationship Diagram
    In the menu bar, select the Files> Options tab.
    In the opened Excel Options window, click the Add-ins tab.
    Now select the COM add-in from the drop-down list and press the OK button. In the open com add-in dialog box, choose Inquiries.
    After that, you will see the Inquiries tab is added to the Excel ribbon.
    In the diagram group, you need to select Cell Relationship.
    The next window of the cell relationship diagram appears on the screen.
    In the opened dialog box, select the options you want and press the OK button.
    Very soon the diagram will appear on your Excel screen.
    You can zoom in to see the links between a cell and another cell, or between a cell and another worksheet.
    Here you can see the entire cell relationship diagram with all the links included. You can also use this chart to review the broken links in your Excel workbook.
    I found this method to be the best way to find the cell with the broken Excel links.

     

    Method 2:
    Using Excel VBA
    Another method to find broken Excel link is by using Excel VBA. In this method, we use the massive code to trace the broken link to an Excel workbook cell.

    You can also restore the return value of the accessing source links to determine whether the links are actually broken or not.

    To use the code, you need to add the Microsoft XML V3 reference:

     

    Private Function CheckHyperlink(HypelinksCell As Range) As String

On Error GoTo ErrorHandler

Dim oHttp As New MSXML2.XMLHTTP30

oHttp.Open “HEAD”, HypelinksCell.Text, False

oHttp.send

CheckHyperlink = oHttp.Status & ” ” & oHttp.statusText

Exit Function

ErrorHandler:

CheckHyperlink = “Error: ” & Err.Description

End Function

Sub Test()

Debug.Print CheckHyperlink(Range(“A1”))

End Sub

     

    With the above code, you can easily keep track of the broken link details of each worksheet cell.

    Once you know the exact location of the broken link, move on to fixing the broken Excel link.

     

    How do I fix broken links in Excel?
    Warning: This step cannot be undone. So it is better that you keep the backup of your Excel workbook before starting the process.

    Open your Excel workbook with the broken links problem.
    Go to the Data tab and tap on the Edit Links option. If you don't get the "Edit Links" option, it means there are no links in your workbook.
    Now select the broken link that you want to fix in the Source file field.
    Click the Change Source option.
    Find the location of the Excel file with the source of your linked data.
    Now select the new source file and click on the Change Source option.
    Tap the Close option.

     

    Hope I was able to help you with this information from the internet.

     

    Nikolino

    I know I don't know anything (Socrates)

     

    * Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

Resources