Solved

Insert into Excel a sum for three columns

Posted on 2014-04-01
1
343 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 24

Accepted Solution

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

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

772 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