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;" & _
strSql = "SELECT Keyword FROM Keywords;"
Set rs = cn.Execute(strSql)
Loop Until rs.EOF
Set rs = Nothing
Set cn = Nothing