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
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