Formatting an excel document within Fox Pro

[johnod33]

Does anyone know where I can find a reference document for formatting an excel document within Fox Pro.
I have been trying to figure out how to total an Excel table at the end of a column not at line 65000 or what ever the limit is.
Something similar to this:

#DEFINE True .T.
#DEFINE False .F.
#DEFINE xlSaveChanges 2
#DEFINE xlDialogSendMail 2

oExcel = CREATEOBJECT ("Excel.Application&quot
oExcel.VISIBLE = .T.

WITH oExcel
.DisplayAlerts = .F.
.Workbooks.OPEN ('c:\cstcntr' + mCstcntr + '.xls')
.ROWS("1:1&quot.SELECT
.SELECTION.FONT.Bold = True
.Cells.SELECT
.Cells.EntireColumn.AutoFit
.ROWS("1:1&quot.SELECT
.SELECTION.EntireRow.INSERT
.RANGE("A1&quot.SELECT
.ActiveCell.FormulaR1C1 = 'Expense File for cost center ' + ;
mCstcntr + ' for ' + cmonth(ctod(Lmonth+'/03/03')) + ' ' + alltrim(str(year(date())))
.ROWS("1:1&quot.SELECT
.SELECTION.FONT.Bold = True
.SELECTION.FONT
.SELECTION.FONT.NAME = "Arial"
.SELECTION.FONT.SIZE = 12
.COLUMNS("D&quot.SELECT
.SELECTION.NumberFormat = "#,##0.00"
.SELECTION.WrapText = False
.SELECTION.ORIENTATION = 0
.SELECTION.AddIndent = False
.SELECTION.ShrinkToFit = False
.SELECTION.MergeCells = False
.RANGE("A1&quot.SELECT
.activewindow.CLOSE(xlSaveChanges)
.APPLICATION.Dialogs(xlDialogSendMail)
.APPLICATION.QUIT
ENDWITH

 

[kenndot]

I have looked several times for a complete reference sheet for excel formatting and have never found it, I just happen upon the answers somehow.

Sometimes if you go into VB Editor in excel and play around that will give you a good idea of the keywords to use.

I don't know specifically how to get what you're looking for, though.

 

[Mike Gagnon]

johnod33

It is unclear what you need. Are you trying to add the values in column1 and paste the answer at the end of that column? (Like one more cell below the end of column #1)?

Mike Gagnon

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

 

[johnod33]

Actually what I am looking for is a complete reference sheet for excel formatting like kenndot mentioned.
I used the summing reference as an example. So I guess that post was two questions now that I look at it.
Maganon.........
Yes I am trying to sum numeric fields in an Excel table. I am able to do it by copying the macro Excels macro recorder writes but you have to know what record the table stops at to sum it just below the last record. When you record this action you are actually choosing the last record and then telling excel to sum it. If you try this programmatically it sums at the last allowed line of an excel table. 65000 something.

 

[ramani]

Hi

Have a look in this.

http://support.microsoft.com/defaul...port/kb/articles/q252/6/15.asp&NoWebContent=1

You can download and expand to get a VFPExcel.PRG. It contains code to export a table .. insert a header row, add a total row at the end and add numeric type fields as the last row.

Could help you with the modus operandi.

ramani
(Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[Mike Gagnon]

Johnnod33

There is no actual book on "Everyting you want to know about Excel automation in FoxPro" (Maybe I should write one ;-)). Microsoft give you the access to the COM object of Excel, but its pretty much up to you to discover how to do it. But here are a couple things you can do.

[lo][li] Get Vfp7.0 at least (or obtain the intellesense class that works for VFP6.0 on UT.) Intellisense will help you discover the properties and method of the Excel object)[/li]
[li]Record a macro in Excel, cut and paste the code in VFP and translate it into VFP (Not that hard).[/li]
[li]Take a look at the Excel object model in the help file "VBAXL10.CHM" (C:\Program Files\Microsoft Office\Office10\1033).[/li]
[li] You can also find the same help file on Microsoft's site[/li]
[li]Buy the only book I know (limited in the samples)

http://www.hentzenwerke.com/catalogpricelists/autofox.htm

[/li][/ol]

Here is an example of what you asked about created mostly with an Excel macro and translated into VFP. Copy and paste into a program and run it.

LOCAL oExcel AS Excel.APPLICATION
oExcel = CREATEOBJECT("excel.application")
oWb = oExcel.workbooks.ADD()
SET step on
oSheet = oWb.ActiveSheet
WITH oSheet
   .RANGE("A2").SELECT
   .RANGE("A2").FormulaR1C1 = "1"
   .RANGE("A3").SELECT
   .RANGE("A3").FormulaR1C1 = "2"
   .RANGE("A4").SELECT
   .RANGE("A4").FormulaR1C1 = "2"
   .RANGE("A5").SELECT
   .RANGE("A5").FormulaR1C1 = "3"
   .RANGE("A6").SELECT
   .RANGE("A6").FormulaR1C1 = "4"
   .RANGE("A7").SELECT
   .RANGE("A7").FormulaR1C1 = "5"
   .RANGE("A2:A8").SELECT
   .RANGE("A8").ACTIVATE
   .RANGE("A8").FormulaR1C1 = "=SUM(R[-6]C:R[-1]C)"
ENDWITH
oExcel.Visible =.t.

Mike Gagnon

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