Problem Breaking Links in Excel

In the screen shot below, there are two files. Cell B4 in the worksheet at the right is linked to cell B7 in the sheet at the left.

breaklinksprotected01

In the file with the link, I can go to the Ribbon’s Data tab, and click the Edit Links command,

breaklinksprotected03

That opens the Edit Links dialog box, and shows a list of the links in the workbook.

To break the link to the other file, you can click the Break Links button.

When the confirmation message appears, read the dire warnings, and make a backup, if you haven’t already done that.

Then come back to the Edit Links command, and click OK.

breaklinksprotected02

Break Link Button Dimmed Out

I use this technique in Excel programming sometimes, when creating copies of workbooks. By breaking the links, the formula results change to values, and the file can be sent to someone who doesn’t have the original source file.

Recently, I ran into a problem – the Break Link command wasn’t working in the macro, even though it had worked nicely for several months.

So, to troubleshoot the problem, I tried to break the link manually. When the Edit Links window opened, the Break Link button was dimmed out. The link was still showing in the list, but I couldn’t change it or break it.

breaklinksprotected04

After several head scratching moments (or hours, I can’t remember), I finally noticed that someone (probably me) had protected the sheet that was currently active.

The sheet with the link wasn’t protected, but that didn’t matter. If the active sheet is protected, you can’t break a link in the file.

Unprotect the Sheet

So, if you’re having a problem breaking a link, check the active sheet for protection. You can do this manually, or in your VBA code.

For example, if you’re doing this in a macro, activate a specific sheet, unprotect it, then break the link, and protect the sheet again. The code below breaks the first link in the file.

Dim astrLinks As Variant
astrLinks = wbNew.LinkSources(Type:=xlLinkTypeExcelLinks)

' Break the first link in the active workbook.
On Error Resume Next
With wbNew.Sheets(1)
   .Select
   .Unprotect
   .BreakLink Name:=astrLinks(1), _
    Type:=xlLinkTypeExcelLinks
On Error GoTo 0
   .Protect
End With

________________________

5 comments to Problem Breaking Links in Excel

  • It is probably worth mentioning here that as long as at least one sheet is protected ANYWHERE in the workbook, one cannot modify or edit any of the styles. New ones can be created and edited by using the "duplicate style" command, but then it remains fixed and cannot be edited or deleted. Had this frustrating problem a while ago. Makes no sense, but go figure.

  • Hi Debra,

    Definitely tucking this macro away. Thank you!

    Is there any way to find out the cells/workbook elements that contain the links? I just ran into this a couple days ago. I kept looking for the links and couldn't find them, and the error message didn't give any specifics. Turns out they were in my named ranges.

  • Andrew Pearce

    I just thought I would mention here in case it helps someone down the road...

    I had an issue breaking a link to a file which I ended up being able to fix. Excel 2010 by the way.

    I had put a considerable amount of effort into one sheet of a workbook, but I decided I wanted it in another file entirely. I couldn't "copy" everything from one worksheet to the other and keep the formulas intact (I could only get the values themselves, which wasn't good enough) but I could use Excel's built-in functionality to move the whole worksheet to a different workbook. This worked great except that I had a reference to a different sheet on the first book which got carried over as a reference to the file, hence the link.

    The problem was that the linked cell had data validation; it was a drop-down menu which forced me to select specific values. Selecting that cell and "deleting" the contents did not destroy the data validation as it would destroy a link in a formula. To destroy the data validation cell, I had to either delete the row or column it was in or find the specific cell and edit the data validation to allow all values (or select from a different list). I had suspected it was a data validation cell but I still had to find it.

    By saving new copies of the worksheet and slowly deleting entire columns or sheets I narrowed it down to the first six columns of my second sheet, but the columns still had 10002 rows! Remembering that the specific values were integer numbers, I selected the entire range, typed in AAA and hit ctrl-enter to put AAA in every cell. Under data validation, I hit circle invalid data and the culprit was exposed.

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>