3.3. CopyFromRecordset Versus Looping

When pulling data from a data source into Excel, you need to decide how you want to process the recordset. For example, if you need all of the fields and records, you have already been introduced to a simple method that does this: the CopyFromRecordset method places the results of a recordset for Excel Range objects in the range in an Excel worksheet. Sometimes, however, you bring in the results of a query but want only a few of the fields. You can accomplish this by looping through the records.

When dealing with ADO and DAO recordsets, there are several methods of record navigation. In most examples, I go to the first record in the recordset and move through the recordset until there are no additional records. You accomplish this by using the MOVEFIRST method of a recordset to go to the first record, and then the MOVENEXT method to move through the recordset. There are several strategies to figure out when you have cycled through all of the records. When you move past the last record, the recordset's EOF flag is set to trUE. I suggest using a While...Wend loop. Assuming your recordset is a variable named rs, you write the While...Wend loop like this:

     rs.movefirst     While Not rs.eof       <Code to work with your Recordset>       rs.movenext     Wend 

Since you have already been introduced to CopyFromRecordSet, let's take a look at an example of where you would want to loop through a recordset. Consider a table of employee information that has 15 fields. Assuming that you need only three of the fields (Last Name, First Name, and Salary), you have two choices for how to accomplish this.

The first method is to simply write your query to pull in only the fields that you want using the criteria that you want. This sounds easy, but in some cases it might be difficult if you are not familiar with how to write SQL and if you do not have the appropriate access rights to make changes to the database. In that case, you would need to find out the field names, loop through the records, and test for criteria, if necessary. You could also pull the records by position.

For this example, assume that the fields that you want are called LName, FName, and EmpSalary and that you already have a variable, rs, which is the recordset object; a variable, x, which is an integer; and a variable, xlws, which is an Excel worksheet. You want those fields for people who have a salary of more than $60,000. This example is the same for both ADO and DAO.

     x = 1     rs.movefirst     While Not rs.eof       If rs.fields("EmpSalary").value > 60000 then       xlws.cells(x,1).value = rs.fields("LName").value       xlws.cells(x,2).value = rs.fields("FName").value       xlws.cells(x,3).value = rs.fields("EmpSalary").value       x = x + 1       End if       rs.movenext     Wend 

The most important part of this Example is that you have the x = x + 1 inside your If statement. If not, you bring in only the records that you want, but you have blank spaces between the records. If you place the x = x + 1 outside the If statement, each time a record is evaluated, x is incremented by one. In this procedure, the variable x determines the row to place the data. After looking at this example, you probably wonder how you get the titles of the fields on the Excel worksheet. When you pull the values by field name, this is very easy to do, either by simply writing the title that you want directly or by accessing the .NAME property of the field object. However, when you perform a CopyFromRecordset, you may not know all of the fields.

Fortunately, there is a very easy method to cycle through the fields. Again, assume that you have a recordset (rs), you also have a variable called fld that is defined as a Field (same for ADO and DAO ADODB.Field or DAO.Field), and finally you have your Excel worksheet defined as xlws. Let's put the titles in the first row and paste the recordset starting in row 2. Here is the code:

     x = 1     For each fld in rs.Fields       xlws.cells(1,x).value = fld.name       x = x + 1     Next     xlws.range("A2").CopyFromRecordset rs 

In general, CopyFromRecordset is quicker than cycling through the records; however, you do give up some control. If you do not see any benefits to moving through the records one at a time, I suggest writing your query to pull the records and fields that you need and then using CopyFromRecordset. You may also have a situation when you want to use the same recordset for multiple purposes, and moving through the records is your best bet. In the previous example, for instance, we wanted to pull only the records where the salary was greater than $60,000. If you wanted to, you could use that same recordset to look at salaries less than $60,000 and place them in another location.