Creating a variable chart in excel

[borsker]

I am trying to create a pie chart and a column chart in excel and I am using some of the code in FAQ1251-5013 (very good stuff). However I have variable data streams, and I am not sure how to create a variable string using the code.


Here is the code. I am trying to make these first two lines variable however when I do I get an error later on in the program.




MakeExcelGraph([B150],[xlLine],[Title Describes],;
[Time Goes By],[Data Goes High],[A2:A50],1.25)

RETURN

PROCEDURE MakeExcelGraph
LPARAMETERS lcDataRange, lcChartType,lcChartTitle, lcXaxisTitle,;
lcYaxisTitle, lcXAxisLabel, lnScaleFromDefault

DO CASE
CASE lcChartType=[xlLine]
lnChartType = 4
OTHERWISE
*and so on
ENDCASE

#DEFINE xl3DArea -4098
#DEFINE xl3DBar -4099
#DEFINE xl3DColumn -4100
#DEFINE xl3DLine -4101
#DEFINE xl3DPie -4102
#DEFINE xl3DSurface -4103
#DEFINE xlCombination -4111
#DEFINE xlDoughnut -4120
#DEFINE xlRadar -4151
#DEFINE xlXYScatter -4169
#DEFINE xlArea 1
#DEFINE xlBar 2
#DEFINE xlColumn 3
#DEFINE xlLine 4
#DEFINE xlPie 5
#DEFINE xlColumnClustered 51

#DEFINE xlPrimary 1
#DEFINE xlCategory 1
#DEFINE xlValue 2
#DEFINE xlRows 1
#DEFINE xlCols 2
#DEFINE xlNone -4142
#DEFINE xlRight -4152
#DEFINE xlBottom 1
#DEFINE xlCustom -4114
#DEFINE xlAutomatic -4105
#DEFINE xlLine 1
#DEFINE xlDash 2
#DEFINE xlDot 3

IF VARTYPE(lnScaleFromDefault)=[L] OR lnScaleFromDefault=0
lnScaleFromDefault=1
ENDIF

IF VARTYPE(lcChartTitle)=[L]
lcChartTitle=[]
ENDIF

IF VARTYPE(lcXaxisTitle)=[L]
lcXaxisTitle=[]
ENDIF

IF VARTYPE(lcYaxisTitle)=[L]
lcYaxisTitle=[]
ENDIF

IF VARTYPE(lcXAxisLabel)=[L]
lcXAxisLabel=[]
ENDIF

loExcel = CREATEOBJECT([Excel.application])
WITH loExcel
.Workbooks.Add() &&or open existing
loSheet = .activesheet

WITH loSheet &&Insert some dummy data
.range([A1]).value=[X Axis Label]
.range([B1]).value=[Time Series A]
.range([C1]).value=[Time Series B]
.range([D1]).value=[Time Series C]
FOR lnAvals = 2 TO 50
.range([A]+TRANS(lnAvals))=lnAvals-2
.range(+TRANS(lnAvals))=RAND()*lnAvals
.range([C]+TRANS(lnAvals))=RAND()*lnAvals*1.5
.range([D]+TRANS(lnAvals))=RAND()*lnAvals*3.5
ENDFOR
ENDWITH &&end of dummy data insert

.Charts.Add()
.ActiveChart.ChartType = lnChartType
.ActiveChart.SetSourceData(loSheet.Range(lcDataRange),xlCols)
.ActiveChart.Location(2,loSheet.Name)
.ActiveChart.HasLegend = .T.
.ActiveChart.Legend.Position = xlBottom

WITH .ActiveChart
.HasTitle = .T.
.ChartTitle.Characters.Text = lcChartTitle

IF !EMPTY(lcXAxisLabel) && use this range as X axis label
.SeriesCollection(1).XValues = loSheet.Range(lcXAxisLabel)
ENDIF

WITH .Axes(xlValue, xlPrimary) &&Y Axis
.HasTitle = .T.
.AxisTitle.Characters.Text = lcYaxisTitle
.MinimumScale = 0 && this only works for Y
ENDWITH

WITH .Axes(xlCategory, xlPrimary) &&X Axis
.HasTitle = .T.
.AxisTitle.Characters.Text = lcXaxisTitle
.TickLabelSpacing = 5
.TickMarkSpacing = 5
ENDWITH

.Axes(xlValue).MajorGridlines.Border.LineStyle = xlDot

ENDWITH

loSheet.Shapes("Chart 1").ScaleWidth(lnScaleFromDefault,.f.,0)
loSheet.Shapes("Chart 1").ScaleHeight(lnScaleFromDefault,.f.,0)
loSheet.range([A1]).select
.Visible =.t.
ENDWITH
ENDPROC


This is the code I tried instead of what really goes in the
code
USE CUSTOMERSTAT2
count to counterA
store counterA-4 to counterA

MakeExcelGraph([B2:B+TRANS(counterA)],[xlColumn],[Title Describes],;
[Time Goes By],[Data Goes High],[A2:A+TRANS(counterA)],1.25)

 

[baltman]

I think you just need a minor change to the 1st paramater.

MakeExcelGraph([B2:B]+TRANS(counterA),...

instead of

MakeExcelGraph([B2:B+TRANS(counterA)],...

Brian

 

[borsker]

that worked, thank you

 

[borsker]

New question, same kinda catagory, if i do not get any responses i will post as a new thread.

I am trying to put down more than one chart, how can i move them around so they are not on top of each other? And also can I change the width of the columns some kind of way.