Excel VBA - Programmatically change chart's source?

Question

I've got this Excel document which contains several charts which target Named Ranges that are all predefined. The problem is, the reference the chart needs have to include the name of the document for some reason. I've managed to add the filename of the document in the sheet. But I cannot target this cell directly when assigning the source of the chart.

So, when I edit the chart and select the source, it looks like this:

='MyExcelDocument.xlsm'!Graphs_TotCost

For some reason Excel doesn't understand when I target the cell that contains the filename. So I'll probably have to do this via a macro by first defining a string variable, assigning the document name to that string. And then assign the completed string combined with Graphs_TotCost. So, how do I select a chart that's in Sheet3 and change the source of that chart using a macro?

JMax · Accepted Answer · 2011-09-07 13:26:51Z

1

You can change the source of a graph with this kind of statement:

Charts("Chart1").ChartWizard Source:="Graphs_TotCost"

You could also probably use the SetSourceData method of the Chart object.

answered Sep 7, 2011 at 13:26

JMax

26.7k12 gold badges74 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Anonymous Type Over a year ago

you should use the SetSourceData method if it is an existing object. Wizard may apply default chart template if it is different from current one? not sure personally haven't tested that scenario.

Collectives™ on Stack Overflow

Excel VBA - Programmatically change chart's source?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related