I would not expect this to work differently to a For i = 1 to
.Chartobjects.Count loop
Aha! Gotcha! Sometimes a For Each loop doesn't find each item in a
collection. It wasn't until I read this response that I remembered this
obscure little feature. Chart Objects are one of the types of items that can
be missed. I'm not sure, but I think sheets or worksheets are as well. It
hasn't happened to me frequently, but lately I've trained myself to use a
looping counter variable in my For Next loops.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -
http://PeltierTech.com
_______
"Peter T" <peter_t@discussions wrote in message
...[color=blue][i]
I did try the normal loop based on max chart count and the results were
the same. Sorry, I just didn't post it since there was no new data.
I assume by "the normal loop" you mean
for i = 1 to activesheet.chartobjects.count
although most people might consider "For..Each" as the "normal" way to
loop objects.
Are you absolutely sure the dubug results are different with the different
methods of looping.
Here's yet another way you can loop your charts
Sub test()
Dim shp As Shape, chtObj As ChartObject, cht As Chart
For Each shp In ActiveSheet.Shapes
If shp.Type = msoChart Then
i = i + 1
Set chtObj = shp.DrawingObject
Set cht = chtObj.Chart
Debug.Print i, chtObj.Name, cht.Name
End If
Next
End Sub
I would not expect this to work differently to a For i = 1 to
.Chartobjects.Count loop
From what you have described about chartobjects having been renamed and
copied I'm not surprised your For Each loop has failed to reference all
correctly.
I know it might seem odd but actually, as your subject line suggests,
there can be a bug of sorts when looping For Each with any object type at
the 'DrawingObject' level; of which ChartObject is a sub type. Apart from
picking up the wrong object, in the case of duplicate names, names that
include punctuation can be missed completely. Remember, we are talking
about ChartObject name here, which is not (normally) same as
chartobject.Chart.Name (though chartObject & Shape names will be the
same).
Regards,
Peter T
"bart13" wrote in message
...[color=green][i]
It was meaningful to me in the sense that it displayed exactly what I
was expecting. Charts are just plain being missed in the loop, no
matter how the loop is constructed or even if the if statement is
removed. The ChartObjects are getting corrupted when renamed, and its
pretty consistent.
I did try the normal loop based on max chart count and the results were
the same. Sorry, I just didn't post it since there was no new data.
Thanks for clarifying on the grouping. I've seen it on one of the
properties menus but never took it any further since I seldom use any
extra chart elements like text boxes, etc.
I hope Mr. Peltier jumps in with an idea or two. I'm at my wits end.
It seems that every time I copy a chart now and then rename it, the
ChartObject gets corrupted and will never again be seen by any loop. If
I don't rename it and just translate the "Chart ###" name to my own
filename in a select case statement, all is well.
I've even tried deleting about 30 of the extra charts in the main chart
tab on the theory that there's some unknown limit I've exceeded, but it
made no difference.
bart13
Peter T;677841 Wrote:
Afraid it's difficult to know how to unwrap those debug lines and make
any
sense of them. I was hoping though that the information might be
meaningful
to you, in particular indicate if any charts were not being picked up
as
expected, or a chart being picked up twice (strangely that's possible
in a
For Each loop).
Did you try looping with the For...To...Next method and compare the
debug,
as I suggested.
I forgot to comment on this from your earlier post
---
I haven't done any grouping that I know of and frankly I'm not
even
sure what it means in this context.---
You can group chartobjects with any other objects, eg rectangle or
other
chartobjects, by selecting them, right-click then group. Copying then
ungrouping can lead to duplicate names (particularly objects that had
been
renamed from their given default).
Regards,
Peter T
"bart13" wrote in message
...-
All the charts in the test tab with only the 6 charts are not being
processed as you can see in the debug output, and just as the text
file
output also shows. Only three of the six are being seen, and in the
first debug section below the names are duplicated.
0 dow_gold_oil_crb1900current_rev Pub2
dow_gold_oil_crb1900current_rev C:\gk\temp\Pub2
dow_gold_oil_crb1900current_rev.png
0 dow_gold_oil_crb1800current_rev Pub2
dow_gold_oil_crb1800current_rev C:\gk\temp\Pub2
dow_gold_oil_crb1800current_rev.png
0 dow_gold_oil_crb1800current Pub2
dow_gold_oil_crb1800current C:\gk\temp\Pub2
dow_gold_oil_crb1800current.png
0 dow_gold_oil_crb1900current_rev Pub2
dow_gold_oil_crb1900current_rev C:\gk\temp\Pub2
dow_gold_oil_crb1900current_rev.png
0 dow_gold_oil_crb1800current_rev Pub2
dow_gold_oil_crb1800current_rev C:\gk\temp\Pub2
dow_gold_oil_crb1800current_rev.png
0 dow_gold_oil_crb1800current Pub2
dow_gold_oil_crb1800current C:\gk\temp\Pub2
dow_gold_oil_crb1800current.png
Here's the same output but with the if/end if commented out and of
course dropping the elseif condition:
1 dow_gold_oil_crb1900current_rev Pub2
dow_gold_oil_crb1900current_rev C:\gk\temp\Pub2
dow_gold_oil_crb1900current_rev.png
2 dow_gold_oil_crb1800current_rev Pub2
dow_gold_oil_crb1800current_rev C:\gk\temp\Pub2
dow_gold_oil_crb1800current_rev.png
3 dow_gold_oil_crb1800current Pub2
dow_gold_oil_crb1800current C:\gk\temp\Pub2
dow_gold_oil_crb1800current.png
The three charts that are being missed aren't anywhere in either
output
and ActiveSheet.ChartObjects.Count does return 6. I have confirmed
again
that all six charts do have unique and different names in the name
box.
Although it may not help, the main chart tab has 350 charts but only
323 are actually processed, whether the Chart/Chert statement is
there
or not. Also, the entire workbook is gigantic - over 500MB.
The purpose of the if condition is so that all I have to do to save
a
new chart for the web site is to change the chart name away from the
default.
Its good to have it confirmed about ChartObject and Chart names.
By the way, thanks for the help - much appreciated. I've been coding
since 1980 but this is my first VBA macro.
bart13
Peter T;677602 Wrote:
Are all your charts being processed or are some skipped due to
this
line-
If Left(strChartName, 5) < "Chert" Then--
(Chert/Chart noted)
comment the export line and just before the "End If" and add these
lines
debug.print counter, Pict.Name, Pict.chart.name, SaveName
Else
debug.print "Else " & Pict.Name, Pict.chart.name
End if
You could also try looping For..To..Next and compare the debug
lines.
Comment the For Each line (also again with the export line
commented
and the
same debug lines)
For i = 1 to Activesheet.Chartobjects.Count
Set Pict = ActiveSheet.ChartObjects(i)
etc
Hopefully comparing debug after looping both ways will indicate
what's
going
on (even if the debug sets are the same). Btw why the If
condition?
Also the debug lines should also highlight what Jon mentioned
about
ChartObject and Chart names not being the same.
I'm sure you know but just in case, press ctrl-g to view the debug
lines in
the Immediate window.
Regards,
Peter T
...
--
bart13