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.
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 .AddComment 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 Else '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.