×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

sum of fields of data exported from dbf to excel in VFP

sum of fields of data exported from dbf to excel in VFP

sum of fields of data exported from dbf to excel in VFP

(OP)
have written following codes
it shows error in line FOR EACH FIELD IN curxl
also require totals to be written down in the field with headings
please can someone explain me
thanks
regards

CLOSE ALL

LOCAL oform as Form
DO Form getdaterange NAME oform noshow
oform.AutoCenter = .t.
oform.Show(1) && 1 = modal style to hold the form visible
IF oform.cancelled
** exit. do not run query
RETURN
ENDIF

** get the date values selected from the hidden form
m.dt1 = oform.from_date
m.dt2 = oform.to_date

oform.release()

SET ENGINEBEHAVIOR 70

SELECT ;
'Invoice ' as doc_type, ;
crs.date as doc_date, ;
crs.bill_no as doc_num, ;
'R' as trans_type, ;
ms.no_of_copy as place_of_supply, ;
'' as rv_chg, ;
'' as igst_state, ;
'27221F1ZF' as supp_gstin, ;
'P A' as legal_name, ;
'PA' as trade_name, ;
'J road' as supp_addr1, ;
'ridi' as supp_addr2, ;
'kolkate' as supp_city, ;
'xxxxxx' as supp_pin, ;
ms.gstin as rcp_gstin, ;
ms.bill_name as rcp_lgl_nme, ;
ms.bill_name as rcp_trade_nme, ;
ms.add1 as rcp_addr1, ;
ms.add2 as rcp_addr2, ;
'TBD' as rcp_city, ;
'TBD' as rcp_pin, ;
'TBD...' as shpto, ;
itm.hsn_code, ;
itm.itemname as prod_desc, ;
SUM(slp.qnty) AS qnty, ;
'OTH' AS item_uqc, ;
slp.rate as unit_price, ;
SUM(slp.gross_amt) as item_value, ;
SUM(slp.sgst_perc + slp.cgst_perc) as Item_gst, ;
SUM(slp.igst_perc) as IGST_AMT, ;
SUM(slp.cgst_perc) as CGST_AMT, ;
SUM(slp.sgst_perc) as SGST_AMT ;
FROM MASTER ms ;
JOIN crsale as crs ON crs.acd = ms.acd ;
JOIN slips as slp ON slp.bill_no = crs.bill_no ;
JOIN itemmst as itm ON itm.itemcode = slp.itemcode ;
WHERE crs.date between m.dt1 AND m.dt2 ;
GROUP BY crs.date, crs.bill_no, itm.itemname,slp.rate ;
INTO CURSOR curxl



SET ENGINEBEHAVIOR 90

LOCAL lnRow, lnCol, loExcel, loSheet
LOCAL lcFileName, lcSheetName, lcFieldName, lcFunction, lcFormat

lcFileName = "C:\1\MyData.xlsx"
lcSheetName = "MySheet"
loExcel = CREATEOBJECT("Excel.Application")
loExcel.Visible = .T.

* Add a new workbook and select the first sheet
loExcel.Workbooks.Add()
loSheet = loExcel.ActiveWorkbook.Worksheets(1)
loSheet.Name = "MySheet"
LOCAL lcTemplate
lcTemplate = "C:\1\123.xlsx"

IF EMPTY(ALIAS())
loexcel.Workbooks.Close()
loexecl.Quit()
MESSAGEBOX("No table or cursor found to export!", 48)
RETURN .f.
ENDIF

LOCAL ncols, j, cell_value
ncols = FCOUNT()
SCAN
FOR j = 1 TO ncols
** get data so we can check the type
cell_value = EVALUATE(FIELD(j))
DO CASE
CASE VARTYPE(m.cell_value) = 'N' OR VARTYPE(m.cell_value) = 'D'
loexcel.Cells(RECNO() + 1, j).Value = m.cell_value
OTHERWISE
loexcel.Cells(RECNO() + 1, j).Value = TRANSFORM(m.cell_value)
ENDCASE
NEXT j

ENDSCAN

lcFunction = "SUM"
lcFormat = "#,##0.00"
FOR EACH FIELD IN curxl
lcFieldName = FIELD.NAME
loSheet.Cells(lnRow, lnCol).Value = lcFunction + "(" + lcFieldName + ")"
loSheet.Cells(lnRow + 1, lnCol).Formula = "=SUM(" + lcFieldName + ")"
loSheet.Cells(lnRow + 1, lnCol).NumberFormat = lcFormat
lnCol = lnCol + 1
ENDFOR

loExcel.ActiveWorkbook.SaveAs(lcFileName)
loExcel.ActiveWorkbook.Close()
loExcel.Quit()



RE: sum of fields of data exported from dbf to excel in VFP

FOR EACH FIELD IN <cursor name> is not valid VFP syntax. FOR EACH is used to loop through arrays or collections, not tables or cursors. You probably want to use a SCAN instead (although it's hard to know for sure because I'm having diffculty in understanding your code).

I suggest you start by studying the relevant items in the VFP Help.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: sum of fields of data exported from dbf to excel in VFP

This has many misunderstandings.

A loop on all fields can't be done by FOR EACH. But there is a loop as you need it just a few lines above using ncols=FCOUNT() - the field count - and using FIELD(j) for getting the field name of field number j, i.e. also FIED.NAME is wrong, it would either address a property Name of an object Field or the field called name of a workarea called Field, but there is no such object or workarea.

What you want to do is

CODE -->

FOR j = 1 TO ncols
lcFieldname= FIELD(j)
...
ENDFOR 

The rest of your code also won't work, though. To add a SUM formula in Excel, you'll need cell names, not a column name. And when you set a formula, that will be evaluated and why do you set the value, when you set the formula, the formula will compute the value, that's not your task. If you set formula and then value, the value will override the formula result, that's just not what you want. You will also only be able to sum cells that are numeric, so this won't necessarily work for all columns, anyway.

Think harder about what you want in the "..." section of the loop.

Chriss

RE: sum of fields of data exported from dbf to excel in VFP

Hi,

Your SQL SELECT will also throw an error. In VFP9 you have to group by all columns that are NEITHER constants (e.g. 'Invoice ' as doc_type) NOR aggregate functions (SUM(...) as)

hth

MarK

EDIT: I just noticed that you SET ENGINEBEHAVIOR TO 70 to circumvent the error throwing of your SELECT ... Although this is possible it is rather confusing since your mixing different behaviors of the VFP SQL engine.

RE: sum of fields of data exported from dbf to excel in VFP

Most of the code is replaceable by the COPY TO or EXPORT command, by the way, you get all data written out to an xls file. That's an old Excel file type, but supporting all relevant VFP data types except general fields. Clearly, it supports all numeric types which are the only ones relevant for sums. And they also export a first row with field names.

Then the only aftermath to do is load the file and add the sum formulas.

Chriss

RE: sum of fields of data exported from dbf to excel in VFP

(OP)
thank you Chris, mjcmksr, Mike for your advice just if you could tell me like i get the dbf with these headings as shown and i want to export to dbf with sum of few fields ( as rightly pointed out by mjcmksr) what code should i write please if youll can guide me only the export and sum part
regards

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close