Apologies in advance for the long post!
I have to change the colours of the slices in a pie chart to automatic, using COM automation. In other words, I need to programmatically achieve the same as if I had right-clicked a pie chart in Excel (2007), selected "Format Data Series...", selected the Fill category on the left, set it to "Automatic", and have "Vary Colours By Slice" ticked.
When the fill is set to automatic, then each slice gets a unique colour (this is the default setting in Excel). I'm in a situation where I have pie charts (both 3D and 2D) with their Fill set to "Solid Fill", meaning all slices have the same colour, and I need to get this back to automatic, so that each slice has its own colour.
I can obtain the chart object, and set its "Vary Colours By Slice" property to true (VB):
Dim group As Excel.ChartGroup = CType(chartObject.Chart.PieGroups(0), Excel.ChartGroup) group.VaryByCategories = True
However, I cannot figure out how to set the Fill itself to "Automatic". I guess a solution would be to assign different colours to each slice myself, but I'd really
prefer to have Excel do this automatically.
The format of the data series can be accessed by using (following on from above code):
Dim dataSeries As Excel.Series = CType(group.SeriesCollection(1), Excel.Series)
property (of type Microsoft.Office.Core.MsoFillType) allows me to distinguish between a solid (all slices the same colour) and automatic (each slice a different colour) fill:
= automatic assignment
= all slices the same colour
property is read-only, and doesn't allow me to change it that way. Instead, one is supposed to use methods, also part of Microsoft.Office.Interop.Excel.FillFormat, to set the fill type (e.g. Solid(), Patterned() etc). It's just that there's no method to set it to automatic.
So, do any of the few people who survived my long-winded explanation have any idea on how to achieve this?
P.S. An example Excel file is available here: http://www.easy-share.com/1915820675/Demo.xls
]. My apologies for the two enforced waits on the download link; I didn't have time to find a better free hosting service. I hate those delays too.