Link to home
Start Free TrialLog in
Avatar of campbme
campbme

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Olaf Doschke
Olaf Doschke
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial