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:
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
Can you help me with this?
Regards and thanks again