CHAPTER 2 n DATA IN, DATA OUT 73
EXCEL 97 AND ADO RECORDSETS
For efficiency and performance, CopyFromRecordset is the preferred method of filling cells with data from
an ADO recordset. Because Excel 97 supports only DAO recordsets with CopyFromRecordset, if you
attempt to pass an ADO recordset to CopyFromRecordset with Excel 97, you receive the following error:
Run-time error 430:
Class does not support Automation or does not support expected interface.
In the code sample, you can avoid this error by checking Excel??™s version using the
ExcelVersionShort property from the cExcelUtils class in the codeLib.xlsm workbook included on
the CD so that you do not use CopyFromRecordset for the 97 version.
Property Get ExcelVersionShort() As String
Dim xlApp As Object
Dim sExcelVersionShort As String
Set xlApp = CreateObject("Excel.Application")
sExcelVersionShort = Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)
Set xlApp = Nothing
ExcelVersionShort = sExcelVersionShort
End Property
Property Get ExcelVersion() As String
Dim xlApp As Object
Dim sExcelVersion As String
Set xlApp = CreateObject("Excel.Application")
sExcelVersion = xlApp.Version
Set xlApp = Nothing
ExcelVersion = sExcelVersion
End Property
If Excel 97 is detected, use the GetRows method of the ADO recordset to copy the recordset into
an array. If you assign the array returned by GetRows to a range of cells in the worksheet, the data goes
across the columns instead of down the rows.
Pages:
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99