Ya, it's looking like I'll have to recreate. Just means more work and
less flexibility. The chart I'm applying this to could come from any
of several sources, each with its own column positions and names, and
I was looking for a generic approach that would fit all cases. I can
derive the column names for the error bars from other column names on
the same source sheet, so if I can drop the existing error bars, I can
just rebuild from scratch; but that means recreating colour and other
style info which is not available in the source data. Maybe I can fish
that out of the DOM.
In case it helps anyone, I did eventually find what's available
through the use of ExecuteExcel4Macro at
http://support.microsoft.com/kb/128185.
There's quite a long list of available functions, but unfortunately
none dealing with retrieval of error bars.
Thanks all
Robert
On Feb 20, 3:04*am, "Andy Pope" wrote:
Hi,
First the type mismatch. Worksheet is a specific type of sheet in an excel
workbook. Along with Chart sheet and the older macro and dialog sheets.
The Sheets collection will return all of the sheet types, as objects. If you
just want the worksheets you can use the following and declare sh as
worksheet.
For Each sh In wb.worksheets
The custom error bar formula is not exposed via the object model. In fact if
you record a macro of you change the error bar from custom to Percent value
you will get Excel4Macro code.
* * ActiveChart.SeriesCollection(1).ErrorBars.Select
* * ExecuteExcel4Macro "ERRORBAR.Y(1,2,5)"
So it maybe possible to execute a command to return the formula but I'm too
young to remember the Excel4Macro syntax :)
Cheers
Andy