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 Options> Advanced> Update Links To Other Documents, but that doesn't work either. Is there a way to change from Absolute Link to Relative Link in mass using a macro or something?

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    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

      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

        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