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 _pcdescrip t,m_pccper item,m_btd escript,m_ btcost,m_b tcperitem, m_bt;
m_bst,m_bstdescript,m_bstc ost,m_bstc peritem,m_ wc,m_wccos t,m_wccper item,m_wcd escript,m_ flatsheet;
m_flshtcost,m_flshtcperite m,m_flshtd escript,m_ ftsht,m_ft shtcperite m,m_ftshtc ost,m_ftsh tdescript;
m_pc,m_pccost,m_pcdescript ,m_pccperi tem,m_nurs prd,m_nurs prdcost,m_ nursprdcpe ritem,m_nu rsprddescr ipt;
m_redsprd,m_redsprdcost,m_ redsprddes cript,m_re dsprdcperi tem,m_bott ,m_bottcos t,m_bottde script;
m_bottcperitem,m_blkt,m_bl ktcost,m_b lktcperite m,m_blktde script,m_b egdate,m_e nddate,m_d ate,date;
begdate,enddate,m_exp_2,ex p_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_E NDDATE);
.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()+'myExcelbl kt2.xls'
copy to (lcFileName) type xl5
oExcel = createobject('Excel.Applic ation')
oExcel.Workbooks.Open(lcFi leName)
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).Col or = RGB(0, 0,255)
.Borders(xlEdgetop).Color = RGB(0, 0,255)
.Borders(xlEdgeleft).Color = RGB(0, 0,255)
.Borders(xlEdgeright).Colo r = RGB(0, 0,255)
.Borders(xlInsideHorizonta l).Color = RGB(0, 0,255)
.Borders(xlInsideVertical) .Color = RGB(0, 0,255)
.Interior.ColorIndex = 6 &&yellow
endwith
Endwith
oExcel.columns(1).EntireCo lumn.AutoF it
oExcel.columns(2).EntireCo lumn.AutoF it
oExcel.columns(3).EntireCo lumn.AutoF it
* We reset the error handler,
* and check if everything went fine
ON ERROR
IF lError
RETURN .F.
ELSE
RETURN .T.
ENDIF
excelblkt.xls
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
m_bst,m_bstdescript,m_bstc
m_flshtcost,m_flshtcperite
m_pc,m_pccost,m_pcdescript
m_redsprd,m_redsprdcost,m_
m_bottcperitem,m_blkt,m_bl
begdate,enddate,m_exp_2,ex
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_E
.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()+'myExcelbl
copy to (lcFileName) type xl5
oExcel = createobject('Excel.Applic
oExcel.Workbooks.Open(lcFi
oExcel.Visible = .t.
WITH oExcel
.Rows(1).Insert
WITH .Range("A1")
.value = "Date:"+ttoc(datetime())+"
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).Col
.Borders(xlEdgetop).Color = RGB(0, 0,255)
.Borders(xlEdgeleft).Color
.Borders(xlEdgeright).Colo
.Borders(xlInsideHorizonta
.Borders(xlInsideVertical)
.Interior.ColorIndex = 6 &&yellow
endwith
Endwith
oExcel.columns(1).EntireCo
oExcel.columns(2).EntireCo
oExcel.columns(3).EntireCo
* 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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.