Solved

Insert into Excel a sum for three columns

Posted on 2014-04-02
8
623 Views
Last Modified: 2014-04-03
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 lError
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.
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
  • 5
  • 3
8 Comments
 
LVL 41

Expert Comment

by:pcelba
ID: 39972478
Do you need the total calculated by FoxPro or by Excel?
0
 
LVL 41

Expert Comment

by:pcelba
ID: 39972512
Here is the solution calculating the total in FoxPro:

Instead of the SELECT command:
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

Use following three commands:
 
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, building ;
  ORDER BY building, date ;
  INTO CURSOR query1

SELECT MAX(date) date, MAX(building) building, ;
    SUM(LinensOnBldg) LinensOnBldg, ;
    SUM(LinensInLaundry) LinensInLaundry, ;
    SUM(LinensRequiredOnBldg) LinensRequiredOnBldg ;
FROM query1 ;
INTO query2

SELECT * FROM query1 ;
UNION ALL  ;
SELECT * FROM query2 ;
INTO CURSOR query

USE IN query1
USE IN query2

Open in new window

0
 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 220 total points
ID: 39972547
And the solution for Excel side:

Simply add the formula to the Excel sheet via automation...
oExcel.Cells(RECCOUNT('query')+2,3).Formula = '=SUM(C2:C' + ALLTRIM(STR(RECCOUNT('query')+1)) + ')'
oExcel.Cells(RECCOUNT('query')+2,4).Formula = '=SUM(D2:D' + ALLTRIM(STR(RECCOUNT('query')+1)) + ')'
oExcel.Cells(RECCOUNT('query')+2,5).Formula = '=SUM(E2:E' + ALLTRIM(STR(RECCOUNT('query')+1)) + ')'

Open in new window

0
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 280 total points
ID: 39974164
Why don't you take the solution you already got for column C and change it for D and E?
Besides, there is no column C3,C4,C5, those are cells, there are columns C,D,E or 3,4,5.

LOCAL lnTotalCellRow, lnTotalCellCol, lnRecords
lnRecords = Reccount("queryovrblkt")
lnTotalCellCol = 3 && 3 stands for "C". You only have to change this line to 4 or 5
lnTotalCellRow = lnRecords +3
* adding 3:  
*+1 for captions row, 
*+1 for the additional row inserted for the Date,
*+1 to use the row [u]beneath[/u] all records
oExcel.ActiveWorkBook.ActiveSheet.Cells(lnTotalCellRow,lnTotalCellCol).FormulaR1C1=;
"=SUM(R[-"+TRANSFORM(lnRecords)+"]C:R[-1]C)"

Open in new window


Instead of c opying the code 3 times, make it a loop:

LOCAL lnTotalCellRow, lnTotalCellCol, lnRecords
lnRecords = Reccount("queryovrblkt")
lnTotalCellRow = lnRecords +3
* adding 3:  
*+1 for captions row, 
*+1 for the additional row inserted for the Date,
*+1 to use the row [u]beneath[/u] all records
For lnTotalCellCol = 3 To 5 && Columns 3,4,5 = C,D,E
   oExcel.ActiveWorkBook.ActiveSheet.Cells(lnTotalCellRow,lnTotalCellCol).FormulaR1C1=;
      "=SUM(R[-"+TRANSFORM(lnRecords)+"]C:R[-1]C)"
Endfor

Open in new window


Bye, Olaf.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 220 total points
ID: 39974459
So instead of three simple and easily understandable lines of code you have 11 lines which are not so easy to understand (to me at least)...  What you mean by one additional row for date? Isn't it column instead?
0
 
LVL 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 280 total points
ID: 39974936
Pavel, did you read the previous thread at all?

My code was already accepted solution in there. It's verbose, but to explain a bit further:

In the previous thread - looking pretty much the same as this one - he did add an additional line to the Excel file at top showing the current date. This time not? Well,  that can be adjusted, can't it? My code explains very well why it puts the total cell three rows lower than reccount, if not all reasons apply, the code can be changed to +2 instead.

I also explained in the last thread, how I came to use FormulaR1C1 instead of Formula, by using macro recording in Excel and adapting the code recorded.

Sorry, for being so complicated Pavel, I'd say I'm more verbose. Variables are defined, they are named speaking for what they are for and where they point to...

Bye, Olaf.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 39975080
Hmm... Neither you nor campbme mentioned some "previous thread" before and I also don't remember such question.

My solution was just a reaction to this question and nothing else. Some additional row for date is not used here and it is not relevant because your solution works well.

BUT we are on internet so some link to the "previous thread" would really be helpful... :-)
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 39976333
OK, now both threads are right beside each other in the Answered Questions.
http://www.experts-exchange.com/Database/Software/FoxPro/Q_28399871.html

I often enough don't see threads you answer before I even read them, this time it was the other way around.

I admit I haven't looked exactly at what is done after oExcel.Workbooks.Open(lcFileName), I assumed the same code again, but anyway, problem solved.

Bye, Olaf.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

867 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now