Loading an ADO Recordset

After saving an ADO recordset to an XML file on disk, you can load it back and read it as if it were a database. To gain access to the records saved in the XML file, use the Open method of the Recordset object and specify the filename including its path and the persisted recordset service provider as "Provider=MSPersist." Let's look at an example.

The following exercise demonstrates how to open a persisted recordset.

1. In the same module where you entered previous procedures, enter the procedure OpenAdoFile, as shown below.

2. Run the OpenAdoFile procedure.

3. Open the C:\Products.xls file created by running this procedure.

Sub OpenAdoFile()

Dim rst As ADODB.Recordset Dim StartRange As Range

Set rst = New ADODB.Recordset ' open your XML file and load it rst.Open "C:\Products.xml", "Provider=MSPersist" ' display the number of records MsgBox rst.RecordCount

' open a new workbook Workbooks.Add

' copy field names as headings to the first row of the worksheet For h = 1 To rst.Fields.Count

ActiveSheet.Cells(1, h).Value = rst.Fields(h - 1).Name

Next

' specify the cell range to receive the data (A2) Set StartRange = ActiveSheet.Cells(2, 1)

'copy the records from the recordset beginning in cell A2 StartRange.CopyFromRecordset rst

'autofit the columns to make the data fit

Range("A1").CurrentRegion.Select

Columns.AutoFit

' close the workbook and save the file ActiveWorkbook.Close SaveChanges:=True, _ Filename:="C:\Products.xls"

End Sub

The example procedure shown above creates a Recordset object and places in it the XML file created by an earlier procedure. After displaying the number of records in the file, the procedure opens a new workbook and fills the first worksheet row with field names. Next, the CopyFromRecordset method is used to retrieve the records into the worksheet. After adjusting the size of the columns to fit the data, the workbook is saved using the standard Excel file format (XLS).

0 0

Responses

Post a comment