>>>Is there a VCX or OCX (free) that I can use to generate graphics. I do not like MSGRAPH, I can not make it work.
>>Hi,
>>
>>MSGraph is not very robust, and it is impossible to manipulate certain items. I recently completed a huge project involoving more than 500 graphs. I used Excel as an ole server object. This worked exceptionally well. The performance was a little slow, but was not an issue because the graphs were printed quarterly.
>>
>>I used VB syntax from VFP to requery external data in Excel through ODBC. After the data wa refreshed, I printed the graphs from the Excel server object. It looked like VFP was printing the graphs to the user. Works like a charm. Send me some email if you would like to see some sample code.
>
>James, if it's not a problem, I'd like to get whatever you can send.
>
>TIA
Ok, here you go... Let me know if you need more info or clarification.
My application uses an OLE server object that creates an instance of Excel. My graphs are stored in several workbooks becasue there are so many. I first open Excel (If it isn't already open and then open my workbook)
Here's a code sample for doing that:

* Program...........: AOLEEXCEL.GETCURRENTINSTANCE
* Author............: James M. Weil
* Project...........: Quality Management Program
* Created...........: 08/17/1998 11:36:45
* Copyright.........: (c) The Bronx Health Plan, 1998
*) Description.......: Gets the current instance of Excel OleServer
*) : and opens the workbook if not already open.
* Calling Samples...:
* Parameter List....:
* Major change list.:

LOCAL loOLEServer, lcOldError, llFound, lcWorkBook

loOLEServer = GETOBJECT( , THIS.cOLERegName )
llFound = .F.
*-- Strip out the path portion of This.cWorkBook
lcWorkBook = UPPER(SUBSTR(This.cWorkbook, RAT('\',This.cWorkbook)+1))
*------------------------------------------------------------------------
*-- Verify that a workbook is open and then loop though all the open
*-- workbooks to find the one we want.
*------------------------------------------------------------------------
IF loOLEServer.APPLICATION.Workbooks.COUNT > 0
FOR i = 1 TO loOLEServer.APPLICATION.Workbooks.COUNT
loOLEServer.APPLICATION.Workbooks(i).ACTIVATE
IF loOLEServer.APPLICATION.ActiveWorkbook.NAME = lcWorkBook
llFound = .T.
EXIT
ENDIF
ENDFOR
ENDIF

*------------------------------------------------------------------------
*-- Open our workbook
*------------------------------------------------------------------------
IF !llFound
loWorkbook=loOLEServer.APPLICATION.Workbooks.OPEN(THIS.cWorkbook)
ENDIF
*------------------------------------------------------------------------

RETURN loOLEServer


After that, I execute queries in Foxpro on data that creates tables. These tables are the ODBC data source for an Excel Worksheet. A graph was created based on that data. When the data changes, the graph is automatically updated. Everything is controlled from Foxpro, right down to the chart titles. It is important to remember to close the tables in Foxpro before trying to refresh the data in Excel. Another sample:

LPARAMETERS tcValue

LOCAL ldDate

ldDate = ALLT(STR(YEAR(DATE())-1))
#DEFINE TITLE_LOC1 "TBHP-Quality Management Department" + CHR(13) + ;
ldDate + " QARRs: Immunizations"

SELECT backhc.i2, backhc.i2desc, backhc.compchc, backhc.ratechc, backhc.sampchc, ;
backhc.comptbhp, backhc.ratetbhp, backhc.samptbhp, ;
ALLTRIM(backhc.lab) + " (" + ;
ALLTRIM(STR(backhc.sampchc)) + ")" AS LABEL ;
FROM backhc ;
WHERE ALLTR(backhc.hccode) = tcValue ;
AND backhc.I1 = '1' ;
INTO TABLE j:\prod\vfp\qarr\immunehc ;
ORDER BY 1
USE IN immunehc

THISFORM.oExcel.oOleApp.APPLICATION.ActiveWorkbook.Worksheets("Immunizations Data HC").ACTIVATE()
THISFORM.oExcel.oOleApp.APPLICATION.SELECTION.QueryTable.REFRESH('False')
THISFORM.oExcel.oOleApp.APPLICATION.ActiveWorkbook.Charts("Immunizations Chart HC").ACTIVATE()
THISFORM.oExcel.oOleApp.APPLICATION.ActiveChart.Unprotect()
THISFORM.oExcel.oOleApp.APPLICATION.ActiveChart.HasTitle = .T.
THISFORM.oExcel.oOleApp.APPLICATION.ActiveChart.ChartTitle.CAPTION = ;
TITLE_LOC1 + CHR(13) + tcValue + " (2 Year Olds)"

Notice the VB syntax embedded in the VFP syntax. SELECTION.QueryTable.REFRESH('False') executes the remote query on the Foxpro table. I set up a single ODBC data source in Excel based on Freetables where my application data will be kept on the server. Problem with this approach is it makes it hard to be multiuser. Thankfully, my application doesn't need to be multiuser, but if it did, I would probably put the data and workbooks in a temp folder on the user's harddrive.

Since I have a reference to my Ole object on the form level I can send print commands to Excel like this:
THISFORM.oExcel.oOleApp.APPLICATION.ActiveWorkbook.ActiveSheet.Printout()

It looks as though Foxpro is printing another report, when in fact Excel is doing all the work. I do this because the Graphs print much better from Excel. They look better too. My graphs are dynamic and constantly changing. This is why I chose to use this approach. It was a lot of work learning how to put all the pieces together, but the end result is pretty slick. By the way, I display the graphs in an embedded ole object on the form, so the user can see the graphs as they run.