INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Jobs

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 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([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

    .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

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

My Archive

Resources

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close