.FormulaR1C1 or .Formula Error in Excel 2010

[chieh]

Hello Everyone,

Please help me with an issue I couldn't figure out why....

Thank you kindly.
Chieh


* Description of my program and problem I am facing (Note && or * is a comment in VFP)
* ===================================================================================
* My excel file has two worksheets:
* 1st sheet is data, contains running months and marketvalue
* i.e. Jan 2010 1,023,003
* Feb 2010 1,345,000
* Mar 2010 1,340,000
* ;;
* ;;
* ;; and so on....
*
* 2nd sheet is a chart showing a bar chart of rolling 12 months of
* marketvalue by month
* i.e. From Mar 2009 to Mar 2010,
*
* My VBA program (written in Visual Foxpro (VFP), which is very similar to
* Excel VBA code)
*
* The code belows is a automated program design to import the new
* marketvalue data from another file (let say I am importing Apr 2010
* data), the program then append a new new
* line in the Data sheet and update it with new month and new
* marketvalue (i.e. Apr. 2010., 1,540,000). And then programmically
* retreive the old chart series formula and update the row start position of
* the data series from Mar. 2009 to Apr. 2010 and row end position of the
* data series from Mar. 2010 to Apr. 2010
*
* This all works with Excel file generated in Excel 2003 and earlier, However
* then I run the same code with Excel file created in Excel 2010, I get the
* following message...
*
*========================================================================================
*
* <<< Member ACTIVECHART does not evaluate to an object. >>>
*
* The line of code where error are

* * Retrieve Old Formula
* lcOldFormula = .ActiveChart.SeriesCollection(1).FormulaR1C1
*
* and this
*
* * Update the formulaR1C1
* .ActiveChart.SeriesCollection(1).Formula = lcNewFormula
*
*========================================================================================
*
* See my program below, I have taking out some code that not relate to
* this issue, the actual program is longer and has more detail.
*

* Instantiate Excel Object....
PRIVATE poExcel
PoExcel = .NULL.
poExcel = CreateObject([Excel.Application]) && Create it

* Sample test file that has a chart and data sheet
LOCAL lcFileNameWithPath, lcFileNameOnly
lcFileNameWithPath = "c:\temp.xlsx"
cFileNameOnly = "temp.xlsx"

WITH poExcel
* Debug mode
.DisplayAlerts = .T. && Turn off displaying of alert.
.ScreenUpdating = .T. && This turns off screen update, for debug
.Visible = .T. && To see the report eate action during debug mode

SET STEP ON && Debug, set the step on

* 2nd parameter is to prevent prompt for "update links" being prompted poWorkBook = .workbooks.open(lcFileNameWithPath, 0)

* Update Chart
.Sheets("CHART").Activate
.Sheets("Chart").Select
* Retrieve old Formula...
lcOldFormula = .ActiveChart.SeriesCollection(1).FormulaR1C1

* E.g. chart FormulaR1C1 ... =
* "=SERIES(,data!R9C1:R21C1,data!R9C2:R21C2,1)"
* When new row of data has been added by the user in the data
* page rebuild the formula to include in the chart page
*
* Build the new formulaR1C1
lcNewFormula = "=SERIES(,DATA!R" + lcStartPos + "C1:R" + lcEndPos + ;
"C1," + "DATA!R" + lcStartPos + "C2:R" + lcEndPos + ;
"C2,1)"

* Update the formulaR1C1
.ActiveChart.SeriesCollection(1).Formula = lcNewFormula

ENDWITH