Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Insert Sql data into Excel and Calculate a single Column

Posted on 2014-03-28
1
Medium Priority
?
546 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
1 Comment
 
LVL 30

Accepted Solution

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

577 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