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
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.
Just to let you know … in your line #1 above ….Click here to read and understand this Method.
…. does not work.
Thanks Ron for pointing this out. Thank you so much !!
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?
Data value______________ Corresponding Recordset value
1234____________________________1234
ABC12___________________________
566_____________________________566
JKL986 _________________________
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
Hi,
Do you have a question?
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.
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.
https://stackoverflow.com/questions/53160820/display-data-accordingly-in-excel-using-vba-from-sql-server
Please solve this question. I need a vb code for this
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.
Hi, I need to extract the values of a single field (Column) in a recordset into an array.
Do you have a better method ?
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”
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