I had to do some real quick budgeting work today, and needed to build a template to work from. The easiest way was to grab a heavily linked workbook we had, and convert all the links to values so that I could work some different assumptions on it. The thing is that I didn't want to just copy and past values, as I wanted all the other formulas to work, I just didn't want to point to external files any more.
I ended up coding this to work as follows:
[vb]Sub KillLinks()
Dim ws As Worksheet
Dim cl As Range
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
Application.StatusBar = _
"Removing external links from " & ws.Name
With ws.UsedRange
Set cl = .Find(":\", LookIn:=xlFormulas)
If Not cl Is Nothing Then
Do
cl.Value = cl.Value
Set cl = .FindNext(cl)
Loop While Not cl Is Nothing
End If
End With
Next ws
With Application
.ScreenUpdating = True
.StatusBar = False
End With
End Sub[/vb]
Worked fairly well, and quickly, giving me the feedback of how far it had progressed.
I'm curious to any comments on this, though. Is there an easier way?
11 thoughts on “Converting External Links to Values”
A really easy way
Let someone else do the work...
Two add-ins which do something very similar
Bill Manville's Link Finder
and
ASAP's Find & Remove External Links
Both give the option Y/N to delete links
Hi Phil,
True enough. Both are excellent utilities. For my purposes, though, I wanted to get rid of about 2000 linked cells in this workbook, so saying Y/N each time really wasn't an option.
In truth, it only took a few minutes to knock up the code, as well. Time well spent, IMO. 🙂
This looks very useful (read: I will probably copy this code for my own uses!)
The only thing I would change is to also look for // in the formula. We have a lot of network drives that are more accessible by their drive name then a drive letter.
Great point! Our stuff was all mapped to network drives, rather than UNC paths, but I can certainly see that adding to the robustness.
Change the name of the folder that the linked spreadsheets are in. When you open the target spreadsheet (containing the links) you will be told that some links can't be updated, and offered the choice to "Continue" or "Edit Links". Choose "Edit Links" then, on the right-hand side, you have the option to "Break links". This converts all linked values to actual values (numbers and/or text).
I'm not sure this would work for your purposes, but I've had success using a pretty simple tactic -- Replace All.
(A) If you want to remove the links to external spreadsheets but preserve the links to specific worksheets within the working file, use the Replace All window with [*] in the Find What box and leave the Replace With box empty.
(B) If you want to remove the links to external spreadsheets and to other worksheets within the given file, i.e., make all of the cell references within the given worksheet refer to that worksheet only, use the Replace All window with '*! in the Find What box and leave the Replace With box empty.
Note that in scenario (A), if the destination file does not contain worksheets with the same names as the ones referenced in the formulas you're copying, Excel is going to think you're looking to open a spreadseet with a filename equal to the sheet name that remains in the formula, and will prompt you to find that file. If you're dealing with a large number of replacements, this prompt will pop up for each instance. Make sure your worksheets doesn't have this issue before using this process. Yes, I learned this lesson the hard way.
I discovered that there is a "BreakLink" method in the Excel object model. So if you're not interested in marking the links that you broke in any way, then the following will quickly nuke all the links in the active workbook:
[vb]Sub BreakLinks()
'Date Created : 9/10/2008 20:14
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: Break all links in the active workbook
Dim vLinks As Variant
Dim lLink As Long
' Define variable as an Excel link type.
vLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
If vLinks = vbNullString Then Exit Sub
' Break all links in the active workbook.
For lLink = LBound(vLinks) To UBound(vLinks)
ActiveWorkbook.BreakLink _
Name:=vLinks(lLink), _
Type:=xlLinkTypeExcelLinks
Next lLink
End Sub[/vb]
I was working today at http://forums.techguy.org on a task in which the person wanted to convert an Excel file with links to XML.
Just coincidence (a linked-in update) got me to the site today and I found this. Thanks!
Pingback: The Ken Puls Blog » Blog Archive » Breaking Links in Excel
Hello Ken,
Unfortunately, this routine doesn't work, like the in-built function, for links include in the names (with for instance offset function, used to defined to define an adaptable range, next summed in the worksheet).
Do you have a solution for this issue ?
Best Regards
Philippe
Hi Phillipe,
No, I haven't attempted to solve that issue at this point. Honestly, I don't use links in names if I can avoid it...