Solved

Insert into Excel a sum for three columns

Posted on 2014-04-02
8
734 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 42

Expert Comment

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

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 42

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
 Database Backup and Recovery Best Practices

Join Percona’s, Architect, Manjot Singh as he presents Database Backup and Recovery Best Practices (with a Focus on MySQL) on Thursday, July 27, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7). In the case of a failure, do you know how long it will take to restore your database?

 
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
 
LVL 42

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 42

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

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

627 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