R
ragtopcaddy via OfficeKB.com
I am having a problem editing an excel template which I managed to cobble
together a while back.
The template is a dashboard report. It contains one specimen of a dashboard
for one store.
The way it works is that for each store being reported on, the range
containing the specimen is copied and pasted. At the end of that proccess
(maybe hundreds of dashboards), 8 chart objects in each dashboard are
connected to their source data:
i = 9
x = conRows '33
On Error GoTo OuttaHere
Do
'Link the charts to their source data:
For y = 0 To 7
shtRpt.ChartObjects(i + y).Activate
XLobj.ActiveChart.SetSourceData _
Source:=XLobj.Sheets("StoreReports").Range(shtRpt.Cells(x + (2 *
y), conCols - 5), _
shtRpt.Cells(x + 1 + (2 * y), conCols)), PlotBy:=xlRows
Next y
i = i + 8
x = x + conRows
Loop
I've been running this code for months and it works like a charm, although I
had to wrestle the template to the ground and beat it into submission,
rearranging the charts until they were in the order that Excel insisted they
be. Before I rearranged them, they were all out of position with regard to
their source data.
OK, so this works fine. But now, I'm required to add 2 more charts. So I
copied and pasted the bottom 2 of the 8 charts below themselves and now have
10 charts. I changed the above code to:
For y = 0 To 9
Unfortunately, the range Excel selects for my 2 new charts using the above
loop was not the data that had been added below the ranges for the other 8
charts (8 2-row data ranges, neatly listed in a 16 row by 5 column range, now
increased to 10 2-row data ranges neatly listed in a 20 row by 5 column range
to the right of the dashboard, out of the print area). Instead of linking to
those 2 new sources (R17C26:R18C31) and (R19C26:R20C31), my new graphs linked
instead to the empty ranges (R35C26:R36C31) and (R33C26:R34C31), which are
not only empty and below the dashboard's range (such that these 2 graphs for
this store would actually find themselves linked to data from the next
store's dashboard), but the order is reversed. The graph on the left should
be linked to the higher positioned data, R33, not R35, if the code was
cycling through the graphs in order. I can find no way to accurately identify
or rename the graphs. I only hit on the successful 8 graph solution after
days of trial and error (I have no idea why it works!).
I have a function that returns the names of the graphs in my immediate window:
Sub ChartNames()
Dim CO As ChartObject
Dim n As Integer
For Each CO In Sheets("Template").ChartObjects
Debug.Print CO.Name
Next CO
End Sub
When I run it for my new template, I get the following list:
Chart 1
Chart 2
Chart 3
Chart 4
Chart 5
Chart 6
Chart 7
Chart 8
Chart 7
Chart 1
How is it that duplicate names are even possible?! This is really mind-
numbing!
Please help!
Thanks,
--
Bill Reed
"If you can't laugh at yourself, laugh at somebody else"
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-chart/200805/1
together a while back.
The template is a dashboard report. It contains one specimen of a dashboard
for one store.
The way it works is that for each store being reported on, the range
containing the specimen is copied and pasted. At the end of that proccess
(maybe hundreds of dashboards), 8 chart objects in each dashboard are
connected to their source data:
i = 9
x = conRows '33
On Error GoTo OuttaHere
Do
'Link the charts to their source data:
For y = 0 To 7
shtRpt.ChartObjects(i + y).Activate
XLobj.ActiveChart.SetSourceData _
Source:=XLobj.Sheets("StoreReports").Range(shtRpt.Cells(x + (2 *
y), conCols - 5), _
shtRpt.Cells(x + 1 + (2 * y), conCols)), PlotBy:=xlRows
Next y
i = i + 8
x = x + conRows
Loop
I've been running this code for months and it works like a charm, although I
had to wrestle the template to the ground and beat it into submission,
rearranging the charts until they were in the order that Excel insisted they
be. Before I rearranged them, they were all out of position with regard to
their source data.
OK, so this works fine. But now, I'm required to add 2 more charts. So I
copied and pasted the bottom 2 of the 8 charts below themselves and now have
10 charts. I changed the above code to:
For y = 0 To 9
Unfortunately, the range Excel selects for my 2 new charts using the above
loop was not the data that had been added below the ranges for the other 8
charts (8 2-row data ranges, neatly listed in a 16 row by 5 column range, now
increased to 10 2-row data ranges neatly listed in a 20 row by 5 column range
to the right of the dashboard, out of the print area). Instead of linking to
those 2 new sources (R17C26:R18C31) and (R19C26:R20C31), my new graphs linked
instead to the empty ranges (R35C26:R36C31) and (R33C26:R34C31), which are
not only empty and below the dashboard's range (such that these 2 graphs for
this store would actually find themselves linked to data from the next
store's dashboard), but the order is reversed. The graph on the left should
be linked to the higher positioned data, R33, not R35, if the code was
cycling through the graphs in order. I can find no way to accurately identify
or rename the graphs. I only hit on the successful 8 graph solution after
days of trial and error (I have no idea why it works!).
I have a function that returns the names of the graphs in my immediate window:
Sub ChartNames()
Dim CO As ChartObject
Dim n As Integer
For Each CO In Sheets("Template").ChartObjects
Debug.Print CO.Name
Next CO
End Sub
When I run it for my new template, I get the following list:
Chart 1
Chart 2
Chart 3
Chart 4
Chart 5
Chart 6
Chart 7
Chart 8
Chart 7
Chart 1
How is it that duplicate names are even possible?! This is really mind-
numbing!
Please help!
Thanks,
--
Bill Reed
"If you can't laugh at yourself, laugh at somebody else"
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-chart/200805/1