If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? -- bart13 |
#2
|
|||
|
|||
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
|
|||
|
|||
Bug with Chart.Name?
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 |
#4
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
Bug with Chart.Name?
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 |
#7
|
|||
|
|||
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
|
|||
|
|||
Bug with Chart.Name?
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=blue][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[color=green][i] 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 |
#9
|
|||
|
|||
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
|
|||
|
|||
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" 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 |
|
Thread Tools | |
Display Modes | |
|
|