Как восстановить гиперссылки после ошибки в excel

Are hyperlinks not opening or working in your Excel workbooks? In Excel, hyperlinks are used to open specific documents, files, folders, or web addresses saved on a network server, intranet, or web. It also creates a shortcut to quickly jump to another worksheet or location in the current workbook.

How do I enable hyperlinks in Excel?

To insert hyperlinks in Excel, you can click on the Insert > Link option and then enter the address of the target file/folder. You can also press the Ctrl+K hotkey by selecting the target cell and then quickly insert a hyperlink to a file or folder. However, some Excel users have reported that the hyperlinks won’t open the target file or folder in Excel.

Why can’t I open hyperlinks in Excel?

There can be different reasons why you are unable to open hyperlinks in an Excel workbook. One of the most common reasons is that the file to which the hyperlink navigates you to doesn’t exist anymore or is moved to some other location. It might also be the case that the target file is renamed. Another common reason for hyperlinks not working or opening is that the name of the target file consists of a pound symbol (#).

Sometimes, the enabled Update links on save option can also cause this issue. Besides that, if your Excel workbook or the Excel program itself is corrupted, you will face issues like this.

Now, if you are one of the affected users for whom the Excel hyperlinks are not opening, the first thing you should do is make sure that that target file exists. You can also open the target file in some other application and make sure it is not corrupted. If you still face the same issue, we got you covered. Here, we will be discussing all the working fixes that have helped affected users get rid of the problem. You can use a suitable solution to fix the issue.

If hyperlinks are not opening or working in your Excel workbook, here are the solutions you can use to fix the problem:

1. Recreate or edit the hyperlink.
2. Rename the target file and delete the pound sign.
3. Disable the Update links on save option.
4. Repair the corrupted Excel file.
5. Repair Microsoft Excel.

1] Recreate or edit the hyperlink

It might be the case that the target file has been changed or moved to some other location, or it has been renamed. And thus, you are unable to open the links in your worksheet. Now, if the scenario is applicable to you, simply recreate or modify the hyperlink accordingly.

To edit the existing hyperlink, here are the steps to follow:

• First, right-click on the problematic hyperlink.
• From the appeared context menu, choose the Edit hyperlink option.
• Next, in the address bar, provide the new location of the file or enter the new filename with its exact path.
• Once done, press the OK button and check if the problem is resolved.

In case you want to recreate the entire hyperlink, right-click on the hyperlink and select the Remove hyperlink option from the right-click menu. Once the link is deleted, click on the Insert > Link option and then enter the address of the file to which you want to add a link.

If this method doesn’t work for you and the Excel hyperlinks still don’t open, move on to the next potential solution.

Read: Arrow keys are not working in Microsoft Excel.

2] Rename the target file and delete the pound sign

Another potential cause of the issue is that the file to which you have inserted a hyperlink contains a pound sign (#). It is a valid character for the filename otherwise but not for hyperlinks in Office documents like Excel workbooks. You may get the following error message when trying to open the hyperlink:

Cannot Open the Specified File.

So, if this scenario is applicable to you, rename the target file and remove the pound sign from its name. Open File Explorer using Win+E, move to the location where the file is saved and right-click on the file. Now, select the Rename option and then enter a name without a pound sign (#). Once done, open Excel, right-click on the problematic link and choose the Edit hyperlink option. After that, browse and select the renamed file or paste its exact address to link to it in the Address box, and press the OK button to save changes.

You need to repeat the same steps as mentioned above for other non-working hyperlinks in your workbook. Hopefully, the problem will be resolved now. If not, we have some more workings fixes to resolve the issue. So, move on to the next one.

See: Excel: The File couldn’t open in Protected View.

3] Disable the Update links on save option

Microsoft Excel provides an option to update the hyperlinks whenever the workbook is saved. It is a handy function but may cause the “Excel hyperlinks not working/opening” issue. Hence, you can simply turn off the Update links on save option and then see if the problem is resolved. This fix has been proven to be effective for some users.

Here’s how to do that:

• First, open Excel and move to the File menu.
• Now, click on Options and navigate to the Advanced tab.
• Next, scroll down to the General section and press the Web Options button.
• In the opened dialog window, go to the Files tab and untick the checkbox naled Update links on save.
• After that, press the OK button to save changes.

Your hyperlinks should work properly now.

See: Fix Excel freezing, crashing, or not responding on Windows.

4] Repair the corrupted Excel file

It might be some corruption in your Excel workbook causing this issue. Hence, repair the corrupted Excel file and then see if the issue is resolved. To do that, you can either use Excel’s inbuilt Open and Repair tool or try a third-party Excel file repair tool.

Here’s how to repair a corrupted Excel workbook using Excel’s Open and Repair tool:

• First, close the problematic workbook and make sure to create a backup of the file in some other folder.
• Then, open Excel and click on the File menu.
• After that, choose the Open option and browse and select the problematic workbook.
• Next, click on the drop-down arrow button associated with the Open button and select the Open and Repair option.
• Now, press the Repair button (to recover most of your data) and follow the prompted instructions to complete the process.
• When done, check if the issue is resolved.

You can also use the OnlineFile.Repair tool to fix the damaged Excel workbook. See if this works for you.

Read: Excel is waiting for another application to complete an OLE action.

5] Repair Microsoft Excel

One more fix to resolve this problem is to repair your Office programs. It might be some corruption in the application itself which is causing the issue. Hence, repair Microsoft Office and its programs and see if the problem is resolved.

To do that, close all running Office programs, including Excel, and open Settings using Win+I. Now, go to Apps > Installed apps and click on the three-dot menu button present next to Microsoft Office. After that, press the Modify option and then choose Quick Repair or Online Repair option. Finally, press the Repair button and it will start repairing your Microsoft Office package. Once done, you can reopen Excel and hopefully, the hyperlinks will work fine now.

I hope this guide helps you get your hyperlinks to work properly in Excel.

Now read: Fix Excel Toolbar not working.

Макрос для исправление повреждённых гиперссылок во всей книге:

Sub ЗаменаИспорченныхГиперссылок()
    On Error Resume Next
    Dim hl As Hyperlink, oldString As String, newString As String, sh As Worksheet
    ' часть гиперссылки, подлежащая замене
    oldString = "C:Documents and settingsБухгалтерApplication data"
    ' на что заменяем
    newString = "\адрес_сервера"
    For Each sh In ActiveWorkbook.Worksheets    ' перебираем все листы в активной книге
        For Each hl In sh.Hyperlinks    ' перебираем все гиперссылки на листе
            If hl.Address Like oldString & "*" Then
                hl.Address = Replace(hl.Address, oldString, newString)
            End If
        Next
    Next sh
End Sub

Макрос может быть полезен для замены абсолютных гиперссылок на относительные, а также помогает вернуть работоспособность ссылок после случайного сохранения файла Excel в другой папке (на другом диске).

Если нужно заменить несколько вариантов неверных ссылок, код будет таким:

Sub ЗаменаИспорченныхГиперссылок_2()
    On Error Resume Next
    Dim hl As Hyperlink, newString$, sh As Worksheet
 
    ' часть гиперссылки, подлежащая замене
    oldString1 = "C:Documents and settingsБухгалтер1"
    oldString2 = "C:Documents and settingsБухгалтер2"
 
    ' на что заменяем
    newString = "\адрес_сервера"
 
    For Each sh In ActiveWorkbook.Worksheets    ' перебираем все листы в активной книге
        For Each hl In sh.Hyperlinks    ' перебираем все гиперссылки на листе
            If hl.Address Like oldString1 & "*" Then  hl.Address = Replace(hl.Address, oldString1, newString)
            If hl.Address Like oldString2 & "*" Then  hl.Address = Replace(hl.Address, oldString2, newString)            
        Next
    Next sh
 
End Sub

Расширенная версия этого макроса учитывает, что слеш в ссылках может быть как прямым, так и обратным, а также выводит информацию о количестве произведённых замен, и список ссылок из файла, которые не были обработаны (к которым замены не были применены)

Sub ЗаменаИспорченныхГиперссылок2()
    On Error Resume Next
    Dim hl As Hyperlink, oldString$, newString$, sh As Worksheet, n&, msg$, coll As New Collection, Item
 
    ' часть гиперссылки, подлежащая замене
    oldString = "../../AppData/Roaming/Microsoft/Excel/"
    ' на что заменяем
    newString = "C:UsersAdminDesktopОТЧЁТЫ ВСЕ"
 
    For Each sh In ActiveWorkbook.Worksheets    ' перебираем все листы в активной книге
        For Each hl In sh.Hyperlinks    ' перебираем все гиперссылки на листе
            ' Debug.Print hl.Address
            If (hl.Address Like oldString & "*") Or (hl.Address Like Replace(oldString, "/", "") & "*") Then
                hl.Address = Replace(hl.Address, oldString, newString, , , vbTextCompare)
                hl.Address = Replace(hl.Address, Replace(oldString, "/", ""), newString, , , vbTextCompare)
                n = n + 1
            Else
                If InStr(1, hl.Address, "mailto", vbTextCompare) = 0 Then coll.Add hl.Address, UCase(hl.Address)
            End If
        Next
    Next sh
 
    For Each Item In coll
        msg$ = msg$ & Item & vbNewLine
    Next
 
    MsgBox "Заменено гиперссылок: " & n & IIf(Len(msg$), vbNewLine & vbNewLine & _
                                                         "Также в файле найдены ссылки на:" & vbNewLine & msg$, ""), vbInformation
End Sub