?
Solved

how to display all values in excel sheet

Posted on 2005-04-15
16
Medium Priority
?
200 Views
Last Modified: 2011-04-14
Hello,

im trying to retreive some data based on a value, for eg: im having a_nr as 1500.001

this particular a_nr has some version numbers, ie 1500.001 ver1
                                                                                      ver1
                                                                                      ver2
etc, so with my code im able to display one one ver using range, but what i want to do is display all the versions numbers that belong to 1500.001

can somebody tell me how to go about

thanks

-----------------

Dim clk As String

clk = ComboBox1.List(ComboBox1.ListIndex) + "." + ComboBox2.List(ComboBox2.ListIndex)

'MsgBox clk

Dim conn As ADODB.Connection
Dim rec As ADODB.Recordset


Set conn = New ADODB.Connection

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

   Set rec = New ADODB.Recordset

rec.Open "SELECT * FROM project_data3 where ang_kost1='" & clk & "'", conn

While Not rec.EOF
   
   Tabelle2.Range("A23") = (rec.Fields("ver_nr1").Value)
   Tabelle2.Range("B23") = (rec.Fields("ver_bes1").Value)
   rec.MoveNext
   Wend

rec.Close
Set rec = Nothing
0
Comment
Question by:vihar123
  • 9
  • 7
16 Comments
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13789246
The question is not clear.

What is a_nr ?  Could please elaporate further.  try to explain a bit more
0
 

Author Comment

by:vihar123
ID: 13789308
its something like order number, and this order number has some versions, so when the user selects this particular order number i want to display all the version numbers belonging to this order nr and mostly the version number are repeated , that is ver1 comes for many n number of times,

so basically im trying to diplay all the versions that belong to order number

0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13789385
So, the recordset is returning the version numbers, correct and you want to display in excel cells all the versions of the select order number?  Just clarifying.  What are the field names of the version numbers in the database?  Is there a fixed number of versions per order or is this dynamic
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:vihar123
ID: 13789541
not its dynamic and i have made a view of tables and im retreiving data from this view, so the table and values looks something like this, so when the user selects 1500.001, all the ver_nr should be displayed


ANG_KOST1 VER_NR1
1500.001       1
1500.001       1
1500.001       1
1500.001       1
1500.001       1
1500.001       1
0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13789632
Now I see.  Try this

Dim i As Integer

While Not rec.EOF  
   i = i + 1
   Tabelle2.Cells(23, i) = (rec.Fields("ver_nr1").Value)
   rec.MoveNext
Wend

0
 

Author Comment

by:vihar123
ID: 13789719
cool but i want them in a column something like A starting from 23 row
0
 

Author Comment

by:vihar123
ID: 13789846
is it possible, im trying with range and other thins but its nor working
0
 
LVL 12

Accepted Solution

by:
gbzhhu earned 2000 total points
ID: 13789869
OK here

Dim i As Integer

i = 22

While Not rec.EOF  
   i = i + 1
   Tabelle2.Cells(i,1) = (rec.Fields("ver_nr1").Value)
   rec.MoveNext
Wend
0
 

Author Comment

by:vihar123
ID: 13789897
i want to display data in column A starting from 23 row
0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13789916
Just change this
   Tabelle2.Cells(i,1) = (rec.Fields("ver_nr1").Value)
to
   Tabelle2.Cells(23,i) = (rec.Fields("ver_nr1").Value)

and remove
i = 22
0
 

Author Comment

by:vihar123
ID: 13789946
ok its working thanks..................

0
 

Author Comment

by:vihar123
ID: 13789960
one more thing, though i will open it as a new thread

i need to get some info in between these versions, something like now the output is

1
1
1
1
1
1
1
so i want to bring in some data from db and place them in the beween something like

1
station number 21       description
1
station number 21       description

etc........................

do i need to change my code or just some query
0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13790012
You will need to change code, just post the code (only the bit we discussed --in the while loop) and I will change it for you

You said your display looks like 1
1
1
1
1
1
1

But I thought it will be like

1   1   1   1   1  1  

filling columns in on row 23
0
 

Author Comment

by:vihar123
ID: 13790044
sorry there was a slight misunderstanding, then let me post this as  a  new thread
0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13790058
OK
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month15 days, 2 hours left to enroll

840 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