Hyperlinks in Excel are changing path

Acq3Z 

Since the PDF files cannot be stored in the same folder as the Excel file, here are some effective methods to ensure that hyperlinks in Excel point to the correct PDF locations without breaking:

All suggestions were created using AI, and not every suggestion is guaranteed to work for your topic. The additional links contain some information that might also help you.

1. Use Absolute Paths in Hyperlinks

• Rather than relying on relative paths (which change based on where the Excel file is stored), use absolute paths that specify the full location of the PDF files.
• For example, instead of a relative path like ..\PDFs\file.pdf, use an absolute path like C:\Server\PDFs\file.pdf or \\ServerName\SharedFolder\PDFs\file.pdf.
• To update the links:
1. Right-click on the hyperlink in Excel.
2. Select Edit Hyperlink.
3. Enter the absolute path of the PDF file.

2. Use UNC (Universal Naming Convention) Paths

• If the PDFs are stored on a network or server, you can use a UNC path rather than relying on a specific drive letter (e.g., C:). This makes the link more reliable, especially if different users are accessing the Excel file from different locations.
• Example of a UNC path: \\ServerName\Folder\SubFolder\file.pdf.
• Replace drive letters with network paths in the hyperlink for a more stable solution.

3. Mapped Network Drive

• Another approach is to map the server folder containing the PDFs as a network drive. For example, you can map the server folder to a drive letter like Z: on all computers.
• Then, link to the PDFs using the Z:\folder\file.pdf format. This ensures that all users have the same path to the PDFs.
• To map a drive:
1. Open File Explorer, right-click on This PC, and select Map network drive.
2. Choose a drive letter (e.g., Z:) and browse to the folder on the server where the PDFs are stored.
3. After mapping, update all the Excel hyperlinks to use the new mapped drive (e.g., Z:\PDFs\file.pdf).

4. Disable “Update Links on Save”

• Sometimes, Excel tries to adjust the links automatically based on where the Excel file is saved. To prevent Excel from changing the paths:
1. Go to File > Options.
2. Select Advanced from the menu.
3. Scroll down to the General section.
4. Uncheck Update links on save.
• This ensures that Excel doesn’t change your hyperlinks automatically when saving.

5. Use VBA to Fix Broken Hyperlinks in Bulk

• If many hyperlinks are already broken and need to be updated, using a VBA macro can save you time. A macro can automatically find and replace broken links with the correct paths.
• Here's a sample VBA code that you can use to update all hyperlinks to point to the correct PDF location:

  The VBA code is untested and serves as an example only, please backup your file in advance as a precaution.

Sub UpdateHyperlinks()
    Dim ws As Worksheet
    Dim hl As Hyperlink
    For Each ws In ThisWorkbook.Sheets
        For Each hl In ws.Hyperlinks
            ' Update with your correct path
            hl.Address = Replace(hl.Address, "C:\OldPath\", "C:\NewPath\")
        Next hl
    Next ws
End Sub​

• This script replaces an old path with the correct new path across all hyperlinks in the workbook. Adjust "C:\OldPath\" and "C:\NewPath\" to your specific folder paths.

6. Consider a Link Tracker Tool

• If the file paths change frequently, consider using an Excel Add-in or a third-party link manager that helps track and update broken links in bulk.

7. Keep the PDF Path Short

• If possible, try to keep the paths to your PDFs relatively short and avoid deeply nested folder structures. Excel can sometimes truncate or misinterpret very long paths, leading to broken links.

By following one or more of these strategies, you should be able to maintain stable hyperlinks between your Excel file and the PDF files stored in different folders.

In the end, however, without more information about your specific situation, it's difficult to say for sure that this will solve the problem. Try changing the steps as suggested and see if that fixes the problem. The text was created with the help of AI.

Additional Links:

How to reference a cell in another workbook without storing absolute path to source file

Get Excel File local absolute path instead of OneDrive Url

2.2.57 UNC

How to open a file from an absolute path without having to save a copy

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.