{"id":4166,"date":"2014-07-07T04:00:47","date_gmt":"2014-07-07T04:00:47","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/wp\/?p=4166"},"modified":"2022-08-06T12:11:12","modified_gmt":"2022-08-06T12:11:12","slug":"displaying-recordset-object-excel-vba","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2014\/07\/displaying-recordset-object-excel-vba\/","title":{"rendered":"Different ways of Reading RecordSet Object in Excel Macro"},"content":{"rendered":"

Dear Friends,<\/p>\n

\nIn my previous article,<\/a> 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<\/strong><\/a>. But in this article I am going to focus on different ways of reading RecordSet<\/strong> Object.<\/p>\n

How to get Column Names from your recordSet Object<\/h3>\n

RecordSetObject.Fields.Count<\/strong> gives the total number of Columns present in your RecordSet Object. You can use following for loop<\/i><\/strong> to get all all the column names of a recordSet in your Excel Sheet. <\/p>\n

\n

\nFollowing is the Syntax to get the column names of a recordSet where all the records are stored.
\nColumn Name of First Column = RecordSetObject(0).Name
\nColumn Name of Second Column = RecordSetObject(1).Name
\n 
\nSimilarly…
\n 
\nColumn Name of Last Column = RecordSetObject(Total Columns – 1).Name\n<\/p>\n<\/blockquote>\n

\r\n            For DBCol = 0 To RecordSetObject.Fields.Count - 1\r\n              Worksheets(\"Data\").Cells(xlRow, xlCol).Value = RecordSetObject(DBCol).Name\r\n              xlCol = xlCol + 1 'move to next column in same row\r\n            Next\r\n<\/code><\/pre>\n
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).<\/i> 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 )  <\/strong><\/p>\n
1. Spreading the whole RecordSet in Sheet (Without Loop)<\/h3>\n
I have explained this with an Example VBA code in my previous article. Click here<\/strong><\/a> to read and understand this Method.<\/p>\n
2. Reading the whole RecordSet Object using Loop<\/h3>\n
RecordSetObject(0).Value<\/strong> gives you the value of the 1st Column value of the 1st Record of your RecordSet Object.  .MoveNext<\/strong> 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<\/i><\/strong>.<\/p>\n
\r\n        xlRow = 1 ' Set it for your Excel Sheet Starting Row\r\n        Do While Not RecordSetObject.EOF  'to traverse till last record\r\n        'This for loop will display the values\r\n        'of all column values in one Single record\r\n            xlCol = 1 'Every next Record Should start from Starting Column\r\n            For DBCol = 0 To RecordSetObject.Fields.Count - 1\r\n              Worksheets(\"Data\").Cells(xlRow, xlCol).Value = RecordSetObject(DBCol).Value\r\n              xlCol = xlCol + 1 'move to next column in same row\r\n            Next\r\n            RecordSetObject.MoveNext 'This moves the loop to next record from the record set\r\n            xlRow = xlRow + 1 'Move to next row in Excel\r\n        Loop\r\n<\/code><\/pre>\n
3. Reading RecordSet Values by Passing Column Names<\/h3>\n
Above VBA code reads all the column values from the RecordSet by passing the Column Index – 0 to Total Columns-1.<\/strong> 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.<\/p>\n
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<\/strong><\/p>\n
\r\n\r\n        xlRow = 1 ' Set it for your Excel Sheet Starting Row\r\n        Do While Not DBrs.EOF  'to loop till last record of the recordSet\r\n            Worksheets(\"Data\").Cells(xlRow, 1).Value = RecordSetObject(\"Column_NAME_1\").Value\r\n            Worksheets(\"Data\").Cells(xlRow, 2).Value = RecordSetObject(\"Column_NAME_2\").Value\r\n            Worksheets(\"Data\").Cells(xlRow, 3).Value = RecordSetObject(\"Column_NAME_3\").Value\r\n            \r\n            DBrs.MoveNext 'This reads the next record from the record set\r\n            xlRow = xlRow + 1 'Move to next row in Excel\r\n        Loop\r\n<\/code><\/pre>\n
I prefer accessing column values by passing the column name. There are two benefits of using this method:<\/p>\n
\n
1. In case of Select * from…<\/strong> 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.<\/p>\n
2. Easy to debug:<\/strong> While debugging your code – Statement RecordSetObject(“Column_NAME_1”).Value<\/i> is clearly understood without referring the actual database table as compared to RecordSetObject(5).Value<\/i>. To know which column is being referred in this statement I need to check the NAME proprty of the 5th Column of your RecordSet\n<\/p>\n<\/blockquote>\n
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 \ud83d\ude42 <\/h3>\n<\/span>","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":45,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[5203,1246,1675],"tags":[],"class_list":["post-4166","post","type-post","status-publish","format-standard","hentry","category-database","category-macro","category-excel-macro-for-beginners"],"yoast_head":"\nDifferent ways of Reading RecordSet Object in Excel Macro - Let's excel in Excel<\/title>\n<meta name=\"description\" content=\"Excel VBA to Copy RecordSet in Excel. Read RecordSet through loop in Excel VBA. How to get Column names from your RecordSet in Excel VBA. VBA Code to Copy only few columns from the RecordSet to Excel\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/vmlogger.com\/excel\/2014\/07\/displaying-recordset-object-excel-vba\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Different ways of Reading RecordSet Object in Excel Macro\" \/>\n<meta property=\"og:description\" content=\"Excel VBA to Copy RecordSet in Excel. Read RecordSet through loop in Excel VBA. How to get Column names from your RecordSet in Excel VBA. VBA Code to Copy only few columns from the RecordSet to Excel\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vmlogger.com\/excel\/2014\/07\/displaying-recordset-object-excel-vba\/\" \/>\n<meta property=\"og:site_name\" content=\"Let's excel in Excel\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:author\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:published_time\" content=\"2014-07-07T04:00:47+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-08-06T12:11:12+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/vmlogger.com_-1.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"628\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Vishwamitra Mishra\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/www.twitter.com\/learnexcelmacro\" \/>\n<meta name=\"twitter:site\" content=\"@learnexcelmacro\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vishwamitra Mishra\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/07\/displaying-recordset-object-excel-vba\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/07\/displaying-recordset-object-excel-vba\/\"},\"author\":{\"name\":\"Vishwamitra Mishra\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"headline\":\"Different ways of Reading RecordSet Object in Excel Macro\",\"datePublished\":\"2014-07-07T04:00:47+00:00\",\"dateModified\":\"2022-08-06T12:11:12+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/07\/displaying-recordset-object-excel-vba\/\"},\"wordCount\":543,\"commentCount\":14,\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"articleSection\":[\"Database\",\"Excel Macro\",\"Excel Macro Tutorial\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2014\/07\/displaying-recordset-object-excel-vba\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/07\/displaying-recordset-object-excel-vba\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/2014\/07\/displaying-recordset-object-excel-vba\/\",\"name\":\"Different ways of Reading RecordSet Object in Excel Macro - Let's excel in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\"},\"datePublished\":\"2014-07-07T04:00:47+00:00\",\"dateModified\":\"2022-08-06T12:11:12+00:00\",\"description\":\"Excel VBA to Copy RecordSet in Excel. Read RecordSet through loop in Excel VBA. How to get Column names from your RecordSet in Excel VBA. VBA Code to Copy only few columns from the RecordSet to Excel\",\"breadcrumb\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/07\/displaying-recordset-object-excel-vba\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2014\/07\/displaying-recordset-object-excel-vba\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/07\/displaying-recordset-object-excel-vba\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/vmlogger.com\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Database\",\"item\":\"https:\/\/vmlogger.com\/excel\/database\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Different ways of Reading RecordSet Object in Excel Macro\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\",\"url\":\"https:\/\/vmlogger.com\/excel\/\",\"name\":\"Let's excel in Excel\",\"description\":\"Let's share knowledge\",\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/vmlogger.com\/excel\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\",\"name\":\"Vishwamitra Mishra\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"width\":528,\"height\":560,\"caption\":\"Vishwamitra Mishra\"},\"logo\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\"},\"description\":\"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.\",\"sameAs\":[\"http:\/\/www.learnexcelmacro.com\",\"http:\/\/www.facebook.com\/vmlogger\",\"https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro\",\"https:\/\/www.youtube.com\/c\/VMLogger\"],\"url\":\"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Different ways of Reading RecordSet Object in Excel Macro - Let's excel in Excel","description":"Excel VBA to Copy RecordSet in Excel. Read RecordSet through loop in Excel VBA. How to get Column names from your RecordSet in Excel VBA. VBA Code to Copy only few columns from the RecordSet to Excel","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/vmlogger.com\/excel\/2014\/07\/displaying-recordset-object-excel-vba\/","og_locale":"en_US","og_type":"article","og_title":"Different ways of Reading RecordSet Object in Excel Macro","og_description":"Excel VBA to Copy RecordSet in Excel. Read RecordSet through loop in Excel VBA. How to get Column names from your RecordSet in Excel VBA. VBA Code to Copy only few columns from the RecordSet to Excel","og_url":"https:\/\/vmlogger.com\/excel\/2014\/07\/displaying-recordset-object-excel-vba\/","og_site_name":"Let's excel in Excel","article_publisher":"http:\/\/www.facebook.com\/vmlogger","article_author":"http:\/\/www.facebook.com\/vmlogger","article_published_time":"2014-07-07T04:00:47+00:00","article_modified_time":"2022-08-06T12:11:12+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/vmlogger.com_-1.png","type":"image\/png"}],"author":"Vishwamitra Mishra","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/www.twitter.com\/learnexcelmacro","twitter_site":"@learnexcelmacro","twitter_misc":{"Written by":"Vishwamitra Mishra","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vmlogger.com\/excel\/2014\/07\/displaying-recordset-object-excel-vba\/#article","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/2014\/07\/displaying-recordset-object-excel-vba\/"},"author":{"name":"Vishwamitra Mishra","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"headline":"Different ways of Reading RecordSet Object in Excel Macro","datePublished":"2014-07-07T04:00:47+00:00","dateModified":"2022-08-06T12:11:12+00:00","mainEntityOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2014\/07\/displaying-recordset-object-excel-vba\/"},"wordCount":543,"commentCount":14,"publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"articleSection":["Database","Excel Macro","Excel Macro Tutorial"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/vmlogger.com\/excel\/2014\/07\/displaying-recordset-object-excel-vba\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/vmlogger.com\/excel\/2014\/07\/displaying-recordset-object-excel-vba\/","url":"https:\/\/vmlogger.com\/excel\/2014\/07\/displaying-recordset-object-excel-vba\/","name":"Different ways of Reading RecordSet Object in Excel Macro - Let's excel in Excel","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/#website"},"datePublished":"2014-07-07T04:00:47+00:00","dateModified":"2022-08-06T12:11:12+00:00","description":"Excel VBA to Copy RecordSet in Excel. Read RecordSet through loop in Excel VBA. How to get Column names from your RecordSet in Excel VBA. VBA Code to Copy only few columns from the RecordSet to Excel","breadcrumb":{"@id":"https:\/\/vmlogger.com\/excel\/2014\/07\/displaying-recordset-object-excel-vba\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vmlogger.com\/excel\/2014\/07\/displaying-recordset-object-excel-vba\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/vmlogger.com\/excel\/2014\/07\/displaying-recordset-object-excel-vba\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vmlogger.com\/excel\/"},{"@type":"ListItem","position":2,"name":"Database","item":"https:\/\/vmlogger.com\/excel\/database\/"},{"@type":"ListItem","position":3,"name":"Different ways of Reading RecordSet Object in Excel Macro"}]},{"@type":"WebSite","@id":"https:\/\/vmlogger.com\/excel\/#website","url":"https:\/\/vmlogger.com\/excel\/","name":"Let's excel in Excel","description":"Let's share knowledge","publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/vmlogger.com\/excel\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5","name":"Vishwamitra Mishra","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","width":528,"height":560,"caption":"Vishwamitra Mishra"},"logo":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/"},"description":"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.","sameAs":["http:\/\/www.learnexcelmacro.com","http:\/\/www.facebook.com\/vmlogger","https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro","https:\/\/www.youtube.com\/c\/VMLogger"],"url":"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/"}]}},"_links":{"self":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/4166"}],"collection":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/users\/45"}],"replies":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/comments?post=4166"}],"version-history":[{"count":0,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/4166\/revisions"}],"wp:attachment":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media?parent=4166"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/categories?post=4166"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/tags?post=4166"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}