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