Accessing Excel chart formulas from VB

[tbarone]

Here is my situation, I have the simple problem of trying to access an excel chart series formula from VB.

The formula looks like this:

=SERIES("2000-01",Data!$E$587:$E$632,Data!$F$587:$F$632,13)

I want to import this formula from the excel chart into vb, change it, and then save it?

Thanks,
Tony Barone

 

[CajunCenturion]

You might the using the .Formula property of the Range object.

TheFormula = WksSheet.Range("A1&quot.Formula
TheFormula = <YourExpression>
WkSheet.Range(&quot;A1&quot.Formula = TheFormula
Good Luck
------------
Select * from Users where Clue > 0
0 rows returned

 

[tbarone]

I tried &quot;theformula = mybook.Worksheets(&quot;Activity vs Max Q&quot.range(&quot;ACT_VIS&quot.Formula

where Activity vs. Max is the name of the sheet with the chart and ACT_VIS is the name of the series on the chart.

It gave me the error Subscript is out of range. I know the book is opened because at the same time I am pulling other data from cells from other worksheets in the book.

Thanks,
Tony Barone

 

[CajunCenturion]

I would guess that the problem is with ACT_VIS - The Range object is expecting a Cell Reference. I would suggest changing the .Range parameter a cell reference, specifically, the one which contains the formula.

On the other hand, if your looking for the Series formula from the Series object, you may want to try a reference similar to the following:

Worksheets(&quot;sheet1&quot.ChartObjects(1).Chart. _
SeriesCollection(1).Formula Good Luck
------------
Select * from Users where Clue > 0
0 rows returned

 

[tbarone]

What would I put in for the name of the chart object?Does this formuls look correct:

mybook.Worksheets(&quot;Activity vs Max Q&quot.ChartObjects_(?).Chart.SeriesCollection(&quot;ACT_VIS&quot.Formula

Thanks,
Tony Barone

 

[CajunCenturion]

ChartObject is a collection, so in the parenthesis you would identify the chart, either by index number, or by key name of the chart you interested in
Good Luck
------------
Select * from Users where Clue > 0
0 rows returned