So during my infatuation with converting my workbook to use IFERROR statements throughout, I suddenly realized that the reports I had to send to head office needed to work in Excel 2003. After a bit of… um… motivational speaking… I elected to do the following:
- Create a new workbook
- Copy each of the required worksheets to the new workbook
- Break the links
This method would allow me to take full advantage of Excel 2007's additional functionality, speed up my calc time by using more efficient formulas, and also still provide head office with the reports they need. And easy enough to do as well, since it's just a bit of VBA code to automate it all… right?
Yeah, right. Nothing is ever as easy as it should be.
Copying the worksheets to a new workbook was simple, but the code to break the links is bizarre. I used the code that I'd posted on this blog a while back, but I had to do it in a weird way. Here's the method I ended up using:
- A loop to copy the worksheets to the new workbook
- A call to break links within the loop
- Save and close the workbook
- Re-open the workbook and break the links
- Save and close the workbook
Seems a little redundant, doesn't it?
The reason I ran the link breaking within the loop that I was using to copy the worksheets to a new workbook is because is dramatically improved the speed of the routine. I can only surmise that it did break some links, as it certainly didn't do them all.
After that I tried to break the links with the workbook still open (saved or not), and it didn't have any effect. Basically the only way I found to reliably kill those links was to save, close and re-open the workbook. Very irritating and slow, but effective.
Of course, as trying to work this out wasn't hard enough, I was also battling this issue, which was leaving me with phantom VBA projects. I'm virtually certain that this was affecting some of the code, although I still had to resort to the above method once I had uninstalled that add-in.
My conclusion… breaking links on an unsaved workbook is hokey. This should work.
And just in case anyone wants it, here's the routine I used to break the links:
[vb]Private Sub Util_BreakWorkbookLinks(wb As Workbook)
'Date Created : 3/18/2009 08:36
'Author      : Ken Puls (www.excelguru.ca)
'Macro Purpose: Break all links in the workbook
Dim vLinks As Variant
Dim lLink As Long
On Error Resume Next
vLinks = wb.LinkSources(Type:=xlLinkTypeExcelLinks)
If Not vLinks = vbNullString Then
' Break all links in the active workbook.
For lLink = LBound(vLinks) To UBound(vLinks)
wb.BreakLink _
Name:=vLinks(lLink), _
Type:=xlLinkTypeExcelLinks
Next lLink
End If
vLinks = Nothing
On Error GoTo 0
End Sub
[/vb]
5 thoughts on “Breaking Links in Excel”
Hi Ken
I would be interested in your comments on the following approach:
Savecopyas to create an intact copy of the workbook (the initial workbook does not have links?);
Open the new copy;
Delete unnecessary sheets from the report;
Loop through worksheets converting formulas to values.
Thanks
Matt
Your code got munged. This is why I don't use any of the code formatting plugins, and stick to preformatted html tags. Not as pretty, but also not as ugly.
Was it worthwhile standing on your head trying to break links just to get the benefit of the shorter formulas in 2007? Probably, if you'd already made the sheet loaded with the formulas. But if you were to start again, wouldn't it have been easier to just do it in 2003?
Code all fixed. I posted it directly from Word, so I guess it doesn't translate into the plugin very well. Good to know for future.
Was it worth standing on my head... I think so. I've already noticed significant improvement in my recalc time, which is something I do frequently at month end. Even if it takes a full year for the time to pay back though, I'd have done it, as it gives me the time back when I need it most. Our month end deadlines are so tight that if I can remove 15 minutes out of the job, it gives me 15 more minutes to review the work before it goes to head office.
Matt,
You I believe that in my case you could actually speed up that approach by doing things in this order:
-SaveCopyAs
-Open
-BreakLinks
-Delete unnecessary sheets
There reason for the speed increase would be that there are generally only a few links, and breaking them converts only those cells to values. Doing it before deleting sheets avoids re-calcing against external files, as the links are already gone.
In my case though, the source workbook is now over 10MB, containing 39 report sheets, 10 pivot tables, a control panel page and a distribution matrix (I print a bunch of reports to PDF.) In all honesty, I should have separated out the required reports for head office anyway, so this was a good kick to do so. The file I deliver now is much more targeted to their needs, and doesn't actually hold the Pivot Table source data.
A simple THANK YOU for your help on solving my basic question on how to make groups in Outlook.