VFP 6 and EXCEL

[HERVEC1]

I would like to know if somebody know a way to export data from VfP base to excel and make a graph

I found a prg but , the graph is cut in half

ex: Jan to december I have just Jan to June . But when I'm in excel, if I select again the data and I ask the graph it's ok I don't know why ???

has somebody a sample of programm in vfp 6 ?
Thanks in advance .. Hc

 

[Mike Gagnon]

HERVEC1

has somebody a sample of programm in vfp 6 ?

There is an example of automating Excel in the "solutions" provided with FoxPro. The path on my system is:
C:\Program Files\Microsoft Visual Studio\MSDN98\98VS\1033\SAMPLES\VFP98\Solution

Can you post some of the code that you are using?

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first

 

[HERVEC1]

So, I already found the program in c:\.. solution
but It use Graph.exe and you can't print the graph
I prefer use Excel . I would lke to send data in cells and call the graph after that I can print thru excel and come back in VFP when Ileave excel.

Sure I can send my prg: (but it doesn't work very well !


LOCAL nTotSeries,i
public oXLChart1
oXLChart1 = GetObject('','excel.chart')
oXLChart1.application.charts[1].haslegend = .F.
oXLChart1.application.visible = .T.
oXLChart1.application.worksheets(1).cells().clear

_cliptext = ""+TAB+N_LOC+TAB+N1_LOC+TAB+"DOTATION"+CRLF+;
"JAN"+TAB+"11"+TAB+"22"+TAB+"100"+CRLF+;
"FEV"+TAB+"33"+TAB+"44"+TAB+"100"+CRLF+;
"MAR"+TAB+"55"+TAB+"55"+TAB+"100"+CRLF+;
"AVR"+TAB+"11"+TAB+"22"+TAB+"100"+CRLF+;
"MAI"+TAB+"33"+TAB+"44"+TAB+"100"+CRLF+;
"JUL"+TAB+"10"+TAB+"20"+TAB+"100"+CRLF+;
"AOU"+TAB+"33"+TAB+"44"+TAB+"100"+CRLF+;
"SEP"+TAB+"55"+TAB+"55"+TAB+"100"+CRLF+;
"OCT"+TAB+"11"+TAB+"22"+TAB+"100"+CRLF+;
"NOV"+TAB+"33"+TAB+"44"+TAB+"100"+CRLF+;
"DEC"+TAB+"55"+TAB+"55"+TAB+"100"+CRLF





oXLChart1.application.worksheets(1).cells(1,1).pastespecial
oXLChart1.application.charts[1].autoformat(4,1)

* Remove extra series added by Excel
nTotSeries = oXLChart1.application.charts[1].SeriesCollection().count
FOR i = m.nTotSeries to 4 STEP -1
oXLChart1.application.charts[1].SeriesCollection(m.i).delete
ENDFOR
oXLChart1.application.charts[1].haslegend = .T.


thanks HC

 

[danceman]

have you looked at faq184-2215. this has always helped me in doing COM. Attitude is Everything

 

[Mike Gagnon]

Herve

but It use Graph.exe and you can't print the graph

You can, just click on it,copy and paste in Word and print.
But the sample I was refering to is under Activex->Automation->Automate a Microsoft Excel spreadsheet.

But if you want to use Excel, you code seems to be missing somethings
The value of n_loc and n1_loc and I assume that the value of CRLF is CHR(13)+CHR(10)
And the value of TAB is CHR(9)

But if I use the above values like this:
#DEFINE TAB CHR(9)
#DEFINE CRLF CHR(13)+CHR(10)
n1_loc = ""
n_loc=""

I'm able to get to Excel show me a chart, and if I go to print preview, I see the whole chart on screen (I don't have a printer here). But I don't seem to be able to duplicate your problem, short of being to print.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first

 

[Mike Gagnon]

Herve

Here is your solution. The idea is to reselect your range and recreate the chart. Instead of specific numbers in the range you may also use variables depending on the record count:

#DEFINE TAB CHR(9)
#DEFINE CRLF CHR(13)+CHR(10)
N_LOC =""
N1_LOC =""
LOCAL nTotSeries,i
public oXLChart1
oXLChart1 = GetObject('','excel.chart')
oXLChart1.application.charts[1].haslegend = .F.
oXLChart1.application.visible = .T.
oXLChart1.application.worksheets(1).cells().clear

_cliptext = ""+TAB+N_LOC+TAB+N1_LOC+TAB+"DOTATION"+CRLF+;
         "JAN"+TAB+"11"+TAB+"22"+TAB+"100"+CRLF+;
         "FEV"+TAB+"33"+TAB+"44"+TAB+"100"+CRLF+;
         "MAR"+TAB+"55"+TAB+"55"+TAB+"100"+CRLF+;
         "AVR"+TAB+"11"+TAB+"22"+TAB+"100"+CRLF+;
         "MAI"+TAB+"33"+TAB+"44"+TAB+"100"+CRLF+;
         "JUL"+TAB+"10"+TAB+"20"+TAB+"100"+CRLF+;
         "AOU"+TAB+"33"+TAB+"44"+TAB+"100"+CRLF+;
         "SEP"+TAB+"55"+TAB+"55"+TAB+"100"+CRLF+;
         "OCT"+TAB+"11"+TAB+"22"+TAB+"100"+CRLF+;
         "NOV"+TAB+"33"+TAB+"44"+TAB+"100"+CRLF+;
         "DEC"+TAB+"55"+TAB+"55"+TAB+"100"+CRLF
oXLChart1.application.worksheets(1).cells(1,1).pastespecial
oXLchart1.ActiveChart.SetSourceData(oXLChart1.Sheets("sheet1").range("A2:D12")) && Line was added
oXLcHART1.ActiveChart.sizewithWindow = .t. && Line was added
oXLChart1.application.charts[1].autoformat(-4100,8)
* Remove extra series added by Excel
nTotSeries = oXLChart1.application.charts[1].SeriesCollection().count
FOR i = m.nTotSeries to 4 STEP -1
    oXLChart1.application.charts[1].SeriesCollection(m.i).delete
ENDFOR
oXLChart1.application.charts[1].haslegend = .F.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first

 

[HERVEC1]

Thanks mgagnon

they cut my french text It's not fair !! I 'll try your idea tomorrow and Vive le pastaga .

 

[Mike Gagnon]

Herve

they cut my french text It's not fair !! I 'll try your idea tomorrow and Vive le pastaga .

I know this is an english forum. Good luck! Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first