I have a template.xls file that contains one "form" (actually a range, A1:
AE28, formatted for my data) for displaying store data. This "form" includes
8 small charts that have datasources on the same range as the charts. I can
easily determine the datasource range for each of the 8 charts, but am having
difficulty assigning the datasource to the correct chart due to the
difficulty in identifying, from my code, which chart goes with which
datasource.

This is only the beginning of the problem. What my Access code does, is it
opens the "template.xls" and saves it as "current.xls". Then I copy the
"form", including the charts, and paste it below the original. I then
populate the original, at the top of the worksheet, with the store data. Then
I copy the empty copy below the form I've just populated, paste it again
under the empty form I just copied, and proceed to populate the second form.
Then I copy the 3rd form and paste it underneath again, and so on, and so on,
until all 900+ stores have forms, each containing 8 small charts, with source
data in ranges to their right.

I hope this makes sense to someone who can give me some guidance. I've
learned that the sourcedata is a property of the chart, which is a property
of the chartobject in the chartobjects collection of the worksheet, but have
not been able to concisely identify the location of each chart so I can
supply the corresponding source data ranges. As it is, when I copy the charts,

they retain their original source data at the top of the worksheet. I need to
change that to reflect the ranges to the right of the charts as I copy and
paste them in the worksheet.

Here's a code sample I tried to use to rename the chartobjects, but I don't
know if the chartobjects are listed in the collection in the same order they
appear on the worksheet:

Function RenameCharts(x As Integer) As String
'renames charts to reflect actual # in worksheet
Dim CO As ChartObject
Dim n As Integer

For Each CO In Sheets("StoreReports").ChartObjects
n = n + 1
CO.Name = "ChObj " & n
Next CO

End Function

--
Bill Reed

"If you can't laugh at yourself, laugh at somebody else"

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...chart/200802/1

Could you use something like this, collect the positions as distances from
the top of the sheet, and then process the charts in order of their
position:

For i = 1 to ActiveSheet.ChartObjects.Count
Distance(i) = ActiveSheet.ChartObjects(i).Top
Next

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"ragtopcaddy via OfficeKB.com" <u9289@uwe wrote in message
news:8020d61370a64@uwe...
I have a template.xls file that contains one "form" (actually a range, A1:
AE28, formatted for my data) for displaying store data. This "form"
includes
8 small charts that have datasources on the same range as the charts. I
can
easily determine the datasource range for each of the 8 charts, but am
having
difficulty assigning the datasource to the correct chart due to the
difficulty in identifying, from my code, which chart goes with which
datasource.

This is only the beginning of the problem. What my Access code does, is it
opens the "template.xls" and saves it as "current.xls". Then I copy the
"form", including the charts, and paste it below the original. I then
populate the original, at the top of the worksheet, with the store data.
Then
I copy the empty copy below the form I've just populated, paste it again
under the empty form I just copied, and proceed to populate the second
form.
Then I copy the 3rd form and paste it underneath again, and so on, and so
on,
until all 900+ stores have forms, each containing 8 small charts, with
source
data in ranges to their right.

I hope this makes sense to someone who can give me some guidance. I've
learned that the sourcedata is a property of the chart, which is a
property
of the chartobject in the chartobjects collection of the worksheet, but
have
not been able to concisely identify the location of each chart so I can
supply the corresponding source data ranges. As it is, when I copy the
charts,

they retain their original source data at the top of the worksheet. I need
to
change that to reflect the ranges to the right of the charts as I copy and
paste them in the worksheet.

Here's a code sample I tried to use to rename the chartobjects, but I
don't
know if the chartobjects are listed in the collection in the same order
they
appear on the worksheet:

Function RenameCharts(x As Integer) As String
'renames charts to reflect actual # in worksheet
Dim CO As ChartObject
Dim n As Integer

For Each CO In Sheets("StoreReports").ChartObjects
n = n + 1
CO.Name = "ChObj " & n
Next CO

End Function

--
Bill Reed

"If you can't laugh at yourself, laugh at somebody else"

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...chart/200802/1