Hi everyone!
I need your help again. Unfortunately I was not able to find what I was looking for on the internet.
I would like to populate an activex object (Combobox) with values from a table in Access...
For example: I have a database "test.accdb" with one table [Names] and one field (First_Name) and I want to connect the data from the database with excel.
For retrieving data from Access to Excel I already found a nice macro. BUT I cannot populate my combobox in Excel from the data in Access..
The idea is to click the combobox and the macro retrieve everytime the data from ms access.
The code to retrieve data from access and paste it in excel:
Code
Const Pathname As String = "C:\Users\Susy\Desktop\test.accdb"
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim xlBook As Workbook
Dim xlSheet As Worksheet
Dim rCount As Long
Dim SQL As String
Set xlBook = ActiveWorkbook
Set xlSheet = xlBook.Worksheets(1)
xlSheet.Range("A2:A10000").ClearContents
Set db = OpenDatabase(Pathname)
SQL = "SELECT DISTINCT First_Name "
SQL = SQL & "FROM Names "
Set rs = db.OpenRecordset(SQL, dbOpenSnapshot)
If rs.RecordCount = 0 Then
MsgBox "No data"
Else
rs.MoveLast
rCount = rs.RecordCount
rs.MoveFirst
End If
xlSheet.Range("A2").CopyFromRecordset rs
Display More
Can you help me with this?
Regards and thanks again