[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

displaying data in a distinct format in excel sheet

Posted on 2005-04-21
21
Medium Priority
?
342 Views
Last Modified: 2010-05-02
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

0
Comment
Question by:vihar123
  • 14
  • 7
21 Comments
 

Author Comment

by:vihar123
ID: 13833245
please can i expect some posts, its little bit urgent
0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13833249
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
 

Author Comment

by:vihar123
ID: 13833376
>>>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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:vihar123
ID: 13833392
it is displaying in the whole of b column where i want something like

versionnr 1   versiondesc xxxxxxxxxx
0
 

Author Comment

by:vihar123
ID: 13833462
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
 

Author Comment

by:vihar123
ID: 13833593
is the explanation clear?? i can explain in more detail when needed
0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13833688
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
 

Author Comment

by:vihar123
ID: 13833856
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
 

Author Comment

by:vihar123
ID: 13834245
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
 
LVL 12

Accepted Solution

by:
gbzhhu earned 2000 total points
ID: 13834987
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
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13834997
By the way are you in Holland?
0
 

Author Comment

by:vihar123
ID: 13858625
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
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13858905
Glad you sorted it out.

I am in England

Guten Tag
0
 

Author Comment

by:vihar123
ID: 13860231
Danke

but Im actually from India

Namaste :-)
0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13860259
mera nam Hassan

Tum hara namkiahe?

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

Author Comment

by:vihar123
ID: 13861761
hey thats cool,

how do you know this????
0
 

Author Comment

by:vihar123
ID: 13861767
mera naam vihar
0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13865167
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
 

Author Comment

by:vihar123
ID: 13869007
cool, bollywood is famous
0
 

Author Comment

by:vihar123
ID: 13869025
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
 

Author Comment

by:vihar123
ID: 13908924
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month18 days, 12 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question