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
.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
Instructions:
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.
