Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 48
Default loop to change source data in Excel Charts

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


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default loop to change source data in Excel Charts


Janie;324208 Wrote:
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...


--
gmorris
------------------------------------------------------------------------
gmorris's Profile: http://www.thecodecage.com/forumz/member.php?userid=245
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=90600

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 48
Default loop to change source data in Excel Charts

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?


"gmorris" wrote:


Janie;324208 Wrote:
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...


--
gmorris
------------------------------------------------------------------------
gmorris's Profile: http://www.thecodecage.com/forumz/member.php?userid=245
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=90600


  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6,582
Default loop to change source data in Excel Charts

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/mak...k-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...00906ACNJ.html
_______


"Janie" wrote in message
...
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




  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 48
Default loop to change source data in Excel Charts

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

"Jon Peltier" wrote:

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/mak...k-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...00906ACNJ.html
_______


"Janie" wrote in message
...
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







  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default loop to change source data in Excel Charts


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.


--
gmorris
------------------------------------------------------------------------
gmorris's Profile: http://www.thecodecage.com/forumz/member.php?userid=245
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=90600

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copying charts with automatic change of data source Abracadabra Charts and Charting in Excel 1 August 22nd 09 01:39 AM
Cannot change data source for copied charts MrN Charts and Charting in Excel 1 May 6th 09 05:59 PM
change source data in multiple charts Tuxla Charts and Charting in Excel 5 September 4th 08 03:38 PM
How to keep source data for the Excel Charts constant ... Please H zoddiax Charts and Charting in Excel 1 January 29th 08 01:16 PM
How do I create charts that change source data automatically? cbalster Charts and Charting in Excel 0 May 26th 06 08:19 PM


All times are GMT +1. The time now is 08:09 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"