>>Are you open to the idea of programmatically building the charts in EXCEL? Some users like this since they can modify the charts and save them. I have lots of examples if you are interested.
>
>I am trying to do this also. I would be interested to see any examples.
>
>TIA
>
>Ian
>
>ianj@afes.com


Here is an example of a simple xy chart. Included are options for chart type, reference lines, color, interpolation, printing etc. which of course would be based on input from a screen form.

Once you get started, you can modify the chart in Excel while a macro is recording. You then can look at the macro to see what objects and properties are being set. Then use this knowledge to get you VFP program to do the work.
The Excel macros use predefined variables which I have documented as a reply to this message.




* Turn the mouse pointer into an hour glass
_SCREEN.MOUSEPOINTER = 11

* Create an Excel spreadsheet and make it visible.
oleChart=CREATEOBJECT("Excel.Sheet")
oleChart.Application.Visible = .T.

* Place data into the Excel spreadsheet.
* The following is a test data set. It actually should come from a query on a real application

oleChart.Cells(1,2).Value = "02/01/90"
oleChart.Cells(1,3).Value = "07/25/91"
oleChart.Cells(1,4).Value = "06/15/94"
oleChart.Cells(1,5).Value = "09/03/96"
oleChart.Cells(1,6).Value = "08/01/97"

oleChart.Cells(2,1).Value = "Lead"
oleChart.Cells(3,1).Value = "Arsenic"
oleChart.Cells(4,1).Value = "Barium"

oleChart.Cells(2,2).Value = 10
oleChart.Cells(2,3).Value = 11
oleChart.Cells(2,4).Value = 13
oleChart.Cells(2,5).Value = 11
oleChart.Cells(2,6).Value = 10

oleChart.Cells(3,2).Value = 5
oleChart.Cells(3,3).Value = 4
* leave a data gap to show interpolation
oleChart.Cells(3,5).Value = 7
oleChart.Cells(3,6).Value = 5

oleChart.Cells(4,2).Value = 2
oleChart.Cells(4,3).Value = 1
oleChart.Cells(4,4).Value = 1.5
oleChart.Cells(4,5).Value = 3
oleChart.Cells(4,6).Value = 2


* the number of series and distinct x values in the table can be set programmically from the query
nSeriescnt = 3
nXvalues = 5

cXtitle = "Sampling Period"
cYtitle = "Concentration"
cMaintit = "Metals Concentration (ug/l)"

* set the chart type to plot both symbols and lines
* see the case statement later for other chart types
cCharttype = "Symbols & Lines "

* set the following flags to .T. if horizontal and vertical reference lines are needed
lWantvline = .F.
lWanthline = .F.

* set the following flag to .F. if you want a white background
lShadeback = .T.

* set the following flag to .F. if you want the symbols and lines to be black
* rather than in color which shows up better on the screen
lWantcolor = .T.

* if the data has gaps where it does not have a y value for every x value then interpolate
lInterpolate = .T.

* if the chart just needs to be printed rather than viewed, change the following flag to .T.
lJustprint = .F.


DO CASE
CASE cCharttype = "Symbols & Lines "
nFormat = 2
CASE cCharttype = "Symbols Only"
nFormat = 1
CASE cCharttype = "Lines Only"
nFormat = 6
CASE cCharttype = "Symbols with Log Y Scale"
nFormat = 4
OTHERWISE
nFormat = 2
ENDCASE


* Create the Graph object and set the chart position so that it is below the data
oleChart1 = oleChart.ChartObjects.Add(10,(13*nSeriescnt)+20,420,(13*nSeriescnt)+200)

* Setup up spreadsheet page header and footer
oleChart.ChartObjects(1).Chart.PageSetup.CenterHeader = ""
oleChart.ChartObjects(1).Chart.PageSetup.CenterFooter = ""


* chartwizard parameters
* object.ChartWizard(source, gallery, format, plotBy, categoryLabels, seriesLabels, hasLegend, title,
* categoryTitle, valueTitle, extraTitle)

* Create the graph by calling the Excel Chart Wizard, then select and copy the results before qutting Excel.
oleChart.ChartObjects(1).Chart.ChartWizard(oleChart.Range(oleChart.Cells(1,1),oleChart.Cells(nSeriescnt+1,nXvalues+1)),-4169,nFormat,1,1,1,1,cMaintit,cXtitle,cYtitle,"")

IF lInterpolate
* set the chart options to interpolated (xlInterpolated is 3)
oleChart.ChartObjects(1).Chart.DisplayBlanksAs = 3
ENDIF

IF NOT lShadeback
* set the background to a solid white fill
oleChart.ChartObjects(1).Chart.PlotArea.Interior.ColorIndex = 2
oleChart.ChartObjects(1).Chart.PlotArea.Interior.Pattern = 1
ENDIF

IF NOT lWantcolor
* reset all the symbols and lines to black
FOR nI = 1 TO nSeriescnt
IF cCharttype <> "Symbols Only"
cOlecommand = "oleChart.ChartObjects(1).Chart.SeriesCollection(" + LTRIM(STR(nI,3,0)) + ;
").Border.ColorIndex = 1"
&cOlecommand
ENDIF
IF cCharttype <> "Lines Only"
cOlecommand = "oleChart.ChartObjects(1).Chart.SeriesCollection(" + LTRIM(STR(nI,3,0)) + ;
").MarkerBackgroundColorIndex = 1"
&cOlecommand
cOlecommand = "oleChart.ChartObjects(1).Chart.SeriesCollection(" + LTRIM(STR(nI,3,0)) + ;
").MarkerForegroundColorIndex = 1"
&cOlecommand
ENDIF
NEXT
ENDIF

IF lWantvline
oleChart.ChartObjects(1).Chart.Axes(1).HasMajorGridlines = 1
ELSE
oleChart.ChartObjects(1).Chart.Axes(1).HasMajorGridlines = 0
ENDIF

IF lWanthline
oleChart.ChartObjects(1).Chart.Axes(2).HasMajorGridlines = 1
ELSE
oleChart.ChartObjects(1).Chart.Axes(2).HasMajorGridlines = 0
ENDIF


IF lJustprint
oleChart.ChartObjects(1).Chart.PrintOut
oleChart.Application.Quit
ENDIF

* Turn the mouse pointer back to normal
_SCREEN.MOUSEPOINTER = 0

WAIT WINDOW "PRESS RETURN"