Populate Excel Table with VBA Code

Posted on 2014-08-31
Medium Priority
Last Modified: 2014-08-31
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)
        MsgBox rs.Fields(0)
    Loop Until rs.EOF
    Set rs = Nothing
    Set cn = Nothing   

End Sub

Open in new window

Question by:victoriaharry
1 Comment
LVL 54

Accepted Solution

Rgonzo1971 earned 2000 total points
ID: 40296134

Why not simply in Excel

go to Data / Get External Data / From Access

Select your access DB / OK / OK

by Select Table choose your Table or Query and OK

At import Data Click on Properties...

Tick Refresh data when opening file and maybe tick Remove data ... as well

in the tab Definition you can make an SQL command by Command Text and change Command TYpe to SQL
SELECT Keyword FROM Keywords if your table as too much information for example and click OK

Choose the destination of your data

And click OK


Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question