VBA & Macro Problems

Convert Absolute Excel Hyperlinks to Relative Links in Bulk

Ayan MasoodAyan Masood Nov 12, 2026 870 views

Question details

Convert thousands of absolute file hyperlinks in a workbook into relative hyperlinks in bulk.

Convert Absolute Excel Hyperlinks to Relative Links in Bulk
Product
Microsoft Excel workbook hyperlinks and VBA macros
Device & OS
not provided
Scenario
The workbook and its linked files will be moved together, and the user wants the links to continue opening the correct files.
Observed behavior
Absolute hyperlinks contain fixed drive and folder locations, so they can break after the workbook and linked files are relocated.
Before you start

Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm) and test the macro on a copy first. Relative paths work only when the workbook and target files are stored on the same drive; place them in their intended folder structure before running the macro.

Solution 1Recommended

Run a VBA macro to convert local absolute hyperlinks to relative paths

Use this method when you need to update hyperlinks across all worksheets at once. The macro skips web links and converts only local drive-based file paths.

The macro compares each local file hyperlink with ThisWorkbook.Path and replaces the absolute address with a path relative to the workbook folder. Hyperlink display text and SubAddress values are left unchanged.

Links to files on a different drive cannot be made relative. Those links remain absolute so they continue to point to their original location.

1
Open the Visual Basic Editor

In Excel, open the workbook and press Alt+F11. In the Project pane, right-click your workbook project, then select Insert > Module.

2
Paste the conversion macro

Paste this code into the new module: Option Explicit Sub ConvertAbsoluteLinksToRelative() Dim ws As Worksheet Dim link As Hyperlink Dim oldAddress As String For Each ws In ThisWorkbook.Worksheets For Each link In ws.Hyperlinks oldAddress = link.Address If IsLocalDrivePath(oldAddress) Then link.Address = RelativePath(ThisWorkbook.Path, oldAddress) End If Next link Next ws MsgBox "Local file hyperlinks have been converted.", vbInformation End Sub Private Function IsLocalDrivePath(ByVal filePath As String) As Boolean IsLocalDrivePath = (Len(filePath) >= 3 And Mid$(filePath, 2, 2) = ":\") End Function Private Function RelativePath(ByVal baseFolder As String, ByVal targetPath As String) As String Dim baseParts() As String, targetParts() As String Dim i As Long, j As Long, lastCommon As Long Dim result As String baseFolder = Replace(baseFolder, "/", "\") targetPath = Replace(targetPath, "/", "\") Do While Len(baseFolder) > 3 And Right$(baseFolder, 1) = "\" baseFolder = Left$(baseFolder, Len(baseFolder) - 1) Loop baseParts = Split(baseFolder, "\") targetParts = Split(targetPath, "\") If LCase$(baseParts(0)) <> LCase$(targetParts(0)) Then RelativePath = targetPath Exit Function End If lastCommon = -1 For i = 0 To Application.Min(UBound(baseParts), UBound(targetParts)) If LCase$(baseParts(i)) <> LCase$(targetParts(i)) Then Exit For lastCommon = i Next i For j = lastCommon + 1 To UBound(baseParts) result = result & "..\" Next j For j = lastCommon + 1 To UBound(targetParts) result = result & targetParts(j) If j < UBound(targetParts) Then result = result & "\" Next j RelativePath = result End Function

3
Run the macro and verify sample links

Press Alt+F8, select ConvertAbsoluteLinksToRelative, and choose Run. Return to the worksheet, right-click several converted links, and select Open Hyperlink to confirm that files open correctly from the new folder structure.

4
Save the converted workbook

Use File > Save to keep the updated links. Keep the workbook in the same relative position to its linked folders; moving only one file or folder can still break a relative hyperlink.

Run a VBA macro to convert local absolute hyperlinks to relative paths
What the macro converts: The macro converts paths such as C:\Project\Files\Report.pdf into relative paths such as ..\Files\Report.pdf when both locations are on the same drive.
Work with spreadsheet hyperlinks in WPS Office

Manage Relative File Hyperlinks with WPS Spreadsheet

WPS Spreadsheet provides a familiar spreadsheet workspace for editing hyperlink addresses and working with macro-enabled workbooks. Open your workbook, use the Developer tools to run an available VBA macro, and verify that the converted relative links still open after the project folder is moved.

  1. 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, select Spreadsheets, and open the .xlsm or .xlsx workbook containing the hyperlinks.
  2. 2. Enable the Developer tools if needed: Open Menu > Options and enable the Developer tab or macro-related tools available in your WPS Office installation.
  3. 3. Add or run the macro: Open Developer > Visual Basic, insert a module, paste the hyperlink conversion macro, and run it from Developer > Macros.
  4. 4. Check converted hyperlinks: Select a linked cell and use the hyperlink command or Ctrl+Click to confirm that each relative file path opens from the workbook's current folder.
Edit and test file hyperlinks directly in WPS Spreadsheet.Use relative paths to keep linked project files portable within the same drive.Open and save Microsoft Excel .xlsx and macro-enabled .xlsm workbook formats.Familiar spreadsheet commands make it easy to edit hyperlink addresses and formulas.Lightweight office tools for organizing linked files and workbooks.
microsoft office alternative - wps office

Frequently Asked Questions

Can Excel hyperlinks use relative paths?

Yes. In the Edit Hyperlink dialog, enter a path relative to the workbook folder, such as ..\Documents\Invoice.pdf. The workbook and linked item must keep the same folder relationship after they are moved.

Why does a relative hyperlink still not work after I move the workbook?

Check that the linked file moved with the workbook and that the relative folder structure is unchanged. Also confirm that both items are on the same drive, because relative paths cannot link across different drive letters.

Will the VBA macro change website hyperlinks?

No. The provided macro checks for local drive paths in the form C:\Folder\File and skips web addresses such as https://www.example.com.

Do I need to save the workbook as XLSM to run the macro?

Yes. Choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm) before keeping VBA code. Saving as .xlsx removes the VBA project.