Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland

asked on 

Excel VBA Read One Column from Recordset


I have the following Excel VBA code that pulls  data from a SQL database.
How do I read an individual column called "Delay Code" and add these items to a ComboBox?

Sub Main()

On Error GoTo EH

    'Step 1:Add reference for Microsoft Activex Data Objects Library

            '1. Go to VBE (Alt+F11) and Select References.. from Tools Menu.
            '2. Then select ” Microsoft Activex Data Objects Library” from the list.
            '3. And Create sub procedure to write the code:

    'Step 2: Create the Connection String with Provider and Data Source options
    Dim sSQLQry As String
    Dim ReturnArray
    Dim Conn As New ADODB.connection
    Dim rs As New ADODB.Recordset
    Dim DBPath As String, sconnect As String
    sconnect = "xxxxxx;"
    'Step 3: Open the Connection to data source
    Conn.Open sconnect
    sSQLSting = "Select * From  Delays"
    'Step 5: Get the records by Opening this Query with in the Connected data source
       rs.Open sSQLSting, Conn
    'Step 5a - Pull the headers
      For i = 0 To rs.Fields.Count - 1
        Sheet1.Cells(1, i + 1) = rs.Fields(i).Name
      Next i

    'Step 6: Copy the reords into our worksheet
        Sheet1.Range("A2").CopyFromRecordset rs

    'Step 7: Close the Record Set and Connection
           'Close Recordset

          'Close Connection
           Exit Sub

     MsgBox Err.Description
End Sub

Open in new window

Microsoft ExcelVBA

Avatar of undefined
Last Comment
Murray Brown
Avatar of Rgonzo1971

What type of combobox and where?
Avatar of Murray Brown
Murray Brown
Flag of United Kingdom of Great Britain and Northern Ireland image


A ComboBox on a form in the Excel VBE
Avatar of Excel amusant
Excel amusant

Here is one way you can add column values in the combo box.

Select the column you want to add the contents in combo box and change the range name to your desired name.

User generated image
Change the highlighted Row source to the range name

User generated image
Load user-form and you will have the selected column contents in the combo box dropdown.

Example attached.
Avatar of Rgonzo1971

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Do you actually want the data in the worksheet?
Avatar of Norie

Why are you pulling all the fields in the query when you only want one?

Try changing the query to only bring back the column you want.

Then use GetRows to put the data from the column in an array and populate the combobox from the array.
Avatar of Murray Brown
Murray Brown
Flag of United Kingdom of Great Britain and Northern Ireland image


Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo