Insert Sql data into Excel and Calculate a single Column

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
campbmeAsked:
Who is Participating?
 
Olaf DoschkeConnect With a Mentor Software DeveloperCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.