Mass Change All Hyperlinks To Relative Hyperlinks

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.