CODEWITHSUNDEEP
excelsumexportvisual-foxprofoxpro
Nick
Nick

Reputation: 165

In Visual Foxpro how to export to excel with total

I'm looking to export a table from Visual Foxpro 5.0 to excel with a total for one column. I know you can calculate sum(column) but I don't know how to include this in the export.

Upvotes: 0

Views: 5608

Answers (4)

Dhugalmac
Dhugalmac

Reputation: 574

I did think of another way to get the calculated total into the Excel file, but it is sort of a 'kludge' and within the Excel file it really wouldn't be a dynamic calculation, but instead it would be a cell which could statically hold the VFP calculated value.

OK, here is the 'kludge'....
Open the VFP data table and run your totals calculation.
APPEND BLANK to add a new empty record and in one of the fields REPLACE its value with the calculated Total.

Now do your COPY TO excel file XL5
All of your data will be written and that data will include your calculated total record with its value.

NOTE: If you need the Excel rows to be in a different sequence than your original VFP data table, then prior to the COPY TO... run a SQL Query to get all of the records into your desired order - and write the results to a new Table/Cursor READWRITE.
Then you would do the Append Blank on the resultant Table/Cursor and use it to COPY TO...

Good Luck

Upvotes: 0

Cetin Basoz
Cetin Basoz

Reputation: 23797

If you need it in excel then why sum at all? Excel knows how to sum. If you really need to then you can do that by simply creating a cursor with a row added to hold sum. Something like:

select f1, f2, f3 from myTable ;
union all ;
select '', '', sum(f3) as f3 ;
from myTable group by 1,2

Creating excel data from a cursor/table is the more tricky part but there are many ways (ie: simply copying as a delimited file with header you create .CSV, which can then be opened by excel). IMHO one of the best ways is to pass the data using an ADO recordset. You can search for VFP2Excel code on the internet. I have written and posted many variations of it (I will add a link if I find one soon - for VFP5 it might need slight changes for the command\functions not available in VFP5).

(Or you might do this the other way and directly get the data from Excel using ADO - QueryTables).

OK found many links, some of these:

Vfp2Excel discussion

Excel automation on fox.wikis

Yet another one

Upvotes: 1

Keht
Keht

Reputation: 57

You can use an excel sheet as a template. Then populate specific cells from FoxPro into this template and functions and formatting in the template will work with your data.

lcExcelFile = "output.xls" && your ouput

oExcelObject = CREATEOBJECT('Excel.Application')
oExcelWorkbook = ;
    oExcelObject.APPLICATION.Workbooks.OPEN(sDir + "\" + "template.xls") && your template
oActiveExcelSheet = oExcelWorkbook.Worksheets("sheet").ACTIVATE
oExcelSheet = oExcelWorkbook.Worksheets("sheet")

WAIT WINDOW "Developing Microsoft Excel File..." + CHR(13) + "" + CHR(13) + ;
    "Passing formatting information to Excel." + CHR(13) + "" NOWAIT

SELECT cTmp && your data table or cursor
GOTO TOP
oExcelSheet.Cells(1,1).VALUE = sTitle
nRow = 3 && moving past title 
sNote = ""

DO WHILE NOT EOF()

    nRow = nRow + 1
    && Insertint Records
    oExcelSheet.Cells(nRow,1).VALUE = STR(cTmp.INlocx)
    oExcelSheet.Cells(nRow,2).VALUE = ALLTRIM(cTmp.INacct) + '-' + ALLTRIM(cTmp.INmr_)
    oExcelSheet.Cells(nRow,3).VALUE = ALLTRIM(cTmp.INpnam)
    oExcelSheet.Cells(nRow,4).VALUE = ALLTRIM(cTmp.INCARR)
    oExcelSheet.Cells(nRow,5).VALUE = ALLTRIM(cTmp.INADR1) + ALLTRIM(cTmp.INADR2)
    oExcelSheet.Cells(nRow,6).VALUE = ALLTRIM(cTmp.INCITY)
    oExcelSheet.Cells(nRow,7).VALUE = ALLTRIM(cTmp.INSTAT)
    oExcelSheet.Cells(nRow,8).VALUE = STR(cTmp.INZIP)
    oExcelSheet.Cells(nRow,9).VALUE = STR(cTmp.INAREA)
    oExcelSheet.Cells(nRow,10).VALUE = STR(cTmp.INPHON)
    oExcelSheet.Cells(nRow,11).VALUE = ALLTRIM(cTmp.INPOL_)
    oExcelSheet.Cells(nRow,12).VALUE = ALLTRIM(cTmp.INGRP_)
    oExcelSheet.Cells(nRow,13).VALUE = ALLTRIM(cTmp.INPOLH)
    oExcelSheet.Cells(nRow,14).VALUE = STR(cTmp.INEFDT)
    oExcelSheet.Cells(nRow,15).VALUE = STR(cTmp.INETDT)
    oExcelSheet.Cells(nRow,16).VALUE = ALLTRIM(cTmp.INRELATE)



    SELECT cTmp
    SKIP
ENDDO

&&saving template AS output
oExcelWorkbook.SAVEAS(lcExcelFile)
oExcelObject.QUIT
RELEASE oExcelObject

Upvotes: 0

Dhugalmac
Dhugalmac

Reputation: 574

I know you can calculate sum(column) but I don't know how to include this in the export

The short answer is you cannot.

You can use VFP's command: COPY TO excel file XL5 to output the DATA into the designated Excel file, but calculated values will not be included.

BUT, after having written the data, you can then use VFP Automation of Excel to either write the calculated value into a specific cell or you can have Excel run the calculation for you and put the result into the specified cell.

If you haven't done VFP Automation of Excel you might want to Google for that since it involves a number of commands which need to vary based on your specific needs.

Good Luck

Upvotes: 0

Related Questions