loop to change source data in Excel Charts

[Janie]

Have mulitple charts. Source Data is :

='[different file]!sheet name'!range.

The sheet from the different file has now been copied into the file where
charts are. Is there an easy way to remove the different file name from all
the Source Data references (for Name, XValues and Values) such that reference
becomes simply:

='sheet name'!range

 

[gmorris]

Have mulitple charts. Source Data is :

='[different file]!sheet name'!range.

The sheet from the different file has now been copied into the file
where
charts are. Is there an easy way to remove the different file name
from all
the Source Data references (for Name, XValues and Values) such that
reference
becomes simply:

='sheet name'!range

Right off I can't think of any EASY way to do it, but there are several
options you could use. It would help to know if you are just linking
straight from the chart workbook or if you are using code to do the work
of populating the cells. If it's only a matter of changing the formula,
look up the Formula and FormulaR1C1 functions in VBA and you can then
change any cell(s) you want, like 'Sheets(1).Cells(1, 1).FormulaR1C1 =
"Hello"' will change the contents of A1 to 'Hello' in the first
worksheet. If you are new to VBA, you will need to do a little more
research and learn how to use the parsing functions to find the
'[different file]!' name and truncate it from the formula, then put it
back in. It all depends on what you are wanting to do with this and
how...

 

[Janie]

no - this is not changing any formula in any cell. This is changing the
REFERENCE in the Source Data dialog. If not using the Source Data dialog,
then the series reference that is =SERIES('[FileName]SheetName'!Range)

If this was just a case of changing references in a Cell, well, then heck,
Find and Replace would do the trick just fine. But Find and Replace does not
touch the charts.

Given that there are 50 charts each having two dozen data points, I do not
relish the prospect of going point by point to correct the reference to
become =SERIES('SheetName'!Range)

That's why I am looking for a nice way to loop through the SeriesCollections
and change the reference.

Any takers on the challenge?

Have mulitple charts. Source Data is :

='[different file]!sheet name'!range.

The sheet from the different file has now been copied into the file
where
charts are. Is there an easy way to remove the different file name
from all
the Source Data references (for Name, XValues and Values) such that
reference
becomes simply:

='sheet name'!range

Right off I can't think of any EASY way to do it, but there are several
options you could use. It would help to know if you are just linking
straight from the chart workbook or if you are using code to do the work
of populating the cells. If it's only a matter of changing the formula,
look up the Formula and FormulaR1C1 functions in VBA and you can then
change any cell(s) you want, like 'Sheets(1).Cells(1, 1).FormulaR1C1 =
"Hello"' will change the contents of A1 to 'Hello' in the first
worksheet. If you are new to VBA, you will need to do a little more
research and learn how to use the parsing functions to find the
'[different file]!' name and truncate it from the formula, then put it
back in. It all depends on what you are wanting to do with this and
how...

 

[Jon Peltier]

Go to Edit menu > Links, find the other workbook in the list and select the
link, click Change Link, and browse to the file you have open,

Next time, copy the charts using this approach:
http://peltiertech.com/WordPress/make-a-copied-chart-link-to-new-data/

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/>
Advanced Excel Conference - Training in Charting and Programming
http://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
_______

 

[Janie]

OMG! Too simple. You're a prince, Jon!

Go to Edit menu > Links, find the other workbook in the list and select the
link, click Change Link, and browse to the file you have open,

Next time, copy the charts using this approach:
http://peltiertech.com/WordPress/make-a-copied-chart-link-to-new-data/

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/>
Advanced Excel Conference - Training in Charting and Programming
http://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
_______

Have mulitple charts. Source Data is :

='[different file]!sheet name'!range.

The sheet from the different file has now been copied into the file where
charts are. Is there an easy way to remove the different file name from
all
the Source Data references (for Name, XValues and Values) such that
reference
becomes simply:

='sheet name'!range

 

[gmorris]

Ahh, now that makes a lot more sense! Sometimes just a little more info
on what one is trying to do makes all the difference.