Different ways of Reading RecordSet Object in Excel Macro

.

Dear Friends,

In my previous article, I emphasized mainly on how to execute different kind of queries in database using Excel VBA. I covered one example How to display or spread a RecordSet in excel Sheet. But in this article I am going to focus on different ways of reading RecordSet Object.

How to get Column Names from your recordSet Object

RecordSetObject.Fields.Count gives the total number of Columns present in your RecordSet Object. You can use following for loop to get all all the column names of a recordSet in your Excel Sheet.

Following is the Syntax to get the column names of a recordSet where all the records are stored.
Column Name of First Column = RecordSetObject(0).Name
Column Name of Second Column = RecordSetObject(1).Name
 
Similarly…
 
Column Name of Last Column = RecordSetObject(Total Columns – 1).Name


            For DBCol = 0 To RecordSetObject.Fields.Count - 1
              Worksheets("Data").Cells(xlRow, xlCol).Value = RecordSetObject(DBCol).Name
              xlCol = xlCol + 1 'move to next column in same row
            Next

RecordSet Object stores all the columns in form of an Array which can be accessed by passing an array Index which starts from Zero (0). This is why in above for loop, DBCol Index variable is initialized from Zero and goes up to one less than total number of columns available ( 0 to n-1 )

1. Spreading the whole RecordSet in Sheet (Without Loop)

I have explained this with an Example VBA code in my previous article. Click here to read and understand this Method.

2. Reading the whole RecordSet Object using Loop

RecordSetObject(0).Value gives you the value of the 1st Column value of the 1st Record of your RecordSet Object. .MoveNext is the method of your RecordSet Object which takes you to the Next Record of your recordSet. Below is the VBA code snippet which reads all the Records of your RecordSet and put it in your Excel Sheet named Data.


        xlRow = 1 ' Set it for your Excel Sheet Starting Row
        Do While Not RecordSetObject.EOF  'to traverse till last record
        'This for loop will display the values
        'of all column values in one Single record
            xlCol = 1 'Every next Record Should start from Starting Column
            For DBCol = 0 To RecordSetObject.Fields.Count - 1
              Worksheets("Data").Cells(xlRow, xlCol).Value = RecordSetObject(DBCol).Value
              xlCol = xlCol + 1 'move to next column in same row
            Next
            RecordSetObject.MoveNext 'This moves the loop to next record from the record set
            xlRow = xlRow + 1 'Move to next row in Excel
        Loop

3. Reading RecordSet Values by Passing Column Names

Above VBA code reads all the column values from the RecordSet by passing the Column Index – 0 to Total Columns-1. Suppose, If you want to read values of a particular column which Name is known to you but it could be 1st Column or 2nd Column or any nth column of your RecordSet Object. I will explain you the method of accessing the value of a column by passing the column name rather Column Index as passed in above example.

Syntax is very much same as above. Instead of passing the Index Number of the Column, you need to pass the Column Name in Double Quotes (” “). RecordSetObject(“Column_Name”).Value



        xlRow = 1 ' Set it for your Excel Sheet Starting Row
        Do While Not DBrs.EOF  'to loop till last record of the recordSet
            Worksheets("Data").Cells(xlRow, 1).Value = RecordSetObject("Column_NAME_1").Value
            Worksheets("Data").Cells(xlRow, 2).Value = RecordSetObject("Column_NAME_2").Value
            Worksheets("Data").Cells(xlRow, 3).Value = RecordSetObject("Column_NAME_3").Value
            
            DBrs.MoveNext 'This reads the next record from the record set
            xlRow = xlRow + 1 'Move to next row in Excel
        Loop

I prefer accessing column values by passing the column name. There are two benefits of using this method:

1. In case of Select * from… queries you do not need to check the position of your column to access it. No matter at what position your column is..can be accessed correctly from this method.

2. Easy to debug: While debugging your code – Statement RecordSetObject(“Column_NAME_1”).Value is clearly understood without referring the actual database table as compared to RecordSetObject(5).Value. To know which column is being referred in this statement I need to check the NAME proprty of the 5th Column of your RecordSet

Are you facing any difficulty in accessing your RecordSet Object?? Put your queries here in comment. We will try to get back on that ASAP 🙂

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

14 Comments

  1. Ananth

    Hello,

    Thanks for this wonderful site, it really helps. Am new to excel macro, Can you let me know how can i query oracle from excel with more than 1000 parameters. my query is like this select * from tb_name where name in (1….2000), Can you please let me know.

    Reply
  2. Ron

    Just to let you know … in your line #1 above ….Click here to read and understand this Method.

    …. does not work.

    Reply
    • Vishwamitra Mishra

      Thanks Ron for pointing this out. Thank you so much !!

      Reply
  3. Akshay

    Hi,

    I am using worksheet range as table and trying to get data in recordset. The datatype of column is determined by first value in data. One of my column has Numeric values at beginning of data and alphanumeric in between.

    Record set considers this column as numeric and blanks/nulls the alphanumeric values of data.
    For eg.

    Data value Corresponding Recordset value
    1234 1234
    ABC12
    566 566
    JKL986

    can anyone please give solution?

    Reply
    • Akshay

      Data value______________ Corresponding Recordset value
      1234____________________________1234
      ABC12___________________________
      566_____________________________566
      JKL986 _________________________

      Reply
  4. Zakaia

    Public Sub ImportData()
    Dim cn As Object
    Dim rs As Object
    Dim strFile As String
    Dim strCon As String
    Dim strSQL As String
    Dim PathFolders As String, DBFileName As String
    Dim idVar As String

    ”Access database
    PathFolders = ThisWorkbook.Path & “\”
    DBFileName = “Database_Time.accdb”
    strFile = PathFolders & DBFileName

    ”This is the Jet 12 connection string, you can get more

    strCon = “Provider=Microsoft.ACE.OLEDB.12.0;” & _
    “Data Source=” & strFile & “;” & _
    “Persist Security Info=False;”

    ‘ Set CnnConn = New ADODB.Connection
    ”Late binding, so no reference is needed

    Set cn = CreateObject(“ADODB.Connection”)
    Set rs = CreateObject(“ADODB.Recordset”)

    cn.Open strCon

    strSQL = “SELECT * ” _
    & “FROM [” & “Table1” & “]” ‘Change Table1 by your table name

    rs.Open strSQL, cn, 1, 1

    strSQL = “Select Distinct TimeStart from Table1″

    ‘Set rs = strCon.OpenRecordset(strSQL)
    rs.MoveFirst

    Do While Not rs.EOF
    idVar = rs!TimeStart ‘Change TimeStart by your Field Name
    MsgBox idVar ‘check value
    rs.MoveNext
    Loop

    ”Tidy up
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

    End Sub

    Reply
  5. Lena

    Hi, your website such a great knowledge tool but I still can not find an answer to my question. I need import data from CSV file into Excel and manipulate with data and format, so I have to treat my CSV data as a Recordset and not sure how to do it. Thanks.

    Reply
    • Vishwamitra Mishra

      Hi,
      You do not need to export CSV in excel file. If you simply open your CSV file in Excel, you can see all of your data in excel which you can edit it directly in excel.
      Let me know if this helped.

      Reply
  6. SIfte

    Hi,

    YEs, CSV files can be opened in EXCEL, however, i believe the question was how to store data from CSV to RECORDSET, manipulate RECORDSET and then populate EXCEL with the results from manipulated RECORDSET.
    The need for doing such would be required if the CSV data is large, hence EXCEL manipulation would be slower.

    Reply
  7. Jomy

    Hi, I need to extract the values of a single field (Column) in a recordset into an array.
    Do you have a better method ?

    Reply
  8. Esper Wehbe

    Hello,
    I am trying to inner join two different tables each one from different database , each database on a different server.
    the query that I am editing is in excel vba macro.
    her is the code:
    I don’t know how to do, how to specify to the select string the address of each table.

    regards
    Esper

    Sub Load_2010_local()
    ‘Declare the connection variables
    Dim cn1 As ADODB.Connection
    Dim cn2 As ADODB.Connection

    Dim rs As ADODB.Recordset

    Dim DBHost_1 As String
    Dim DBHost_2 As String
    Dim DBPort As String
    Dim DBsrvname As String
    Dim DBuid As String
    Dim DBpwd As String
    Dim strcon As String
    Dim SQL_Query As String
    Dim OraDynaSet As Object
    Dim i As Integer
    Dim r As Range
    Dim Query_Date As String
    Dim WS_WorkBook As String
    Dim WS_sheet As String
    Dim sheet As Variant

    ‘ DB connectivity details. To Cent
    DBHost_1 = “p720-1”
    DBHost_2 = “p720-2”
    DBPort = “1521”
    DBsrvname = “icbs”
    DBuid = “acc_inq”
    DBpwd = “acc_inq”

    ‘Define the connection parameters

    ‘Connection string to connect to Oracle using Service Name
    strcon = “Driver={Microsoft ODBC for Oracle}; ” & _
    “CONNECTSTRING=(DESCRIPTION=” & _
    “(ADDRESS = (PROTOCOL = TCP)(HOST = ” & DBHost_1 & “)(PORT =” & DBPort & “))” & _
    “(CONNECT_DATA= (SERVICE_NAME=” & DBsrvname & “))); ” & _
    “uid=” & DBuid & “; pwd=” & DBpwd & “;”

    ‘Connection string to connect to STR_10_S.MDB BDL Microsoft Access Database
    DBProvider = “Microsoft.ace.oledb.12.0” ‘ Define the parameters Connection string
    DBDatasource = “\\HO-ACC-pc09\STR_10\STR_10_S.MDB”

    ‘Connection to STR_10_S.MDB BDL Microsoft using Connection String ‘ Create The connection
    cnstr = “provider=” & DBProvider & “; Data Source=” & DBDatasource & “;” ‘ Define The open connection String

    ‘Create connection
    Set cn1 = New ADODB.Connection
    Set cn2 = New ADODB.Connection

    ‘Open the connection to oracle database using Connection String
    cn1.Open (strcon) ‘Connecion to ICBS Oracle is made

    cn2.Open (cnstr) ‘Connecion to BDL Access database

    ‘ Create a recordset
    Set rs = New ADODB.Recordset ‘ all records in a table

    ‘Enter Situation (Populate)Date
    Populate_Date.Show

    Query_Date = Format(Cells(5, 6), “DD/MMM/YY”)
    SQL_Query = “Select col1,substr(col2,1,1)col2,sum( Balance)/1000 Balance ” & _
    “From ibl_all_2010 Inner Join RowFiletest” & _
    ” ON (RowFiletest.T_CircNumb_T = “”2010″”) ” & _
    ” AND (RowFiletest.T_CircPage_T) = “”0″”) ” & _
    ” AND (AND RowFiletest.T_SituationType_T = “”M””) ” & _
    ” AND (RowFiletest.T_StartDate_D = #01/15/2020#) ” & _
    ” And (RowFiletest.T_ItemNumb_N) = Col1 ” & _
    “Where tab =’L’ ” & _
    ” And cbkd_date = ‘” & Query_Date & “‘ ” & _
    “Group by col1, substr(col2,1,1) ” & _
    “Order by RowFiletest.[T_CountNumb_N],col1,col2”

    Reply
  9. Rogelio

    Hello my name is Rogelio and I would like to see if you can help me on how I can use the copyfromrecordset without headers in excel vba or how I can pass the copyfromrecordset headers as one more record in excel vba

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest