Solved

Insert into Excel a sum for three columns

Posted on 2014-04-01
1
355 Views
Last Modified: 2014-04-02
I have three columns(C3,C4,C5) that I need a bottom total at the end of each single column.  How can I get a bottom total for each single column(LinensOnBldg, LinensInLaundry, LinensRequiredOnBldg)?  I have attached the example report.



SET DEFAULT TO \LDY
SET BELL OFF
SET CENTURY ON
SET TALK ON
SET STAT OFF
SET EXCLUSIVE OFF
OPEN DATABASE LAUNDRY SHARED
PUBLIC m_building,m_pc,m_pccost,m_pcdescript,m_pccperitem,m_btdescript,m_btcost,m_btcperitem,m_bt;
  m_bst,m_bstdescript,m_bstcost,m_bstcperitem,m_wc,m_wccost,m_wccperitem,m_wcdescript,m_flatsheet;
  m_flshtcost,m_flshtcperitem,m_flshtdescript,m_ftsht,m_ftshtcperitem,m_ftshtcost,m_ftshtdescript;
  m_pc,m_pccost,m_pcdescript,m_pccperitem,m_nursprd,m_nursprdcost,m_nursprdcperitem,m_nursprddescript;
  m_redsprd,m_redsprdcost,m_redsprddescript,m_redsprdcperitem,m_bott,m_bottcost,m_bottdescript;
  m_bottcperitem,m_blkt,m_blktcost,m_blktcperitem,m_blktdescript,m_begdate,m_enddate,m_date,date;
  begdate,enddate,m_exp_2,exp_2;
   T2,T3,T4,T5,T6,T7,T8,T9,S1,S2,S3,S4,S5,S6,S7,S8,S9,B1,B2,B3,B4,B5,V1,V2,V3,V4,V5,V6,V7,V8,V9;
  M1,M2,M3,M4,M5,M6,M7,M8,M9,D1,D2,D3,D4,D5,D6,D7,D8,D9,Y1,Y2,Y3,Y4,Y5,Y6,Y7,Y8,Y9,P1,P2,P3,P4;
  P5,P6,P7,P8,P9,R1,R2,R3,R4,R5,R6,R7,R8,R9,K1,K2,K3,K4,K5,K6,K7,K8,K9,C1,C2,C3,C4,C5,C6,C7,C8;
  C9,H1,H2,H3,H4,H5,H6,H7,H8,H9J1,J2,J3,J4,J5,J6,J7,J8,J9,L1,L2,L3,L4,L5,L6,L7,L8,L9,Q1,Q2,Q3;
  Q4,Q5,Q6,Q7,Q8,Q9,S1,S2,S3,S4,S5,S6,S7,S8,S9W1,W2,W3,W4,W5,W6,W7,W8,W9
 
USE BLDG87 && OPEN TABLE SHARED
STORE { /  /   } TO M_BEGDATE,M_ENDDATE,M_DATE
STORE DATE TO M_BEGDATE,M_ENDDATE
CLEAR
STORE SPACE(1) TO SUBSELECT
STORE {  /  /   } TO M_BEGDATE,M_ENDDATE,M_DATE
@1,17 SAY 'ENTER THE START DATE' GET M_BEGDATE
@3,17 SAY 'ENTER THE END DATE' GET M_ENDDATE
READ
CLEAR GETS
SELECT BLDG87.building, BLDG87.USERID, BLDG87.date, BLDG87.RECDATE, BLDG87.RDAYNAME, BLDG87.DAYNAME, CMONTH(BLDG87.DATE) AS MONTH,;
  SUM(BLDG87.btamtin) AS BTAMT, SUM(BLDG87.BTQTY) AS BT, SUM(BLDG87.wcamtin) AS WCAMT,;
  SUM(BLDG87.wcQTY) AS WC, SUM(BLDG87.bstamtin) AS BSTAMT, SUM(BLDG87.bstqty) AS BST, SUM(BLDG87.SHTPamtin) AS SHTPAMT, SUM(BLDG87.shtpQTY) AS SHTP,;
  SUM(BLDG87.SHTBTamtin) AS SHTBTAMT, SUM(BLDG87.shtbtQTY) AS SHTBT, SUM(BLDG87.pcamtin) AS PCAMT, SUM(BLDG87.pcQTY) AS PC, SUM(BLDG87.blktamtin) AS BLKTAMT,;
  SUM(BLDG87.blktQTY) AS BLKT, SUM(BLDG87.sprdamtin) AS SPRDAMT, SUM(BLDG87.sprdQTY) AS SPRD,;
  SUM(BLDG87.OTH7amtin) AS OTH7AMT, SUM(BLDG87.oth7qty) AS OTH7, SUM(BLDG87.WCREQ) AS WCR, SUM(BLDG87.BTREQ) AS BTR, SUM(BLDG87.BSTREQ) AS BSTR,;
  SUM(BLDG87.BLKTREQ) AS BLKTR, SUM(BLDG87.SPRDREQ) AS SPRDR, SUM(BLDG87.SHTBTREQ) AS SHTBTR, SUM(BLDG87.SHTPREQ) AS SHTPR, SUM(BLDG87.OTH7REQ) AS OTH7R,;
  SUM(BLDG87.PCREQ) AS PCR, BLDG87.CENWC, BLDG87.CENBT, BLDG87.CENBST, BLDG87.CENBLKT, BLDG87.CENPC, BLDG87.CENGWN, BLDG87.CENSPRD,;
  BLDG87.CENSHTBT, BLDG87.CENSHTP;
 FROM laundry!BLDG87;
 WHERE BETWEEN(DATE,M_BEGDATE,M_ENDDATE) ;
 GROUP BY BLDG87.DATE;
 ORDER BY BLDG87.DATE INTO CURSOR EXBLDG87
SELECT Date, building, SUM(BT+BST+BLKT+WC+SPRD+SHTBT+SHTP+PC+OTH7) AS LinensOnBldg,;
  SUM(BTAMT+BSTAMT+BLKTAMT+WCAMT+SPRDAMT+SHTBTAMT+SHTPAMT+PCAMT+OTH7AMT) AS LinensInLaundry,;
  SUM(BTR+BSTR+BLKTR+WCR+SPRDR+SHTBTR+SHTPR+PCR+OTH7R) AS LinensRequiredOnBldg;
  FROM EXBLDG87;
  GROUP BY date;
  ORDER BY building, date;
  INTO CURSOR query
LOCAL lnTotalCellRow, lnTotalCellCol, lnRecords, lReturnValue, lError
lReturnValue = .T.
lError = .F.
ON ERROR lError = .T.
lcFileName = sys(5)+curdir()+'myExcel.xls'
copy to (lcFileName) type xl5
oExcel = createobject('Excel.Application')
oExcel.Workbooks.Open(lcFileName)
oExcel.Visible = .T.
lnRecords = Reccount("QUERY")
lnTotalCellCol = 5 && "C"
lnTotalCellRow = lnRecords +2
* adding 6:
*+1 for captions row,
*+1 for the additional row inserted for the Date,
*+1 to use the row beneath all records
oExcel.ActiveWorkBook.ActiveSheet.Cells(lnTotalCellRow,lnTotalCellCol).FormulaR1C1=;
"=SUM(R[-"+TRANSFORM(lnRecords)+"]C:R[-1]C)"
lReturnValue = .T.
oExcel.Visible = .t.
oExcel.columns(1).EntireColumn.AutoFit
oExcel.columns(2).EntireColumn.AutoFit
oExcel.columns(3).EntireColumn.AutoFit
oExcel.columns(4).EntireColumn.AutoFit
oExcel.columns(5).EntireColumn.AutoFit
* We reset the error handler,
* and check if everything went fine
ON ERROR
IF lError
  RETURN .F.
ELSE
  RETURN .T.
ENDIF
excel3col.xls
0
Comment
Question by:campbme
1 Comment
 
LVL 25

Accepted Solution

by:
chaau earned 500 total points
ID: 39970880
Looks like FoxPro to me
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Display field if column exists 7 32
How come this XML node is not read? 3 27
how to make geography query faster?  SQL 7 43
Delete row if does not start with 0 43 37
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question