drWatson
Board Regular
- Joined
- Feb 9, 2007
- Messages
- 55
Hello,
A VB application uses Excel worksheets as templates, and exports charts as GIF images.
The template xls file contains:
1. a worksheet with 'source data'
2. a pivot table report generated from 'source data'
3. a chart report, generated from the pivot table
In operation, the VB application loads new data into the data worksheet, iterates over all pages/fields in the pivot table (refreshing chart data), and exports a GIF version of each graph.
The amount of data is unpredictible, so the amount of rows and columns in the pivot table report changes at each iteration. A routine calculates the exact data range that should be plotted on the chart. When the range is defined, I use it to change the chart's data source. I do so using the chart object's SetSourceData method, or ChartWizard method, specifying a new source. Both work in most cases, but for one specific chart, both method fail equally.
This particular chart doesn't look very much different from other charts in other worksheets I have. It's a stacked column chart, with 12 to 30 data series. I thought at first it would be a memory problem, so I removed all font formatting, and reduced the amount of data, but the method still failed. When I right-click the chart in Excel, the "Source Data" option is disabled in the menu, contrary to the other charts for which the methods work.
All charts (and workbooks) were created using XL97, then edited using Excel 2002 (on Windows XP).
- If I open the original version of the workbook, and right-click the graph, the "Source Data" menu item is correctly enabled.
- If, using this same workbook, I create a new chart report using the original pivot table, the "Source Data" for this new chart is DISABLED.
- If a create a workbook from scratch, copy-paste my data into it, recreate the pivot table and its associated chart report, the "Source Data" menu option is DISABLED.
I can't figure out whether this is linked to a particular setting in the chart, the data itself.
I still can't rule out it's memory-related: in a previous approach, I used to iterate over all SERIES in the chart SeriesCollection and change each series's formula to include the full range of data. This would work for a couple of graphs, then after three or four graph's I'd get another runtime error (method 'CurrentPage' of object 'pivotfield' failed, at which point the Visual Basic debugger would lose track of my chart object (all properties/method invocations failed).
And now, I'm trying to get the ChartWizard to do the job for me, but as said, it won't work in all cases.
I hope someone out there can give a hand, suggest a workaround or an idea for further debugging.
Thanks in advance for your time
[Excel97, on Windows XP
]
A VB application uses Excel worksheets as templates, and exports charts as GIF images.
The template xls file contains:
1. a worksheet with 'source data'
2. a pivot table report generated from 'source data'
3. a chart report, generated from the pivot table
In operation, the VB application loads new data into the data worksheet, iterates over all pages/fields in the pivot table (refreshing chart data), and exports a GIF version of each graph.
The amount of data is unpredictible, so the amount of rows and columns in the pivot table report changes at each iteration. A routine calculates the exact data range that should be plotted on the chart. When the range is defined, I use it to change the chart's data source. I do so using the chart object's SetSourceData method, or ChartWizard method, specifying a new source. Both work in most cases, but for one specific chart, both method fail equally.
This particular chart doesn't look very much different from other charts in other worksheets I have. It's a stacked column chart, with 12 to 30 data series. I thought at first it would be a memory problem, so I removed all font formatting, and reduced the amount of data, but the method still failed. When I right-click the chart in Excel, the "Source Data" option is disabled in the menu, contrary to the other charts for which the methods work.
All charts (and workbooks) were created using XL97, then edited using Excel 2002 (on Windows XP).
- If I open the original version of the workbook, and right-click the graph, the "Source Data" menu item is correctly enabled.
- If, using this same workbook, I create a new chart report using the original pivot table, the "Source Data" for this new chart is DISABLED.
- If a create a workbook from scratch, copy-paste my data into it, recreate the pivot table and its associated chart report, the "Source Data" menu option is DISABLED.
I can't figure out whether this is linked to a particular setting in the chart, the data itself.
I still can't rule out it's memory-related: in a previous approach, I used to iterate over all SERIES in the chart SeriesCollection and change each series's formula to include the full range of data. This would work for a couple of graphs, then after three or four graph's I'd get another runtime error (method 'CurrentPage' of object 'pivotfield' failed, at which point the Visual Basic debugger would lose track of my chart object (all properties/method invocations failed).
And now, I'm trying to get the ChartWizard to do the job for me, but as said, it won't work in all cases.
I hope someone out there can give a hand, suggest a workaround or an idea for further debugging.
Thanks in advance for your time
[Excel97, on Windows XP
]
