Replace External Links With Values

This code replaces all external links with their values, changes the colour of the background cell (so that you can see where), and places the original link path in a comment. If a comment already existed, it appends the original comment content to the bottom of the comment, and changes the comment colour as well.

Where to place the code:

This code goes in a standard module.

Code required:

Option Explicit
'Place these lines at the top of the module, right under the Option Explicit statement
Private Const lLinkedCommentColor As Long = 65280
Private Const lLinkedCellColor As Long = 4

Sub CementExternalLinks()
'Author       : Ken Puls (
'Macro Purpose: To cement external links with their values, change the cell colour to show it has
'               been cemented and place the original link in a comment

    Dim wks As Worksheet
    Dim strNote As String
    Dim rngSearch As Range
    Dim cl As Range

    'Optimize for speed
    Call Environ_SpeedSetting

    'Loop through each sheet
    For Each wks In ActiveWorkbook.Worksheets

        'Inform the user of progress
        Application.StatusBar = "Searching " & wks.Name

        'Set the range to search
        On Error Resume Next
        Set rngSearch = wks.UsedRange.SpecialCells(xlCellTypeFormulas)

        If Not Err.Number = 1004 Then
            'Search only cells with formulas
            On Error GoTo 0
            For Each cl In rngSearch

                'If the formula contains ".xls]", it must link to an external sheet
                If InStr(1, cl.Formula, ".xls]") > 1 Then
                    With cl
                        strNote = "Was linked from:" & vbNewLine & .Formula
                        On Error Resume Next
                        If Err.Number = 1004 Then

                            'Comment exists, so append original contents to new comment
                            'and change comment colour to make it obvious
                            With .Comment
                                strNote = strNote & vbNewLine & .Text
                                .Shape.Fill.ForeColor.RGB = lLinkedCommentColor
                            End With
                        End If

                        On Error GoTo 0

                        'Hide comment by default, and place the text with orignal link
                        With .Comment
                            .Visible = False
                            .Text Text:=strNote
                        End With

                        'Cement the value and change the cell colour
                        .Value = .Value
                        .Interior.ColorIndex = lLinkedCellColor
                    End With
                End If
            Next cl
            'No formulas in sheet
            On Error GoTo 0
        End If
    Next wks

    'Reset the working environment
    Call Environ_RestoreSettings
End Sub

Private Sub Environ_SpeedSetting()
'Author       : Ken Puls (
'Macro Purpose: Speed up the working environment
    With Application
        .ScreenUpdating = False
    End With
End Sub

Private Sub Environ_RestoreSettings()
'Author       : Ken Puls (
'Macro Purpose: Restore the working environment
    With Application
        .ScreenUpdating = True
        .StatusBar = False
    End With
End Sub


Run the CementExternalLinks subroutine to replace all the external links with their values.

NOTE: To find out the Long value for the desired colour, first locate an RGB decimal colour code. (An excellent source for this is found here.) Enter the following in the immediate window, replacing "0, 255, 0" with your desired colour, and hit Enter:

?RGB(0, 255, 0)

The resulting number is the Long that you wish to use for your colour constant in the code.



Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Latest Posts