Solved

Insert Sql data into Excel and Calculate a single Column

Posted on 2014-03-28
1
507 Views
Last Modified: 2014-03-31
After I run the sql program code, I want to insert a total at the bottom of extralinens single column.  The program code is below.  What code can I use to calculate the total for the column?  I have attached the Excel spreadsheet.

CLEAR
CLEAR ALL
SET DEFAULT TO \LDY
SET BELL OFF
SET CENTURY ON
SET TALK ON
SET STAT OFF
SET EXCLUSIVE OFF

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;
 
 
USE LAUNDRY2 && 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 Laundry2.building, Laundry2.date, CMONTH(Laundry2.date) AS MONTH, Laundry2.blktdescript,;
  SUM(Laundry2.blktamtdiff) AS EXTRALINENS;
 FROM laundry!laundry2;
 WHERE BETWEEN(DATE,M_BEGDATE,M_ENDDATE);
 .and. Laundry2.blktamtdiff <=0;
 GROUP BY BUILDING, MONTH;
 ORDER BY Laundry2.building, Laundry2.date DESC INTO CURSOR OVRblkt
SELECT building, (blktdescript) AS BLANKETS, EXTRALINENS;
  FROM OVRblkt;  
  ORDER BY building INTO CURSOR queryovrblkt
LOCAL lReturnValue
lReturnValue = .T.
LOCAL lError
lError = .F.
ON ERROR lError = .T.
lcFileName = sys(5)+curdir()+'myExcelblkt2.xls'
copy to (lcFileName) type xl5
oExcel = createobject('Excel.Application')
oExcel.Workbooks.Open(lcFileName)
oExcel.Visible = .t.
WITH oExcel
 .Rows(1).Insert
   WITH .Range("A1")
            .value =  "Date:"+ttoc(datetime())+" Year "+trans(year(date()))
             WITH .font
                  .bold = .T.
                  .ITALIC=.T.
                  .color=0
                  .size = 14
             ENDWITH    
   ENDWITH
   #define xlDouble -4119
   #define xlEdgeBottom 9
   #define xlEdgeTop 8
   #define xlEdgeleft 7
   #define xlEdgeRight 10
   #define xlInsideHorizontal  12
   #define xlInsideVertical 11

   with .Range("C3:C31")
     .Borders.LineStyle = xlDouble
     .Borders(xlEdgeBottom).Color = RGB(0, 0,255)    
     .Borders(xlEdgetop).Color = RGB(0, 0,255)    
     .Borders(xlEdgeleft).Color = RGB(0, 0,255)    
     .Borders(xlEdgeright).Color = RGB(0, 0,255)  
     .Borders(xlInsideHorizontal).Color = RGB(0, 0,255)    
     .Borders(xlInsideVertical).Color = RGB(0, 0,255)
     .Interior.ColorIndex = 6    &&yellow
   endwith  
 
 
Endwith
oExcel.columns(1).EntireColumn.AutoFit
oExcel.columns(2).EntireColumn.AutoFit
oExcel.columns(3).EntireColumn.AutoFit
* We reset the error handler,
* and check if everything went fine
ON ERROR
IF lError
  RETURN .F.
ELSE
  RETURN .T.
ENDIF
excelblkt.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
1 Comment
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 500 total points
ID: 39963350
Here's what I did:
1. I started macto recording in Excel.
2. I activated the cell under the extralinens
3. I then added an Autosum.
4. Then I stopped makro recording
and
5. I edited the makro.

This is what I found:
ActiveCell.FormulaR1C1 = "=SUM(R[-29]C:R[-1]C)"

Now we know the property of the cell to set is FormulaR1C1. Also the Formula can be written in many ways, but this way is fine, it's saying relative to the cell sum values from 1 cell above (R[-1]) to 29 cells above (R[-29]). 29 is your Reccount.

So do
LOCAL lnTotalCellRow, lnTotalCellCol, lnRecords
lnRecords = Reccount("queryovrblkt")
lnTotalCellCol = 3 && "C"
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

Bye, Olaf.
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

738 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