Remove External Links from a Chart

excel?

Board Regular
Joined
Sep 14, 2004
Messages
143
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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:

Excel Formula:
=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):

Excel Formula:
=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.

VBA Code:
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
 
Upvote 0
Solution

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top