Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 8th 08, 04:44 AM
Junior Member
 
First recorded activity by ExcelBanter: Jun 2008
Posts: 8
Default Bug with Chart.Name?

Excel 2003 with hundreds of charts in 3 tabs and just under 200 total tabs. I'm trying to automate the export of them with a macro. The code seems fine, but all the charts are not being exported.

I've changed the chart names away from the default Chart ### via the shift click method but even though the name shows correctly in the name box at the upper left, a few are still named Chart ### when exported.

I've even tried copying a few of the charts to a brand new chart tab - no joy. There are six charts (and that's what ActiveSheet.ChartObjects.Count shows) in the new tab but only three will export. The other three that will not export were created via ctrl-c copies of one of the working ones and then changes were made through the normal UI to the date range, Chart.Name, etc.

Maybe some kind of weird corruption in the various problem charts or some unknown limit to Chart.Name... or just plain PEBKAC?

  #2   Report Post  
Old June 8th 08, 10:12 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 5,600
Default Bug with Chart.Name?

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

Excel 2003 with hundreds of charts in 3 tabs and just under 200 total
tabs. I'm trying to automate the export of them with a macro. The code
seems fine, but all the charts are not being exported.

I've changed the chart names away from the default Chart ### via the
shift click method but even though the name shows correctly in the name
box at the upper left, a few are still named Chart ### when exported.

I've even tried copying a few of the charts to a brand new chart tab -
no joy. There are six charts (and that's what
ActiveSheet.ChartObjects.Count shows) in the new tab but only three
will export. The other three that will not export were created via
ctrl-c copies of one of the working ones and then changes were made
through the normal UI to the date range, Chart.Name, etc.

Maybe some kind of weird corruption in the various problem charts or
some unknown limit to Chart.Name... or just plain PEBKAC?




--
bart13



  #3   Report Post  
Old June 8th 08, 04:39 PM
Junior Member
 
First recorded activity by ExcelBanter: Jun 2008
Posts: 8
Default

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

intChartNameLength = Len(Pict.Chart.Name) - ThisSheetNameLength
strChartName = Right(Pict.Chart.Name, intChartNameLength)
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



Quote:
Originally Posted by Peter T View Post
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

Last edited by bart13 : June 8th 08 at 07:04 PM
  #4   Report Post  
Old June 8th 08, 07:31 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default Bug with Chart.Name?

The name of an embedded chart is really the chartobject's name. This is what
you change in the name box after shift-click selecting the chart.

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


"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



  #5   Report Post  
Old June 8th 08, 10:00 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 5,600
Default Bug with Chart.Name?

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





  #6   Report Post  
Old June 9th 08, 04:21 AM
Junior Member
 
First recorded activity by ExcelBanter: Jun 2008
Posts: 8
Default

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



Quote:
Originally Posted by Peter T View Post
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

Last edited by bart13 : June 9th 08 at 04:35 AM
  #7   Report Post  
Old June 9th 08, 03:27 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 5,600
Default Bug with Chart.Name?

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" 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-





--
bart13



  #8   Report Post  
Old June 9th 08, 06:31 PM
Junior Member
 
First recorded activity by ExcelBanter: Jun 2008
Posts: 8
Default

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


Quote:
Originally Posted by Peter T View Post
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



...
  #9   Report Post  
Old June 10th 08, 04:38 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 5,600
Default Bug with Chart.Name?

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


Peter T;677841 Wrote:[color=green][i]
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


  #10   Report Post  
Old June 10th 08, 08:05 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default Bug with Chart.Name?

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" <[email protected] 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






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2007 error "some chart types cannot be combined with other chart types. Select a different chart types" roadsidetree Charts and Charting in Excel 15 June 2nd 09 10:53 AM
chart from pivot data does not update x-axis bar chart values - bug jason gers Excel Discussion (Misc queries) 0 April 3rd 07 06:34 PM
Double-stacked Bar Chart WITH a Secondary Y Axis Line chart? lpenndorf Charts and Charting in Excel 1 February 7th 07 05:32 PM
Huge differences in chart values screws my bar chart scale up. Sirritys Charts and Charting in Excel 2 June 13th 06 10:33 AM
scatter chart insists on plotting as a line chart Knows too little New Users to Excel 0 March 11th 05 07:37 PM


All times are GMT +1. The time now is 07:51 PM.

Powered by vBulletin® Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017