Populate Excel Table with VBA Code

Posted on 2014-08-31
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 47

    Accepted Solution


    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

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Article by: Martin
    Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now