2 Replies - 6207 Views - Last Post: 17 September 2010 - 11:46 PM Rate Topic: -----

#1 Guest_S. JEYARAMAN*


Reputation:

VB6-DATAGRID TO EXCEL

Posted 17 September 2010 - 03:20 AM

DEAR SIR,


Private Sub Command1_Click()
Dim sql As String
sql = "SELECT JOBNO,CUSTOMER,COLOUR,DYE1,DYE1QTY,DATE FROM TRIAL3 WHERE pdn like '" & txtcus2.Text & "' AND date between '" & txtsdate2.Text & "' AND '" & txtedate2.Text & "'" & _
"UNION SELECT JOBNO,CUSTOMER,COLOUR,DYE2,DYE2QTY,DATE FROM TRIAL3 WHERE pdn like '" & txtcus2.Text & "' AND date between '" & txtsdate2.Text & "' AND '" & txtedate2.Text & "'" & _
"UNION SELECT JOBNO,CUSTOMER,COLOUR,DYE3,DYE3QTY,DATE FROM TRIAL3 WHERE pdn like '" & txtcus2.Text & "' AND date between '" & txtsdate2.Text & "' AND '" & txtedate2.Text & "'"

Adodc1.RecordSource = sql
Debug.Print sql
Adodc1.RecordSource = sql
Adodc1.Refresh
Adodc1.Caption = Adodc1.RecordSource
End Sub

THE ABOVE IS THE CODE FOR DATAGRID

BUT I NEED TO CONSOLIDATE COLUMNS INTO EXCEL TO MAKE REPORT.


DATAGRID MAKING COLUMNS OF REPEATED ITEMS BUT I WANT CONSOLIDATE
BLUE - 100
YELLOW -200
RED -100
YELLOW - 200
BLUE - 200
RED -300

I WANT IN EXCEL FROM DATAGRID AS FOLLOWS
BLUE - 300
YELLOW-400
RED - 400

CAN ANYONE HELP ME TO TAKE A CONSOLIDATED REPORT IN EXCEL FORM FROM DATAGRID




THANKS IN ADVANCE

WITH BEST REGARDS

S. JEYARAMAN

Is This A Good Question/Topic? 0

Replies To: VB6-DATAGRID TO EXCEL

#2 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 386
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: VB6-DATAGRID TO EXCEL

Posted 17 September 2010 - 03:28 AM

Do this from your SQL statement:
"select Jobno, CUSTOMER, COLOUR, DYE1, SUM(DYE1QTY), DATE FROM
(
SELECT JOBNO,CUSTOMER,COLOUR,DYE1,DYE1QTY,DATE FROM TRIAL3 WHERE pdn like '" & txtcus2.Text & "' AND date between '" & txtsdate2.Text & "' AND '" & txtedate2.Text & "'" & _
"UNION SELECT JOBNO,CUSTOMER,COLOUR,DYE2,DYE2QTY,DATE FROM TRIAL3 WHERE pdn like '" & txtcus2.Text & "' AND date between '" & txtsdate2.Text & "' AND '" & txtedate2.Text & "'" & _
"UNION SELECT JOBNO,CUSTOMER,COLOUR,DYE3,DYE3QTY,DATE FROM TRIAL3 WHERE pdn like '" & txtcus2.Text & "' AND date between '" & txtsdate2.Text & "' AND '" & txtedate2.Text & "' 
) X
GROUP BY COLOUR, DYE1, DATE, JOBNO, CUSTOMER"



Before running it from vb, run this statement from SQL and see if you get any errors and you have the desired output.

Ionut
Was This Post Helpful? 0
  • +
  • -

#3 Guest_S. JEYARAMAN*


Reputation:

Re: VB6-DATAGRID TO EXCEL

Posted 17 September 2010 - 11:46 PM

View PostS. JEYARAMAN, on 17 September 2010 - 02:20 AM, said:

DEAR SIR,


Private Sub Command1_Click()
Dim sql As String
sql = "SELECT JOBNO,CUSTOMER,COLOUR,DYE1,DYE1QTY,DATE FROM TRIAL3 WHERE pdn like '" & txtcus2.Text & "' AND date between '" & txtsdate2.Text & "' AND '" & txtedate2.Text & "'" & _
"UNION SELECT JOBNO,CUSTOMER,COLOUR,DYE2,DYE2QTY,DATE FROM TRIAL3 WHERE pdn like '" & txtcus2.Text & "' AND date between '" & txtsdate2.Text & "' AND '" & txtedate2.Text & "'" & _
"UNION SELECT JOBNO,CUSTOMER,COLOUR,DYE3,DYE3QTY,DATE FROM TRIAL3 WHERE pdn like '" & txtcus2.Text & "' AND date between '" & txtsdate2.Text & "' AND '" & txtedate2.Text & "'"

Adodc1.RecordSource = sql
Debug.Print sql
Adodc1.RecordSource = sql
Adodc1.Refresh
Adodc1.Caption = Adodc1.RecordSource
End Sub

THE ABOVE IS THE CODE FOR DATAGRID

BUT I NEED TO CONSOLIDATE COLUMNS INTO EXCEL TO MAKE REPORT.


DATAGRID MAKING COLUMNS OF REPEATED ITEMS BUT I WANT CONSOLIDATE
BLUE - 100
YELLOW -200
RED -100
YELLOW - 200
BLUE - 200
RED -300

I WANT IN EXCEL FROM DATAGRID AS FOLLOWS
BLUE - 300
YELLOW-400
RED - 400

CAN ANYONE HELP ME TO TAKE A CONSOLIDATED REPORT IN EXCEL FORM FROM DATAGRID




THANKS IN ADVANCE

WITH BEST REGARDS

S. JEYARAMAN

DEAR SIR,

THANK YOU FOR YOUR KIND REPLY

BUT IT IS SHOWING ERROR IN VB6 AND SQL2000 AS WELL

AGAIN I WILL SHWO YOU WHAT I WANTED EXACTLY IN DETAIL.

Private Sub Command1_Click()
Dim sql As String
sql = "SELECT JOBNO,CUSTOMER,COLOUR,DYE1,DYE1QTY,DATE FROM TRIAL3 WHERE pdn like '" & txtcus2.Text & "' AND date between '" & txtsdate2.Text & "' AND '" & txtedate2.Text & "'" & _
"UNION SELECT JOBNO,CUSTOMER,COLOUR,DYE2,DYE2QTY,DATE FROM TRIAL3 WHERE pdn like '" & txtcus2.Text & "' AND date between '" & txtsdate2.Text & "' AND '" & txtedate2.Text & "'" & _
"UNION SELECT JOBNO,CUSTOMER,COLOUR,DYE3,DYE3QTY,DATE FROM TRIAL3 WHERE pdn like '" & txtcus2.Text & "' AND date between '" & txtsdate2.Text & "' AND '" & txtedate2.Text & "'"

Adodc1.RecordSource = sql
Debug.Print sql
Adodc1.RecordSource = sql
Adodc1.Refresh
Adodc1.Caption = Adodc1.RecordSource
End Sub

DATAGRID OUTPUT IS AS SHOWN BELOW

JOBNO CUSTOMER COLOUR DYE1 DYE QTY DATE

1 CEC RED BLUE FNR 5.00 9/9/2010
2 REC BLUE YELLLOW FN2R 3.00 9/10/2010
3 GEC OLIVE RED FN2BL 1.00 9/11/2010
4 HEC ORANGE YELLOW FN2R 3.00 9/12/2010
5 IEC LT BLUE BLUE FNR 1.00 9/13/2010
6 ECS RED RED FN2BL 2.00 9/14/2010

MY ABOVE DATAGRID CODE WORKS WELL FOR THE ABOVE OUTPUT, NO PROBLEM

BUT FROM DATAGRID OUTPUT I NEED THE FOLLOWING REPORT (AS CONSOLIDATED DYE CONSUMPTON REPORT) IN EXCEL BETWEEN GIVEN TWO DATES

FOLLOWING IS THE EXCEL REPORT I NEEDED

SLNO START DATE END DATE DYE1 QTY
1 9/9/2010 9/14/2010 BLUE FNR 6.00
2 9/9/2010 9/14/2010 YELLLOW FN2R 6.00
3 9/9/2010 9/14/2010 RED FN2BL 3.00

THUS I WANT TO SORT ONLY DYE CONSUMPTION BETWEEN TWO DATES, THAT IS IT , I DONOT WANT OTHER ITEMS FROM DATAGRID.
NOTE I HAVE OMITTED JOBNO,CUSTOMER,COLOUR AND I HAVE SUMMED UP SIMILAR DYEQTY BETWEEN TWO DATES.


PLEASE HELP ME

THANKS IN ADVANCE

WITH BEST REGARDS

S. JEYARAMAN
Was This Post Helpful? 0

Page 1 of 1