Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Marcia Akins
  Where is Marcia Akins?
 Akorn
 Ohio - United States
 Marcia Akins
 To: CJ Wiley
  Where is CJ Wiley?
 Hideaway
 Texas - United States
 CJ Wiley
 Tags
Subject: RE: I found the answer
Thread ID: 104282 Message ID: 104468 # Views: 5 # Ratings: 1
Version: Visual FoxPro 9 Category: Microsoft Office Automation
Date: Wednesday, August 16, 2006 8:42:54 PM         
   


I assumed I was using automation because this isn't the GENGRAPH program and am using the OLEGraph with automation commands.

You can automate MsGraph. It is, after all, only a cut down version of Excel's graphing engine.

This code shows you how to update a seed graph in a general foeld using MsGraph:

*** Now build the string we need to update the graph
*** in the general field
lcGraphData = ""
	
SELECT csrResults
lnFieldCount = FCOUNT()
	
*** Build tab-delimited string of field names:
FOR lnFld = 1 TO lnFieldCount  
  lcGraphData = lcGraphData + FIELD( lnFld ) ;
  + IIF( lnFld < lnFieldCount, CHR( 9 ), CHR( 13 ) + CHR( 10 ) )
ENDFOR

*** Concatenate the data, converting numeric fields to character:
SCAN
  FOR lnFld = 1 TO lnFieldCount  
    lcGraphData = lcGraphData + TRANSFORM( EVALUATE( FIELD( lnFld ) ) ) + ;
      IIF( lnFld < lnFieldCount, CHR( 9 ), CHR( 13 )  + CHR( 10 ) )
  ENDFOR
ENDSCAN

GO TOP IN csrResults

*** OK, ready to update the graph
SELECT csrGraph
APPEND GENERAL oleGraph CLASS "MsGraph.Chart" DATA lcGraphData


To do the same thing using Excel automation, the code looks like this:

LPARAMETERS tnPlotBy
*** If we do not have a parameter of how to plot the graph
*** default to columns
tnPlotby = IIF( INLIST( tnPlotby, xlRows, xlColumns ), tnPlotBy, xlColumns )

loXl = CREATEOBJECT( 'Excel.Application' )
*** Now add a workbook so we can populate the active worksheet
*** with the data from the query results
loWB = loXl.Workbooks.Add()

*** Now fill in the data
WITH loWb.ActiveSheet
  *** Give it a name so we can reference it
  *** after we add a new sheet for the chart
  .Name = "ChartData"
  *** Get the number of field in eachs record
  lnFldCount = FCOUNT( 'csrResults' )
  *** Add one because copying the data to the clipboard
  *** adds a row for the field names
  lnRecCnt = RECCOUNT( 'csrResults' ) + 1
  SELECT csrResults
  GO TOP
  *** Copy to clipboard with fields delimited by tabs
  _VFP.DataToClip( 'csrResults', RECCOUNT( 'csrResults' ), 3 )
  *** Get the range of the data in the worksheet
  lcCell = 'A1:' + CHR( 64 + lnFldCount ) + TRANSFORM( lnRecCnt )
  *** And paste it in
  .Paste( .Range( lcCell ) ) 
  *** But now we have to make sure that cell A1 is blank
  *** Otherwise the chart is not created correctly
  .Range( "A1" ).Value = ""
  GO TOP IN csrResults
ENDWITH
loChart = loWB.Charts.Add()		

WITH loChart	
  *** Generate the chart from the data in the worksheet
  .SetSourceData( loWB.Sheets( "ChartData" ).Range( lcCell ), tnPlotBy )
  *** Set the chart type
  .ChartType = Thisform.cboGraphType.Value
  loXl.Visible= .T. 
  *** Save to a temporary file
  lcFileName = SYS( 2015 ) + '.xls'
  loChart.SaveAs( FULLPATH( CURDIR() ) + lcFileName )
ENDWITH
*** and quit the application
loXl.Quit()

*** And return the name of the file
RETURN lcFileName



Regards,
Marcia G. Akins
Tightline Computers, Inc.


ENTIRE THREAD

Graph Background color? Posted by CJ Wiley @ 8/14/2006 4:49:45 PM
RE: Graph Background color? Posted by Marcia Akins @ 8/14/2006 6:02:05 PM
OLEGraph in a VFP Report Posted by CJ Wiley @ 8/14/2006 7:56:01 PM
I found the answer Posted by CJ Wiley @ 8/15/2006 5:55:04 PM
RE: I found the answer Posted by Marcia Akins @ 8/16/2006 12:30:31 PM
RE: I found the answer Posted by CJ Wiley @ 8/16/2006 5:26:55 PM
RE: I found the answer Posted by Marcia Akins @ 8/16/2006 8:42:54 PM