×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Microsoft: FoxPro FAQ

## Excel

Graphing With Excel Function by baltman
Posted: 4 Apr 04 (Edited 28 Sep 06)

*:*************************************
*:* Coded by Brian Altman
*:*************************************
*:* Save this portion as a prg and run
*:*
*:* To make use with your own data,
*:* make sure to remove sample data logic.
*:*
*:* There are additional settings and
*:* functionality not demonstrated.
*:* For example, a Pie chart has no X axis,
*:* but this function does not have handling for it.
*:*
*:* Parameters:
*:* lcDataRange - Excel's Range where the graph data is located
*:* lcChartType - Excel Graph Type Name, see DEFINEs below
*:* lcChartTitle (optional) - Chart's Main Title
*:* lcXaxisTitle (optional) - X Axis Title
*:* lcYaxisTitle (optional) - Y Axis Title
*:* lcXAxisLabel (optional) - Excel's Range where the X axis label data is located
*:* lnScaleFromDefault (optional) - a Number used to scale the graph's size
*:*************************************

FoxPro programming rocks!

#### CODE

MakeExcelGraph([B1:D50],[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 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
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([B]+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

.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

Back to Microsoft: FoxPro FAQ Index
Back to Microsoft: FoxPro Forum

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!