Originally Posted by Peter T

Could you explain what you mean by "exported". With the chart Export method
you need to supply a file name for what will be an image of the chart saved
to disc (unique to avoid over writing). This name is not related to the
chart name, even though in practice you might use a similar name, eg
"C:\ChartSales01.gif"

Do you mean perhaps copying to some other workbook. Either way, there can be
problems referencing all chartobjects under relatively rare scenarios (or
any objects at the drawing object level). The following may reference
different charts,

for each chtObj in ws.chartobjects
vs
for i = 1 to ws.ChartObjects.Count
Set chtObj = ws.Chartobjects(i)

(ws is a reference to the sheet)

One way discrepancies can arise is after first renaming, then grouping, then
copying then ungrouping. You can end up with multiple chartobjects having
the same 'new name'.

Normally the For Next loop is more reliable (same as Chartobjects.count) but
the For Each loop will pick up any chartobjects that are still in a group
(but can be problematic for different reasons).

Regards,
Peter T

Originally Posted by Peter T

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

Hi Peter,

I'm doing a loop similar to what you noted, but excluding charts with
the default name of Chart ###

-
For Each Pict In ActiveSheet.ChartObjects

strChartName = RTrim(Pict.Chart.Name)
tChartName = strChartName
ThisChartNum = Val(Right(strChartName, 4))

testFile.WriteLine strChartName

If Left(strChartName, 5) < "Chert" Then
Set ThisChart = Pict.Chart
SaveName = SavePath & strChartName & ".png"


ThisChart.Export Filename:=SaveName, FilterName:="PNG"
Counter = Counter + 1

End If

Next
Set ThisChart = Nothing

testFile.Close
-
(note that the actual non test code does have "Chart" instead of
"Chert")


I haven't done any grouping that I know of and frankly I'm not even
sure what it means in this context.

The truly weird part is that the code usually works and does save the
file just fine. The test tab that I noted has 6 charts in it and three
of them save just fine, but its like the other three (created by just
copying one of the working charts, which itself was copied from another
chart tab, and then editing the name and other attributes like the x
axis via the normal UI ) don't even exist. Their names don't even show
up in the output text file. All the names are shorter than the max 31
character limit.


bart13



Peter T;677490 Wrote:
Could you explain what you mean by "exported". With the chart Export
method
you need to supply a file name for what will be an image of the chart
saved
to disc (unique to avoid over writing). This name is not related to
the
chart name, even though in practice you might use a similar name, eg
"C:\ChartSales01.gif"

Do you mean perhaps copying to some other workbook. Either way, there
can be
problems referencing all chartobjects under relatively rare scenarios
(or
any objects at the drawing object level). The following may reference
different charts,

for each chtObj in ws.chartobjects
vs
for i = 1 to ws.ChartObjects.Count
Set chtObj = ws.Chartobjects(i)

(ws is a reference to the sheet)

One way discrepancies can arise is after first renaming, then grouping,
then
copying then ungrouping. You can end up with multiple chartobjects
having
the same 'new name'.

Normally the For Next loop is more reliable (same as
Chartobjects.count) but
the For Each loop will pick up any chartobjects that are still in a
group
(but can be problematic for different reasons).

Regards,
Peter T






--
bart13

Originally Posted by Peter T

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
...[color=blue][i]

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:[color=green][i]
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



...

Originally Posted by Peter T

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=blue][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

Originally Posted by Peter T

I really would expect both the "for i = 1 to .chartobjects.count" and the
For each shp in .Shapes loop (with if msoChart test) to work correctly.
Possibly there's something else in your own code at fault, or some other bug
that we have all missed.

If you like you can send me a zipped stripped down non-sensitive version of
what you have. Make a copy of your book, prune as much as possible in terms
of data, charts, sheets and not least code; leaving just enough to
demonstrate the problem.

Regards,
Peter T
pmbthornton gmail com

"bart13" wrote in message
...[color=blue][i]

Yes - very much so. The straight loop (as in "for i = 1 to
activesheet.chartobjects.count" ) has wrong results, just like every
other attempt and regardless of whether the if test was being used or
not. There are 6 charts on the tab, only three show up in both the
actual text file and the debug print statement.

No matter what the code is, charts are being skipped.

To state it another way, Pict.Name and Pict.Chart.Name return the exact
same matching values - every time. But both only return three names and
there are six charts, and all six have unique names in the name box.

No matter if I look at the output in the text file or from the debug
print statement, its the same issue. Six charts in the tab, only three
recognized.


And I do betray my ancient status as a coder. The last time I did any
Basic coding, there was no such thing as a for each loop so the "for i
= 1 to ..." is normal to me.

I did double check the name box again for all six charts, and all the
names are both unique and do not contain punctuation either (unless an
underscore is considered punctuation).

I'm obviously missing something since it appears you're saying I have
dupe names, but I have no way of which I'm aware to change the
duplicate ones nor is there any code that I can run which will
distinguish the dupes.

The only way that works on new copied charts is not to change the name
but rather hard code it into a select case set of statements based on
the "Chart ###" name.

Are you saying that this is expected behavior and an open and old bug
(or something similar), and that I should just continue to do the hard
coding and work around? I'm not trying to be accusatory, just in case.

Regards,
bart


Peter T;678438 Wrote:
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
...

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







--
bart13

Originally Posted by Peter T

OK I got it, the stripped down version of only 14Mg !

The problem was easy to find, it was due to exactly one of the potential
reasons I predicted when looping For Each ChartObject (or any drawing object
type), specifically

in a reply to you
" names that include punctuation can be missed completely. "

and in my reply to Jon
" Further, For Each Drawingobject can entirely fail to reference any names
with punctuation, eg "MyChart.1". "

You have three chartobject names like this
"dow_gold_oil_crb1900-current"
note the - dash !

Solution is either rename those objects or change your loop For Each
Chartobject loop to either of the other loop methods I've suggested many
times in this thread.

For i = 1 to ws.ChartObjects.Count
Set chtObj = ws.ChartObjects(i)

or
For Each shp in ws.Shapes
If shp.Type = msoChart then
'etc

I'd use the index method.

BTW, in your file I see you tried in a test macro to implement an index
style loop. Unfortunately you did it like this

For Counter = 1 to ws.ChartObjects.Count
' do stuff
Counter = Counter + 1
Next

So your loop would only process every other chart !

Regards,
Peter T



"bart13" wrote in message
...[color=blue][i]

The email with a link to the workbook will be on the way soon. The week
was too wild to get it to you sooner.

I did not try the .Shapes loop yet, but don't have much hope for it.

Thanks,
bart13

Peter T;678958 Wrote:[color=green][i]
I really would expect both the "for i = 1 to .chartobjects.count" and
the
For each shp in .Shapes loop (with if msoChart test) to work
correctly.
Possibly there's something else in your own code at fault, or some
other bug
that we have all missed.

If you like you can send me a zipped stripped down non-sensitive
version of
what you have. Make a copy of your book, prune as much as possible in
terms
of data, charts, sheets and not least code; leaving just enough to
demonstrate the problem.

Regards,
Peter T
pmbthornton gmail com

"bart13" wrote in message
...-

Yes - very much so. The straight loop (as in "for i = 1 to
activesheet.chartobjects.count" ) has wrong results, just like
every
other attempt and regardless of whether the if test was being used
or
not. There are 6 charts on the tab, only three show up in both the
actual text file and the debug print statement.

No matter what the code is, charts are being skipped.

To state it another way, Pict.Name and Pict.Chart.Name return the
exact
same matching values - every time. But both only return three names
and
there are six charts, and all six have unique names in the name box.

No matter if I look at the output in the text file or from the debug
print statement, its the same issue. Six charts in the tab, only
three
recognized.


And I do betray my ancient status as a coder. The last time I did
any
Basic coding, there was no such thing as a for each loop so the "for
i
= 1 to ..." is normal to me.

I did double check the name box again for all six charts, and all
the
names are both unique and do not contain punctuation either (unless
an
underscore is considered punctuation).

I'm obviously missing something since it appears you're saying I
have
dupe names, but I have no way of which I'm aware to change the
duplicate ones nor is there any code that I can run which will
distinguish the dupes.

The only way that works on new copied charts is not to change the
name
but rather hard code it into a select case set of statements based
on
the "Chart ###" name.

Are you saying that this is expected behavior and an open and old
bug
(or something similar), and that I should just continue to do the
hard
coding and work around? I'm not trying to be accusatory, just in
case.

Regards,
bart


Peter T;678438 Wrote:--
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[color=darkred][i]
same).

Regards,
Peter T

Originally Posted by Peter T

I also tried to reply to your off-line email but it bounced back. If you
don't get the second attempt maybe contact me again with a different email
address.

Regards,
Peter T