show info from access in excel

**miiia83** · 02-23-2007, 10:04 AM

hi!

Is it possible to make a scrolldownlist in excel that contains info from a table in access..? And then if you choose "C" from the list the info that belongs to "C" should appear??

Mia

**antoka05** · 02-23-2007, 11:04 AM

You can use this code code to populate a listbox with data from access
(to use the code open VB editor (alt + F11), click on menu tools, select reference, from the list select 'Microsoft DAO 3.xx Object Library):

Private Sub Macro1()
   Dim db As Database
   Dim dbName As String
   
   Dim rs As Recordset
   dbName = "C:\...\northwind.mdb"
   Set db = DBEngine.Workspaces(0).OpenDatabase(dbName)
   
   query = "select [field1] from table1"
   Set rs = db.OpenRecordset(query)
   Do While Not rs.EOF
      ListBox1.AddItem rs(0)
   
      rs.MoveNext
   Loop
   rs.Close
   db.Close
End Sub

Regards,
Antonio

**miiia83** · 02-27-2007, 04:45 AM

I didn't get that to work... not that good at VB

**miiia83** · 02-27-2007, 06:11 AM

this is the code that I have... it only shows the info from function_code and description..
I want the FUNCTION_CODE to be shown as an listbox.. and then when you choose "1H" out of the listbox.. then DESCRIPTION should show the info that belongs to "1H".
Did that make any sence??

Sub Get_Category()
    resrow = 3
    rescol = 2
    Sheets("sheet1").Range("b2:j1000").ClearContents
    

    OpenConnection ("RFC")
    'DESCRIPTIONS = tabellnamn
    strQuery = "SELECT * FROM DESCRIPTIONS"
    Set Rs = ConnRFC.Execute(strQuery)
    Do Until Rs.EOF
        Sheets("sheet1").Cells(resrow, rescol).Value = Rs("FUNCTION_CODE")
        Sheets("sheet1").Cells(resrow, rescol + 1).Value = Rs("DESCRIPTION")
        
        resrow = resrow + 1
        Rs.MoveNext
Loop

Kill Connection ("RFC")

Mia