Populate Excel Table with VBA Code

Hi Guys,

I created a list box in Excel 2013 using the 'Data Validation' button and linked the source to a table in a different sheet. The reason I linked it to a table is the values will be changing and the list box will continue to grow. I couldn't think of another way to do it. The values for the list box are coming from an Access Database using the code below. I used Name Manager to give the table a name so the listbox can use it as a source.It can be referred to by using '=Keywords' in the source of the list box.

What I want to do is run this code when the excel workbook is open so the values from the database are added to the Excel table which means they will dynamically appear in the list box. I'm not sure how to add the values to the existing table in an Excel sheet so any help with this would be appreciated

Private Sub Workbook_Open()


    Dim cn As Object
    Dim rs As Object
    Dim strSql As String
    Dim strConnection As String
    Set cn = CreateObject("ADODB.Connection")
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=C:\UFT\Automation_Interface\Automation.accdb"
    strSql = "SELECT Keyword FROM Keywords;"
    cn.Open strConnection
    Set rs = cn.Execute(strSql)
    
    rs.MoveFirst
    Do
        MsgBox rs.Fields(0)
        rs.MoveNext
    Loop Until rs.EOF
    
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing   
        

End Sub

Select allOpen in new window