How to retrieve DataSource of chart

[tdhsaurabh]

Hi,

I am quite new to VBA and EXCEL world.
I have following problem.

We plot a chart say chartA in sheetA of bookA, datasource of that chart is say RangeA in sheetA only

Now,

We move sheetA to bookB,

But

ChartA is reffering to RangeA in bookA only

My task is to write a macro (or create any other way) so that after moving chart to bookB, ranges should copy to new book as well.

So my idea is after moving a chart we can trigger a function which:

1)Retrive the NAME of data source of ChartA say:

oldRange = book1.xls!sheet1!RangeA

2) create a dupilcate range in new workbook that is bookB say

newRange = RangeA (this comes after extracting from oldRange)

3) Then change the data source to new range.

mychartobj.chart.setDataSource = RangeA


I am not able to find out data source of ChartA

is there is any way to know chart data source name?

like is there is any counter part of

mychartobj.chart.setDataSource

which is:

mychartobj.chart.getDataSourceName


Or any other approach for the problem will be very help ful thanks

[mdmackillop]

Moved to Excel forum