Retrieving Data with the Copy FromRecordset Method

To retrieve an entire recordset into a worksheet, you can use the CopyFromRecordset method of the Range object. This method can take up to three arguments: Data, MaxRows, and MaxColumns. Only the first argument, Data, is required. This argument can be the Recordset object. The optional arguments, MaxRows and MaxColumns, allow you to specify the number of records that should be returned (MaxRows) and the number of fields

(MaxColumns). If you omit the MaxRows argument, all the returned records will be copied to the worksheet. If you omit the MaxColumns argument, all the fields will be retrieved. The GetProducts procedure illustrated below establishes a connection with the Northwind database using the ADO objects. For this procedure to work correctly, you must create a reference to the Microsoft ActiveX Data Objects 2.6 Library (refer to the instructions on setting up a reference to object libraries earlier in this chapter).

Sub GetProducts()

Dim conn As New ADODB.Connection Dim rst As ADODB.Recordset Dim strPath As String strPath = "C:\Program Files\Microsoft Office\" _ & "Office\Samples\Northwind.mdb"

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _

& "Data Source=" & strPath & ";" conn.CursorLocation = adUseClient

' Create a Recordset from all the records ' in the Products table

Set rst = conn.Execute(CommandText:="Products", _

Options:=adCmdTable) ' begin with the first record rst.MoveFirst

' transfer the data to Excel ' get the names of fields first With Worksheets("Sheet3").Range("A1") .CurrentRegion.Clear For j = 0 To rst.Fields.Count - 1

.Offset(1, 0).CopyFromRecordset rst .CurrentRegion.Columns.AutoFit End With rst.Close conn.Close End Sub

The above procedure copies all the records from the Products table in the Northwind database into an Excel worksheet. If you want to copy fewer records, you can use the MaxRows argument as follows:

.Offset(1, 0).CopyFromRecordset rst, 5

This statement tells Visual Basic to copy only five records. The Offset method causes the records to be entered in a spreadsheet, starting with the second spreadsheet row.

To send all the records to the worksheet using the data from only two table fields, use the following statement:

.Offset(1, 0).CopyFromRecordset rst, , 2

This statement tells Visual Basic to copy all the data from the first two columns. The comma between the rst and the number 2 is a placeholder for the omitted MaxRows argument.

0 0

Post a comment