none
Excel 2010 VBA: Workbook.BreakLink code identifies but fails to break link

    Question

  • I'm trying to break a link that is embedded in my Excel workbook.  Actually there are two of them.  See below:

    These links don't respond to either Break or Change Source options.

    I run the following code which correctly identifies the links in the MsgBox but for some reason does not delete the links.  Any ideas out there on why this code is not breaking the links??

    Offending Code:

    Public Sub RemoveLinks()
    Dim Link As Variant
    For Each Link In ActiveWorkbook.LinkSources
    MsgBox ("Link name is " & Link)
    ActiveWorkbook.BreakLink Name:=Link, Type:=xlLinkTypeExcelLinks
    Next
    End Sub

    Saturday, September 29, 2012 11:12 PM

Answers

  • Hi bob,

    Thanks for posting in the MSDN Forum.

    It's based on my experience that you can try this macro to break links in Excel

    Option Explicit
    Sub BreakLinks()
     Dim astrLinks As Variant
     Dim iCtr As Long
     astrLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
     If IsArray(astrLinks) Then
     For iCtr = LBound(astrLinks) To UBound(astrLinks)
     ActiveWorkbook.BreakLink Name:=astrLinks(iCtr), _
     Type:=xlLinkTypeExcelLinks
     Next iCtr
     End If
    End Sub
    

    Hope this helps.

    Best Regards,


    Leo_Gao [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Leo_GaoModerator Monday, October 08, 2012 1:22 AM
    • Unmarked as answer by ConstPM Wednesday, October 10, 2012 1:46 PM
    • Marked as answer by ConstPM Wednesday, October 10, 2012 1:47 PM
    Wednesday, October 03, 2012 7:04 AM
    Moderator

All replies

  • Have you checked the sheet protection ? I suppose the sheet is protected where the external ref used.

    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Sunday, September 30, 2012 6:13 AM
    Answerer
  • Yep!  I run code before this to unprotect all sheets and make all sheets visible.  Also it usually bombs out when it is addressing a protect sheet when it shouldn't be.  The code I sent doesn't bomb out.  It just simply doesn't break the links.

    Thanks just the same.

    ...bob sutor


    • Edited by ConstPM Sunday, September 30, 2012 7:16 AM
    Sunday, September 30, 2012 7:14 AM
  • Hi bob,

    Thanks for posting in the MSDN Forum.

    It's based on my experience that you can try this macro to break links in Excel

    Option Explicit
    Sub BreakLinks()
     Dim astrLinks As Variant
     Dim iCtr As Long
     astrLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
     If IsArray(astrLinks) Then
     For iCtr = LBound(astrLinks) To UBound(astrLinks)
     ActiveWorkbook.BreakLink Name:=astrLinks(iCtr), _
     Type:=xlLinkTypeExcelLinks
     Next iCtr
     End If
    End Sub
    

    Hope this helps.

    Best Regards,


    Leo_Gao [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Leo_GaoModerator Monday, October 08, 2012 1:22 AM
    • Unmarked as answer by ConstPM Wednesday, October 10, 2012 1:46 PM
    • Marked as answer by ConstPM Wednesday, October 10, 2012 1:47 PM
    Wednesday, October 03, 2012 7:04 AM
    Moderator
  • Thanks Leo,

    I really appreciate the code and direction.  Sorry for the late response I have been away.

    Regards,

    Bob Sutor

    Wednesday, October 10, 2012 1:46 PM
  • In Excel 2010, I've seen it "not break the links" but then when I save the resulting spreadsheet and re-open it, the links are gone.  In other words, your code works but you can't prove it works until later.  Most of the time, breaking links makes them disappear right away, of course. 

    My problem links were from charts referencing data, and I just reproduced this in a new workbook.  Copy data and chart from workbook A and paste in B, then paste-values again in the same place.  Then break the link (not using macro) -- it does not disappear.  Close workbook B and re-open.  Link is gone.


    Kip B


    • Edited by KipB Wednesday, November 06, 2013 1:17 AM add test result
    Wednesday, November 06, 2013 1:12 AM