Remove External Links from a Chart

[excel?]

How can I remove the external link to a different Excel file in a Chart?

I copied a chart from one Excel file to another. The New file's Data tab has the same name as the old file, but the Chart is still linked to the original workbook.

I need the chart to link to the Data tab in the New file not to the old file. Break Links says it will replace the links with values which wont help. Having to edit each Legend Entries (Series) & Horizontal (Category) Axis Labels for all my parameters is going to be very labor intensive and risk missing updating some.

Hoping there is one or two places to change this for the whole Chart.

Thank you for any help you can provide.

 

[Jon Peltier]

How can I remove the external link to a different Excel file in a Chart?

I copied a chart from one Excel file to another. The New file's Data tab has the same name as the old file, but the Chart is still linked to the original workbook.

I need the chart to link to the Data tab in the New file not to the old file. Break Links says it will replace the links with values which wont help. Having to edit each Legend Entries (Series) & Horizontal (Category) Axis Labels for all my parameters is going to be very labor intensive and risk missing updating some.

Hoping there is one or two places to change this for the whole Chart.

Thank you for any help you can provide.

When you go to Edit Links, rather than Break Links, you can click on Change Links, and browse to the new file.

Or...

Assuming the chart in the old file (OldFile.xlsx) has series formulas like this:

=SERIES(Sheet1!$C$2,Sheet1!$B$3:$B$8,Sheet1!$C$3:$C$8,1)

These will look like this in the new file (NewFile.xlsx):

=SERIES([OldFile.xlsx]Sheet1!$C$2,[OldFile.xlsx]Sheet1!$B$3:$B$8,[OldFile.xlsx]Sheet1!$C$3:$C$8,1)

The difference is the reference to the old file, [OldFile.xlsx].

You can use a routine to extract the old file name.

Sub ChartDataFromOtherToThisWorkbook()
  Dim srs As Series
  For Each srs In ActiveChart.SeriesCollection
    Dim sFmla As String
    sFmla = srs.Formula
    Dim vFmla As Variant
    vFmla = Split(sFmla, "[")
    Dim iFmla As Long
    For iFmla = LBound(vFmla) To UBound(vFmla)
      Dim vvFmla As Variant
      vvFmla = Split(vFmla(iFmla), "]")
      vFmla(iFmla) = vvFmla(UBound(vvFmla))
    Next
    sFmla = Join(vFmla, "")
    srs.Formula = sFmla
  Next
End Sub

 

[excel?]

Perfect - Thank you so much,