Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Insert into Excel a sum for three columns

Posted on 2014-04-02
8
Medium Priority
?
795 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 43

Expert Comment

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

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 43

Assisted Solution

by:pcelba
pcelba earned 880 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 30

Accepted Solution

by:
Olaf Doschke earned 1120 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
 
LVL 43

Assisted Solution

by:pcelba
pcelba earned 880 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 30

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 1120 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 43

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 30

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

877 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