asked on

Excel VBA Read One Column from Recordset

Hi

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
   'http://analysistabs.com/excel-vba/ado-sql-macros-connecting-database/

    '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
            rs.Close

          'Close Connection
           Conn.Close
           
           Exit Sub
EH:

     MsgBox Err.Description
     
End Sub

Open in new window

Rgonzo1971

What type of combobox and where?

Murray Brown

ASKER

A ComboBox on a form in the Excel VBE

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.

Change the highlighted Row source to the range name

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

Example attached.
Combo-Box.xlsb

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Rory Archibald

Do you actually want the data in the worksheet?

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.

Murray Brown

ASKER

Thanks