Solved

Insert Sql data into Excel and Calculate a single Column

Posted on 2014-03-28
1
485 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 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel for Mac - How make those Tabs larger? 2 31
splitting text of cell to columns 14 24
ADD New Entries 7 15
Incorporate VBA Code to work with Original Workbook 23 33
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now