displaying data in a distinct format in excel sheet

please can i expect some posts, its little bit urgent

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)

>>>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

it is displaying in the whole of b column where i want something like

versionnr 1   versiondesc xxxxxxxxxx

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

is the explanation clear?? i can explain in more detail when needed

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

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

                                    

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

By the way are you in Holland?

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?

Glad you sorted it out.

I am in England

Guten Tag

Danke

but Im actually from India

Namaste :-)

mera nam Hassan

Tum hara namkiahe?

I am slo not English, I am from Somalia :-)

hey thats cool,

how do you know this????

mera naam vihar

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)

cool, bollywood is famous

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:-)

hello gbzhhu,

could you refer to this question, thanks

https://www.experts-exchange.com/questions/21407207/copy-and-paste-from-excel-to-outlook.html