Forum Discussion

CompellingExcelling's avatar
CompellingExcelling
Copper Contributor
Feb 01, 2024

Mass Change All Hyperlinks To Relative Hyperlinks

I have a spreadsheet that has over 10,000 hyperlinks to drawing files. If I move that file it changes all of the hyperlinks. How do I prevent the links from changing. I've tried unchecking the Option...
Excel
Formulas and Functions
NikolinoDE's avatar
NikolinoDE
Gold Contributor
Feb 02, 2024

CompellingExcelling 

Maybe you can try to use a VBA (Visual Basic for Applications) macro to change all hyperlinks in your Excel spreadsheet from absolute to relative links.

Here is a sample macro that you can use:

 

Sub ConvertToRelativeHyperlinks()
    Dim ws As Worksheet
    Dim hyperlink As Hyperlink
    Dim oldAddress As String
    Dim newAddress As String

    ' Loop through all worksheets in the workbook
    For Each ws In ThisWorkbook.Sheets
        ' Loop through all hyperlinks in the worksheet
        For Each hyperlink In ws.Hyperlinks
            ' Store the old hyperlink address
            oldAddress = hyperlink.Address

            ' Convert the absolute hyperlink to a relative one
            newAddress = RelativePath(ThisWorkbook.Path, oldAddress)

            ' Update the hyperlink address
            hyperlink.Address = newAddress
        Next hyperlink
    Next ws
End Sub

Public Function RelativePath(ByVal SwitchToRelative As String, ByVal AbsolutePath As String) As String
    ' The code for the RelativePath function you provided goes here
    ' ...
End Function

 

Please follow these steps to use the macro:

  1. Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
  2. Insert a new module by right-clicking on any item in the left pane (e.g., "VBAProject (YourWorkbookName)") and selecting Insert > Module.
  3. Copy and paste the provided VBA code into the module.
  4. Close the VBA editor.

Now, you can run the macro by pressing Alt + F8, selecting the makro, and clicking Run. This macro will convert all absolute hyperlinks to relative hyperlinks based on the file's current location.

Make sure to save your workbook before running the macro in case you need to revert any changes.

 

Add. Information:

  • Relative path of the SwitchToRelative argument. If the path already has a . (period) begins, no change is made.
  • Relative paths can only be created if both paths are on the same drive.
  • The function does not check whether the paths actually exist.

 

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.

  • CompellingExcelling's avatar
    CompellingExcelling
    Copper Contributor
    Feb 02, 2024

    NikolinoDE 

    Thank you for your response and help. I made a test copy and added the Macro Module using VBA editor. Once I ran the Macro it basically broke all links and shortened the link to the parent folder. All files are in child folders. Is there something in the code that needs changing to cover all sub-folders?

    • NikolinoDE's avatar
      NikolinoDE
      Gold Contributor
      Feb 02, 2024

      CompellingExcelling 

      Let’s try to make a modification to the code to ensure that it considers subfolders as well. We will use the FileSystemObject to work with folder paths.

      Please replace the existing VBA code with the modified version below:

      Vba code is untested, please backup your file.

      Sub ConvertToRelativeHyperlinks()
    Dim ws As Worksheet
    Dim hyperlink As Hyperlink
    Dim oldAddress As String
    Dim newAddress As String

    ' Loop through all worksheets in the workbook
    For Each ws In ThisWorkbook.Sheets
        ' Loop through all hyperlinks in the worksheet
        For Each hyperlink In ws.Hyperlinks
            ' Store the old hyperlink address
            oldAddress = hyperlink.Address

            ' Convert the absolute hyperlink to a relative one
            newAddress = ConvertToRelativePath(ThisWorkbook.Path, oldAddress)

            ' Update the hyperlink address
            hyperlink.Address = newAddress
        Next hyperlink
    Next ws
End Sub

Public Function ConvertToRelativePath(ByVal basePath As String, ByVal absolutePath As String) As String
    Dim relativePath As String
    Dim baseFolder As Object
    Dim targetFolder As Object

    ' Create FileSystemObject
    Set baseFolder = CreateObject("Scripting.FileSystemObject")
    Set targetFolder = CreateObject("Scripting.FileSystemObject")

    ' Get the base folder and target folder paths
    baseFolderPath = baseFolder.GetAbsolutePathName(basePath)
    targetFolderPath = targetFolder.GetAbsolutePathName(absolutePath)

    ' Check if both paths are on the same drive
    If Left(baseFolderPath, 1) = Left(targetFolderPath, 1) Then
        ' Create a relative path using the relative parts of the two paths
        relativePath = Replace(targetFolderPath, baseFolderPath & "\", "")
    Else
        ' Paths are on different drives, return the absolute path
        relativePath = absolutePath
    End If

    ' Release objects
    Set baseFolder = Nothing
    Set targetFolder = Nothing

    ' Return the relative path
    ConvertToRelativePath = relativePath
End Function

      This modified code uses the ConvertToRelativePath function to convert absolute paths to relative paths. It takes into account the base path and target path, ensuring that they are on the same drive before attempting to create a relative path.

      Please try running this updated code, and it should handle subfolders correctly. As always, make sure to save your workbook before running the macro.

Resources