Export file to Excel with formulas using VFP 8

I need to export some VFP data to an Excel file, is there any way to
include formulas when I create the file?

Stefan
Tue Apr 15 02:08:01 CDT 2008


<bob66k88@gmail.com> schrieb im Newsbeitrag
news:6e25ac0c-8fda-4753-ac01-d00180b10ca1@u12g2000prd.googlegroups.com...
>I need to export some VFP data to an Excel file, is there any way to
> include formulas when I create the file?

When you want to create an XLS file directly from VFP, you
can only store values, formula results.
On the other hand, you can use MS Office Automation via COM
interface, e.g.

oExcel = CREATEOBJECT('Excel.Application')
oExcel.Workbooks.Add()
oExcel.ActiveSheet.Range("A1:A12").Select()
oExcel.ActiveCell.FormulaR1C1 = "=SUM(RC[1]:R[9]C[1])"
oExcel.Visible = .T.

In general, in Excel you can use the Macro Recorder to see code
for almost any user action and then translate VBA to Vfp.



hth
-Stefan



--
|\_/| ------ ProLib - programmers liberty -----------------
(.. ) Our MVPs and MCPs make the Fox run....
- / See us at www.prolib.de or www.AFPages.de
-----------------------------------------------------------

bob66k88
Wed Apr 23 13:38:16 CDT 2008

On Apr 15, 12:08 am, "Stefan Wuebbe" <stefan.wue...@gmx.de> wrote:
> <bob66...@gmail.com> schrieb im Newsbeitragnews:6e25ac0c-8fda-4753-ac01-d00180b10ca1@u12g2000prd.googlegroups.com...
>
> >I need to export some VFP data to an Excel file, is there any way to
> > include formulas when I create the file?
>
> When you want to create an XLS file directly from VFP, you
> can only store values, formula results.
> On the other hand, you can use MS Office Automation via COM
> interface, e.g.
>
> oExcel = CREATEOBJECT('Excel.Application')
> oExcel.Workbooks.Add()
> oExcel.ActiveSheet.Range("A1:A12").Select()
> oExcel.ActiveCell.FormulaR1C1 = "=SUM(RC[1]:R[9]C[1])"
> oExcel.Visible = .T.
>
> In general, in Excel you can use the Macro Recorder to see code
> for almost any user action and then translate VBA to Vfp.
>
> hth
> -Stefan
>
> --
> |\_/| ------ ProLib - programmers liberty -----------------
> (.. ) Our MVPs and MCPs make the Fox run....
> - / See us atwww.prolib.deorwww.AFPages.de
> -----------------------------------------------------------

Thanks for the information. Will follow-up this week.

However, I have one more question:
Is there a way to create a character field with the formula,
and follow the formula with a character to indicate the end of the
field?
i.e. either a <CR> or <LF> or <CR><LF> like you do when creating a
'Delimited' file.

In a test, my Excel file has the formula in a cell,
if I select that cell, and at the end of the formula, hit the <Enter>
key,
the formula is evaluated.

Stefan
Thu Apr 24 02:13:45 CDT 2008


<bob66k88@gmail.com> schrieb
>
> Thanks for the information. Will follow-up this week.
>
> However, I have one more question:
> Is there a way to create a character field with the formula,
> and follow the formula with a character to indicate the end of the
> field?
> i.e. either a <CR> or <LF> or <CR><LF> like you do when creating a
> 'Delimited' file.
>
> In a test, my Excel file has the formula in a cell,
> if I select that cell, and at the end of the formula, hit the <Enter>
> key, the formula is evaluated.

I did not try that ...

>> oExcel.ActiveCell.FormulaR1C1 = "=SUM(RC[1]:R[9]C[1])"
+ Chr(13)+Chr(10)

... but would guess that the Macro Recorder might help?

>> In general, in Excel you can use the Macro Recorder to see code
>> for almost any user action and then translate VBA to Vfp.


-Stefan




--
|\_/| ------ ProLib - programmers liberty -----------------
(.. ) Our MVPs and MCPs make the Fox run....
- / See us at www.prolib.de or www.AFPages.de
-----------------------------------------------------------