• 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 (www.excelguru.ca)
    '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 (www.excelguru.ca)
    'Macro Purpose: Speed up the working environment
        With Application
            .ScreenUpdating = False
        End With
    End Sub
    Private Sub Environ_RestoreSettings()
    'Author       : Ken Puls (www.excelguru.ca)
    '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.


    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!


    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!

    Comments 2 Comments
    1. scott's Avatar
      scott -
      The code here is great...as always! Used your site many times!!!

      However, I would like to take the code above a step further and only replace the 'linked portion of the formula' with the external value and keep the cell formula intact. But I don't know how.

      For example:
      if the linked value of "[Book1]Sheet1!$A$1" = 5

      Replace the original linked cell formula:

      = 5*10

      Not just the final value of "50".

      Any ideas?
    1. scott's Avatar
      scott -
      sorry somehow posted the same comment twice...I have deleted the duplicate comment with this (as I do not know how to delete the full comment )