displaying data in a distinct format in excel sheet

Hello,

im retreiving data from oracle db and want to display this data in excel in a special format, im retreiving especially
order_nr, version_nr, version_desc, station_nr, cusomer_nr, price_nr

I have placed combobox and popuilated with order_nr and im retreiving data depending on its selection.

so for eg:- if user sel an order number and cliks ok button,  i want to dynamically display data in this particular format

ver_nr     version_ desc (in one line)
station_nr    cusomer_nr     price_nr (all the stations numbers belonging to this version)
station_nr    cusomer_nr     price_nr
station_nr    cusomer_nr     price_nr
station_nr    cusomer_nr     price_nr

ver_nr     version_ desc (the same process should be repated for number of versions)
station_nr    cusomer_nr     price_nr
station_nr    cusomer_nr     price_nr
station_nr    cusomer_nr     price_nr
station_nr    cusomer_nr     price_nr

down is my code, im not getting how to display ver_nr and version_desc in one line and then go further and start with new ver and so on

could anyone please help

thanks in advance

----------

Dim sel As String
Dim conn1 As ADODB.Connection
Dim rec1 As ADODB.Recordset
Static j As String

sel = ComboBox1.List(ComboBox1.ListIndex) + (",") + ComboBox2.List(ComboBox2.ListIndex)
MsgBox sel


Set conn1 = New ADODB.Connection

conn1.Open "Driver={Microsoft ODBC for Oracle};" & _
                   "Server=;" & _
                   "Uid=;" & _
                   "Pwd=;"

   Set rec1 = New ADODB.Recordset

rec1.Open "SELECT DISTINCT * FROM v_offrdet_kostdet1 WHERE vangnr ='" & sel & "'", conn1

j = 22

While Not rec1.EOF
Tabelle2.Cells(j, 1) = (rec1.Fields("vvernr").Value)
Tabelle2.Cells(j, 2) = (rec1.Fields("vvbes").Value)
j = j + 1
   Tabelle2.Cells(j, 3) = (rec1.Fields("vstnnr").Value)
   Tabelle2.Cells(j, 4) = (rec1.Fields("vuanr").Value)
   Tabelle2.Cells(j, 5) = (rec1.Fields("vpsp").Value)
   Tabelle2.Cells(j, 7) = (rec1.Fields("vstnben").Value)
   Tabelle2.Cells(j, 8) = (rec1.Fields("vve").Value)
      rec1.MoveNext
Wend

rec1.Close
Set rec1 = Nothing

End Sub

vihar123Asked:
Who is Participating?
 
gbzhhuCommented:
OK

Try changing this to

Tabelle2.Range("B" + j) = (rec1.Fields("vvernr").Value)
Tabelle2.Range("E" + j) = (rec1.Fields("vvbes").Value)

to

Tabelle2.Range("B" & j) = (rec1.Fields("vvernr").Value)
Tabelle2.Range("E" & j) = (rec1.Fields("vvbes").Value)
0
 
vihar123Author Commented:
please can i expect some posts, its little bit urgent
0
 
gbzhhuCommented:
Hi It is me back!!

Change this
Tabelle2.Cells(j, 1) = (rec1.Fields("vvernr").Value)
Tabelle2.Cells(j, 2) = (rec1.Fields("vvbes").Value)

to
Tabelle2.Cells(j, 2) = (rec1.Fields("vvernr").Value) & "  " & (rec1.Fields("vvbes").Value)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
vihar123Author Commented:
>>>Tabelle2.Cells(j, 2) = (rec1.Fields("vvernr").Value) & "  " & (rec1.Fields("vvbes").Value)


its not displaying
----------------


While Not rec1.EOF
Tabelle2.Cells(j, 2) = (rec1.Fields("vvernr").Value) & "  " & (rec1.Fields("vvbes").Value)
j = j + 1
   Tabelle2.Cells(j, 3) = (rec1.Fields("vstnnr").Value)
   Tabelle2.Cells(j, 4) = (rec1.Fields("vuanr").Value)
   Tabelle2.Cells(j, 5) = (rec1.Fields("vpsp").Value)
   Tabelle2.Cells(j, 6) = (rec1.Fields("vstnben").Value)
   Tabelle2.Cells(j, 7) = (rec1.Fields("vve").Value)
   
Range("A24:A" & j).Select
Selection.ClearOutline
Selection.Rows.Group
Selection.HorizontalAlignment = xlCenter
Selection.Font.Bold = True

 Tabelle2.Cells(j, 1).EntireRow.Select

    With Selection.Interior
        .ColorIndex = 8
        .Pattern = xlSolid
         
    End With
   
      rec1.MoveNext
Wend
0
 
vihar123Author Commented:
it is displaying in the whole of b column where i want something like

versionnr 1   versiondesc xxxxxxxxxx
0
 
vihar123Author Commented:
well actully what im trying to achieve is that display version nr with all the belonging station nrs and colour them and group them and repeat the same for every version

so it shoud be like

ver nr: 1  verdesc : yyyyyy
station_nr    cusomer_nr     price_nr
station_nr    cusomer_nr     price_nr
station_nr    cusomer_nr     price_nr

colour
group
ver nr: 2  verdesc : kkkkkk
station_nr    cusomer_nr     price_nr
station_nr    cusomer_nr     price_nr
station_nr    cusomer_nr     price_nr

colour
group
0
 
vihar123Author Commented:
is the explanation clear?? i can explain in more detail when needed
0
 
gbzhhuCommented:
What was the original code dosplaying

this original code
Tabelle2.Cells(j, 1) = (rec1.Fields("vvernr").Value)
Tabelle2.Cells(j, 2) = (rec1.Fields("vvbes").Value)

I am going to a meeting for one hour.  give me as much info as you can and I will post when I come out of the meeting.

You can save the sheet as tab delimited text and paste the text in the text file into here so I can see the display, then show where you want to change the display
0
 
vihar123Author Commented:
1                  ver 1 von 1000.002            
            10      10      0            10000
            20      20      0            20000
            30      30      0            30000
            40      40      0            40000
            50      50      0            50000
            60      60      0            60000
            70      70      0            70000
            80      80      0            80000
            90      90      0            90000
            100      100      0            100000
            10      10      0            10000
            20      20      0            20000
            30      30      0            30000
            40      40      0            40000
            50      50      0            50000
            60      60      0            60000
            70      70      0            70000
            80      80      0            80000
            90      90      0            90000
            100      100      0            100000


so above to the left 1 is the version nr and adjacent is the description and below all the values that belong to this particular version,
i want the same thing to get repeated for other versions, but with this code
Tabelle2.Cells(j, 1) = (rec1.Fields("vvernr").Value)
Tabelle2.Cells(j, 2) = (rec1.Fields("vvbes").Value)

its is populating the A and B columns, but for me i want in the above format and other this is i made a view of required fields which looks like this

CREATE OR REPLACE VIEW v_offrdet_kostdet1 (
  vangnr,
  vvernr,
  vvbes,
  vstnnr,
  vuanr,
  vpsp,
  vstnben,
  vve
) AS
SELECT
od.ang_nr, Od.ver_nr, od.ver_besh, k.stn_nr, k.line_nr, k.psp_el_nr, k.be, k.ges_preis
FROM offrdet od, kostdet k
/

so with my query
"SELECT DISTINCT * FROM v_offrdet_kostdet1 WHERE vangnr ='" & sel & "'", conn1
it fetches all the repetitives of vernr
but me i just want distinct ver no

for eg: 1000.001 has 3 versions, and i want to display these three versions one after the other in the above format with colouring and grouping

well i think its little confusing but when you are back i can explain step by step

                                    
0
 
vihar123Author Commented:
im trying something like this, hope youll get an idean what im trying to do........



Dim sel As String
Dim conn1 As ADODB.Connection
Dim rec1 As ADODB.Recordset
Dim rec2 As ADODB.Recordset
'Static j As String

sel = ComboBox1.List(ComboBox1.ListIndex) + (",") + ComboBox2.List(ComboBox2.ListIndex)
MsgBox sel

Range("A24:IV" & j).Select
Selection.ClearContents
Selection.ClearOutline
Selection.ClearFormats

Set conn1 = New ADODB.Connection

conn1.Open "Driver={Microsoft ODBC for Oracle};" & _
                   "Server=;" & _
                   "Uid=;" & _
                   "Pwd=;"

   Set rec1 = New ADODB.Recordset
   Set rec2 = New ADODB.Recordset

rec1.Open "SELECT DISTINCT vvernr, vvbes FROM v_offrdet_kostdet1 WHERE vangnr ='" & sel & "'", conn1

j = 22

'Tabelle2.Range("B" + j) = (rec1.Fields("vvernr").Value)
'Tabelle2.Range("E" + j) = (rec1.Fields("vvbes").Value)


While Not rec1.EOF
Selection.HorizontalAlignment = xlCenter
Selection.Font.Bold = True

Tabelle2.Range("B" + j) = (rec1.Fields("vvernr").Value)
Tabelle2.Range("E" + j) = (rec1.Fields("vvbes").Value)

'Tabelle2.Cells(j, 2) = (rec1.Fields("vvernr").Value) & "  " & (rec1.Fields("vvbes").Value)
rec2.Open "SELECT DISTINCT * FROM v_offrdet_kostdet1 WHERE vangnr ='" & sel & "'", conn1
j = j + 1
   
   Tabelle2.Cells(j, 3) = (rec2.Fields("vstnnr").Value)
   Tabelle2.Cells(j, 4) = (rec2.Fields("vuanr").Value)
   Tabelle2.Cells(j, 5) = (rec2.Fields("vpsp").Value)
   Tabelle2.Cells(j, 6) = (rec2.Fields("vstnben").Value)
   Tabelle2.Cells(j, 7) = (rec2.Fields("vve").Value)
   
Range("A24:A" & j).Select
Selection.ClearOutline
Selection.Rows.Group
 
 
 Tabelle2.Cells(j, 1).EntireRow.Select

    With Selection.Interior
        .ColorIndex = 8
        .Pattern = xlSolid
         
    End With
   rec2.MoveNext
      rec1.MoveNext
     
Wend

rec1.Close
Set rec1 = Nothing
rec2.Close
Set rec2 = Nothing

End Sub
0
 
gbzhhuCommented:
By the way are you in Holland?
0
 
vihar123Author Commented:
Hi gbzhhu,

Sorry for the late reply, was out for some time, well somehow i made some hit and trials and its working

thanks a lot

and im in Germany

how abt u?

0
 
gbzhhuCommented:
Glad you sorted it out.

I am in England

Guten Tag
0
 
vihar123Author Commented:
Danke

but Im actually from India

Namaste :-)
0
 
gbzhhuCommented:
mera nam Hassan

Tum hara namkiahe?

I am slo not English, I am from Somalia :-)
0
 
vihar123Author Commented:
hey thats cool,

how do you know this????
0
 
vihar123Author Commented:
mera naam vihar
0
 
gbzhhuCommented:
When I was growing up back home up to age of 15 all I watched was indian films, old school like Amita Bachan, Dharmendra, Amjad Khan, Mithum (can't spell his surname ;-)), Rekha, Hema Malini etc.

Do you remember the famous love song
Kaphee Kaphee mera dil me  (I just wrote what it sounds like)
0
 
vihar123Author Commented:
cool, bollywood is famous
0
 
vihar123Author Commented:
one mor thing i need is to copy some cells from the excel sheet and paste it in the outlook, how can i do it dynamically..........
ill open this as a new thread and though i need it coming week, but just curious and to save time:-)


0
 
vihar123Author Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.