Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Break Links to Source in Both Workbook and Chart

    I am using a macro to create a new workbook from two sheets in the current workbook. But this leaves links to the source in the new workbook. To break the links in the new workbook I have found this code which works great . . . until I include a chart. Then the code hangs-up resulting in Not Responding.

    Dim aLinksArray As Variant
    aLinksArray = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLi nks)

    Do Until IsEmpty(aLinksArray)
    ActiveWorkbook.BreakLink Name:=aLinksArray(1), Type:=xlLinkTypeExcelLinks
    aLinksArray = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLi nks)
    Loop

    Is there a way to include the chart, but still break the chart's links using VBA?

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 646 Times in 590 Posts
    Arc,
    I came across this simple little code that works great! Append it to your existing code to break the chart links:

    Code:
    Sub BreakChartLinks()
    For Each x In ActiveChart.SeriesCollection
       x.Values = x.Values
       x.XValues = x.XValues
       x.Name = x.Name
    Next x
    End Sub
    http://support.microsoft.com/kb/213443

    Follow through with the sample testing they provide. NOTE: The chart must be selected when the code is ran. I am dropping this one i my bag of tricks!

    As far as breaking the rest of the links, I found this post by Dave Peterson documented as a solved solution to an OP. I have not tested it myself, however.

    Code:
    Option Explicit
     Sub UseBreakLink2()
     
    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
    HTH,
    Maud
    Last edited by Maudibe; 2013-06-19 at 18:18.

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 646 Times in 590 Posts
    UPDATE:

    I have combined the code and tested it. It will remove all links from the copied worksheet and the chart links as well. I had to add two additional lines of code. It will select the chart when it runs. You may have to change "Chart 1" to "Chart 2", etc., if you have added and deleted charts.

    Code:
    Sub UseBreakLink2()
     Dim x As Series
    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
    
    ActiveSheet.ChartObjects("Chart 1").Activate  
    For Each x In ActiveChart.SeriesCollection
       x.Values = x.Values
       x.XValues = x.XValues
       x.Name = x.Name
    Next x
    
    End Sub
    See sources in previous post.

    Maud
    Last edited by Maudibe; 2013-06-19 at 18:32.

  4. The Following User Says Thank You to Maudibe For This Useful Post:

    Arcturus16a (2013-06-27)

  5. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    That's great! Thanks Maud. I modified it for three charts and it works perfectly.

  6. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maudibe,

    I don't have a workbook with links and charts to test but would this modification work so you don't have to worry about the number of charts on a sheet?
    Code:
    Sub UseBreakLink3()
     Dim x As Series
     Dim astrLinks As Variant
     Dim iCtr As Long
     Dim oCurChart as Object
     
    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
    
    For Each oCurChart in ActivesSheet.ChartObjects
       oCurChart.Activate  
       For Each x In ActiveChart.SeriesCollection
          x.Values = x.Values
          x.XValues = x.XValues
          x.Name = x.Name
       Next x
    Next oCurChart   
    
    End Sub
    Alert! This is Air Code! HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Maudibe (2013-06-30)

  8. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 646 Times in 590 Posts
    RG
    Looks Like it should work. Alert! Air judgement

  9. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 646 Times in 590 Posts
    RG,

    Yes, It did work. Thanks

  10. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud,

    Thanks for the post back.

    Just curious but why the series of x.Values = xValues, etc. Is there some kind of translation going on that I'm missing?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 646 Times in 590 Posts
    RG,

    It is re-assisgning the current values of x (which are linked) to x.values (without the link). See the values In the images of the linked chart Vs the values when links get removed.

    Linked:
    Chart1.png

    Unlinked:
    Chart2.png

    The x values linked to another workbook have been replaced with just the values not the formula.

    Similar to the effect using these two different code lines with cells where C7=1000:

    Cells(1, 1).Value = Cells(1, 2).Formula yields Cell A1 =C7/100 (a formula)
    Cells(1, 1).Value = Cells(1, 2).Value yields Cell A1 =10 (a value)

    I hope I made that somewhat clear.

    Maud

  12. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud,

    That's what I thought was going on but seemed a little dicey for some reason. It's even clearer if you use Cells(1,1) in all the references in the post above and always start with =C7/100 in A1. I tested with that and it made things perfectly clear.
    Last edited by RetiredGeek; 2013-07-01 at 08:31.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #11
    New Lounger
    Join Date
    Mar 2015
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code not working

    Hello, Below code is not working for Xl2010 , I Breaks only 1 Link, Where in it has mutiple links

    Dim x As Series
    Dim astrLinks As Variant
    Dim iCtr As Long
    Dim oCurChart as Object

    astrLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLi nks)

    If IsArray(astrLinks) Then
    For iCtr = LBound(astrLinks) To UBound(astrLinks)
    ActiveWorkbook.BreakLink Name:=astrLinks(iCtr), _
    Type:=xlLinkTypeExcelLinks
    Next iCtr
    End If

  14. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 646 Times in 590 Posts
    vijaymathad,

    Can you post a sample of your workbook?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •