drop-down list - can the list be populated in code?

[BillE]

Can a dropdown list be populated in code?

I would like to use ado to retrieve a list from an Access database with VBA
and use it to populate a dropdown.

Alternatively, could I populate a group of cells and then dynamically (in
VBA code) set the dropdown list source to the group of cells?

Thanks
Bill

 

[JP]

I found some sample code here:

http://tinyurl.com/3brzjy
http://tinyurl.com/3ywvhr

Does that help?

--JP

 

[Mika Oukka]

The following should generate the data from your access table. Take it from
here.

Mika Oukka - IT-Consultant

Sub Import_From_Access()
'Check the correct Microsoft the correct ActiveX DataObjects Library
Dim cnt As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim stDB As String, stSQL1 As String
Dim stConn As String
Dim wbBook As Workbook
Dim wsSheet1 As Worksheet
Dim lnField As Long, lnCount As Long

Set cnt = New ADODB.Connection
Set rst1 = New ADODB.Recordset
Set wbBook = ThisWorkbook
Set wsSheet1 = wbBook.Worksheets(1)

'Set the correct path below******************
stDB = "c:\temp\test\db1.mdb"
'******************
stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & stDB & ";"

'The 1st raw SQL-statement to be executed.
stSQL1 = "SELECT Tbl1.* FROM Tbl1;"

'Clear the worksheet.
wsSheet1.Range("A1").CurrentRegion.Clear

With cnt
.Open (stConn) 'Open the connection.
.CursorLocation = adUseClient 'Necessary to disconnect the
recordset.
End With

With rst1
.Open stSQL1, cnt 'Create the recordset.
Set .ActiveConnection = Nothing 'Disconnect the recordset.
End With

With wsSheet1
.Cells(1, 1).CopyFromRecordset rst1 'Copy the 1st recordset.
End With

rst1.Close
Set rst1 = Nothing

cnt.Close
Set cnt = Nothing
End Sub