>>I have a custom class that is used to assist with Excel automation. I am trying to add a function to set the range of the labels for the x axis. I have tried many variations of the syntax but can't seem to get it right. My current try is
>>
>>function SetXLabels(cRange)
>> oSheet = this.oExcel.ActiveSheet
>> this.oExcel.ActiveChart.SeriesCollection(1).XValues = oSheet.range(cRange)
>> endfunc
>>
>>If I was working without the custom class, this would work.
>>
>>oExcel.Activechart.SeriesCollection(1).XValues = oSheet.range('A10:A22')
>>>>The function errors on the last line that says 'OLE error code 0x80020006: Unknown Name'
>>The value of cRange is "A10:A22"
>>
>>Can someone help please.
>>
>>Thanks
>
>I never used the Series Collection stuff with Excel VBA - I do use it with MSChart. What I did was get the data into the spread sheet - worked the Excel select, cell and column functions to format the data and let the excels embedded chat feature to do the series assignments.
>
>I depended a lot on VFP's auto complete from the command window to figure it out. That might be helpful in your case. It's a lot easier to figure out from the command line than it is through the debugger or constant testing and editing,
>
>In your VFP command window just type "oX=createobject('Excel.Application'):
>
>Next type "OX.". As soon as you key the dot after OX, you'll get a list of excel VBA properties and methods in the autocomplete dialog!
>
>Here's some script I wrote so long ago I can't remember how it works. The trick was to get the data to a worksheet and then reference that data using Excel's "column" and "cell" methods.
>
>This one described (i think) what the labels would look like
>
WITH .ActiveChart
>*.Axes(xlCategory).Select
>.Axes(xlCategory).TickLabels.AutoScaleFont = xlTrue
> WITH .Axes(xlCategory).TickLabels.Font
> .Name = "Arial"
> .FontStyle = "Regular"
> .Size = 8
> .Strikethrough = xlFalse
> .Superscript = xlTrue
> .Subscript = xlFalse
> .OutlineFont = xlFalse
> .Shadow = xlFalse
> .Underline = xlUnderlineStyleNone
> .ColorIndex = xlAutomatic
> .Background = xlAutomatic
> ENDWITH
> WITH .Axes(xlCategory).TickLabels
> .Orientation = xlUpward
> .NumberFormat = "mmmmm-yy"
> ENDWITH
>.ChartArea.Select
>ENDWITH
>
>this one formated the column the series label data was in (dates).
>
WITH oExcel
>.Columns("A:A").Select
>.Selection.NumberFormat = "mmm-yy"
>.....
>ENDWITH>
>* Heres some sample script that wrote the DBF data to the "sheet"
>
>(... with oSheet)
>DO WHILE LEFT(DTOS(ldCurrentDate),6)<=LEFT(DTOS(ldPeriodEnding_Date),6)
>lcDPtr=LEFT(DTOS(ldCurrentDate),6)
>lnCellRow=lnCellRow+1
>* Period date
> IF lnChart_IdxCnt>1
> .Cells(lnCellRow,3+lnColOffSet).Value=;
> EX_GetReturnData(lcIdx_Pkey1+lcDPtr,[BMR],[rate_date],llCompounding,'lnIdx1_Ctl',ldCurrentDate)
> ELSE
> ENDIF
> IF lnChart_IdxCnt>2
> .Cells(lnCellRow,4+lnColOffSet).Value=;
> EX_GetReturnData(lcIdx_Pkey2+lcDPtr,[BMR],[rate_date],llCompounding,'lnIdx2_Ctl',ldCurrentDate)
> ELSE
> ENDIF
> IF lnChart_IdxCnt>3
> .Cells(lnCellRow,5+lnColOffSet).Value=;
> EX_GetReturnData(lcIdx_Pkey3+lcDPtr,[BMR],[rate_date],llCompounding,'lnIdx3_Ctl',ldCurrentDate)
> ELSE
> ENDIF
>(..etc)
>ENDDO
>
>There is a description of this project on my
website under the projects menu - the title is something like
VFP EXCEL VBA
>
>You're going to have a lot of fun and you'll feel like king of your desk when you get the project firing on all 8's!
Thanks Terry. I found this would work.function SetXLabels(cRange)
this.oExcel.ActiveChart.Axes(1).Select
this.oExcel.ActiveChart.SeriesCollection(1).XValues = cRange
endfunc
cRange needs to be in the fomat "=Sheet1!R10C1:R22C1"
I guess that was the problem.
