Передача данных из набора записей ADO в Excel средствами автоматизации

"Примечание. При использовании метода CopyFromRecordset следует иметь в виду, что используемые наборы записей ADO и DAO не могут содержать поля объектов OLE или массивы данных, такие как иерархические наборы записей. Если в наборе записей имеются поля таких типов, при вызове метода CopyFromRecordset произойдет сбой и появится следующее сообщение об ошибке: "  
 
http://support.microsoft.com/kb/246335/ru  

Аннотация

Передать содержимое набора записей ADO в лист Microsoft Excel можно средствами автоматизации Excel. Конкретный подход зависит от автоматизируемой версии Excel. В Excel 97, Excel 2000 и Excel 2002 имеется метод CopyFromRecordset, который можно использовать для передачи данных из набора записей в диапазон ячеек. Метод CopyFromRecordset в Excel 2000 и 2002 можно использовать для копирования наборов записей как DAO, так и ADO. Однако метод CopyFromRecordset в Excel 97 поддерживает только наборы записей DAO. Для передачи данных из набора записей ADO в Excel 97 можно создать на основе набора записей массив, а затем заполнить диапазон ячеек содержимым этого массива.

В данной статье описаны оба подхода. Приведенный в статье образец кода иллюстрирует способ передачи данных из набора записей ADO в Excel 97, Excel 2000, Excel 2002, Excel 2003 или Excel 2007.

Дополнительная информация

В приведенном ниже примере кода показано, как скопировать содержимое набора записей ADO в лист Microsoft Excel средствами автоматизации языка Microsoft Visual Basic. В коде сначала проверяется версия Excel. Если определяется версия Excel 2000 или 2002, используется метод CopyFromRecordset, поскольку он работает эффективно и требует меньшего объема кода. Однако если определяется версия Excel 97 или более ранняя версия, набор записей сначала копируется в массив с помощью метода GetRows объекта набора записей ADO. Этот массив затем транспонируется таким образом, что записи располагаются в его первой размерности (в строках), а поля — во второй (в столбцах). Затем массив копируется в лист Excel путем присвоения массива диапазону ячеек. (Массив копируется за одно действие, а не в цикле по всем ячейкам листа.)

В образце кода используется база данных "Борей" (Northwind), поставляемая с Microsoft Office. Если при установке Microsoft Office была выбрана папка по умолчанию, то эта база данных находится в следующей папке:

\Program Files\Microsoft Office\Office\Samples\Northwind.mdb

Если база данных "Борей" находится в другой папке, необходимо изменить путь к папке в приведенном ниже коде.

Если база данных "Борей" не установлена на компьютере, ее можно установить с помощью функции добавления и удаления компонентов программы установки Microsoft Office.

Примечание. База данных "Борей" не устанавливается при установке выпуска 2007 системы Microsoft Office. Чтобы загрузить базу данных "Борей 2007", перейдите на следующий веб-узел Майкрософт:

h ttp://office.microsoft.com/ru-ru/templates/TC012289971049.aspx

(на английском языке)

Действия по созданию примера

1. Запустите Visual Basic и создайте проект Standard EXE. По умолчанию будет создана форма Form1.
2. Добавьте в форму Form1 элемент управления CommandButton.
3. Выберите в меню Project (проект) пункт References (ссылки). Добавьте ссылку на библиотеку объектов Microsoft ActiveX Data Objects 2.1 Library.
4. Вставьте в раздел кода формы Form1 приведенный ниже код.

Private Sub Command1_Click()
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset

Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object


Dim recArray As Variant

Dim strDB As String
Dim fldCount As Integer
Dim recCount As Long
Dim iCol As Integer
Dim iRow As Integer

' Присвоить строке путь к базе данных "Борей"
strDB ="c:\program files\Microsoft office\office11\samples\Northwind.mdb"

' Открыть подключение к базе данных
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDB & ";"

''При использовании базы данных "Борей" для Access 2007
''закомментируйте предыдущий код и раскомментируйте приведенный ниже код.
'cnt.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
' "Data Source=" & strDB & ";"

' Открыть набор записей на основе таблицы Orders (заказы)
rst.Open "Select * From Orders", cnt

' Создать экземпляр Excel и добавить книгу
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Add
Set xlWs = xlWb.Worksheets("Sheet1")

' Вывести Excel на экран позволить пользователю управлять временем работы Excel
xlApp.Visible = True
xlApp.UserControl = True

' Скопировать имена полей в первую строку листа
fldCount = rst.Fields.Count
For iCol = 1 To fldCount
xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
Next

' Проверить версию Excel
If Val(Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)) > 8 Then
'EXCEL 2000,2002,2003 или 2007: используется метод CopyFromRecordset

' Скопировать набор записей на лист, начиная с ячейки A2
xlWs.Cells(2, 1).CopyFromRecordset rst
'Примечание. При использовании метода CopyFromRecordset произойдет сбой, если набор записей
'содержит поле объекта OLE или массив данных, таких как
'иерархические наборы записей

Else
'EXCEL 97 или более ранней версии: Будет использоваться метод GetRows, а затем массив будет скопирован в Excel

' Скопировать набор данных в массив
recArray = rst.GetRows
'Примечание. Метод GetRows возвращает массив, индексируемый с 0, первая
'размерность которого содержит поля, а вторая
'содержит записи. Массив будет транспонирован таким образом, чтобы
'первая размерность содержала записи, обеспечивая
'правильное отображение данных при копировании в Excel

' Определить количество строк

recCount = UBound(recArray, 2) + 1 '+ 1, поскольку массив индексируется с 0


' Проверить массив на наличие недопустимого содержимого при
' копировании массива в лист Excel
For iCol = 0 To fldCount - 1
For iRow = 0 To recCount - 1
' Обработка полей Date (дата)
If IsDate(recArray(iCol, iRow)) Then
recArray(iCol, iRow) = Format(recArray(iCol, iRow))
' Обработка полей объектов OLE или полей массивов
ElseIf IsArray(recArray(iCol, iRow)) Then
recArray(iCol, iRow) = "Array Field"
End If
Next iRow 'следующая запись
Next iCol 'следующее поле

' Транспонировать и скопировать массив в лист,
' начиная с ячейки A2
xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
TransposeDim(recArray)
End If

' Автоматически подобрать ширину столбцов и высоту строк
xlApp.Selection.CurrentRegion.Columns.AutoFit
xlApp.Selection.CurrentRegion.Rows.AutoFit

' Закрыть объекты ADO
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing

' Освободить ссылки на Excel
Set xlWs = Nothing
Set xlWb = Nothing

Set xlApp = Nothing

End Sub


Function TransposeDim(v As Variant) As Variant
' Пользовательская функция для транспонирования массива, индексируемого с 0 (v)

Dim X As Long, Y As Long, Xupper As Long, Yupper As Long
Dim tempArray As Variant

Xupper = UBound(v, 2)
Yupper = UBound(v, 1)

ReDim tempArray(Xupper, Yupper)
For X = 0 To Xupper
For Y = 0 To Yupper
tempArray(X, Y) = v(Y, X)
Next Y
Next X

TransposeDim = tempArray


End Function





5. Для выполнения проекта нажмите клавишу F5. Появится форма Form1.
6. Нажмите в форме Form1 кнопку CommandButton и обратите внимание на содержимое таблицы Orders (заказы), появившееся в новой книге Excel.

Использование метода CopyFromRecordset

Рекомендуется использовать метод CopyFromRecordset благодаря его высокой эффективности и производительности. Поскольку метод CopyFromRecordset в Excel 97 поддерживает только наборы записей DAO, при попытке передать методу CopyFromRecordset в Excel 97 набор записей ADO появится следующее сообщение об ошибке:

Run-time error 430:
Class does not support Automation or does not support expected interface (Ошибка времени выполнения 430: класс не поддерживает автоматизацию либо не поддерживает соответствующий интерфейс)

В данном примере кода этой ошибки можно избежать путем проверки версии Excel, чтобы не использовать метод CopyFromRecordset для версии Excel 97.

Примечание. При использовании метода CopyFromRecordset следует иметь в виду, что используемые наборы записей ADO и DAO не могут содержать поля объектов OLE или массивы данных, такие как иерархические наборы записей. Если в наборе записей имеются поля таких типов, при вызове метода CopyFromRecordset произойдет сбой и появится следующее сообщение об ошибке:

Run-time error -2147467259:
Method CopyFromRecordset of object Range failed (Ошибка времени выполнения -2147467259: сбой метода CopyFromRecordset объекта Range).

Использование метода GetRows

Если обнаружена версия Excel 97, для копирования набора записей в массив используется метод GetRows набора записей ADO. Если присвоить диапазону ячеек в листе значение массива, возвращенного методом GetRows, данные будут выведены по столбцам, а не по строкам. Например, если набор записей состоит из двух полей и десяти строк, массив будет состоять из двух строк и десяти столбцов. Следовательно, перед присвоением значения массива диапазону ячеек массив необходимо транспонировать с помощью функции TransposeDim(). При присвоении значения массива диапазону ячеек имеется ряд ограничений, которые следует иметь в виду.

Указанные ниже ограничения применяются при присвоении значения массива объекту Range (диапазон) Excel.

• Массив не может содержать поля объектов OLE или массивы данных, такие как иерархические наборы записей. Обратите внимание на то, что в примере кода это условие проверяется, а в случае обнаружения подобного поля выводится сообщение "Array Field" (поле массива), чтобы уведомить пользователя о невозможности отобразить поле в Excel.
  
  
• Массив не может содержать поля типа Date (дата), в которых установлена дата до 1900 г. (Ссылку на соответствующую статью базы знаний Майкрософт см. в разделе "Ссылки".) Отметим, что для обхода этой проблемы в данном примере поля типа Date форматируются как строки типа "Variant".

Перед копированием массива в лист Excel массив транспонируется с помощью функции TransposeDim(). Вместо создания собственной функции для транспонирования массива можно воспользоваться встроенной функцией Excel Transpose, изменив код таким образом, чтобы присвоить массив диапазону ячеек, как показано ниже.

xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
xlApp.WorksheetFunction.Transpose(recArray)

Если для транспонирования массива вместо функции TransposeDim() будет использоваться метод Excel Transpose, следует учитывать ограничения, накладываемые на метод Transpose.

• Массив не может содержать элементов, длина которых превышает 255 знаков.
• Массив не может содержать пустые (Null) значения.
• Количество элементов не может превышать 5461.

Если при копировании массива в лист Excel не принять во внимание приведенные выше ограничения, может возникнуть одна из указанных ниже ошибок времени выполнения.

Ошибка времени выполнения 13: несоответствие типов

Ошибка времени выполнения 5: недопустимый аргумент или вызов процедуры

Ошибка времени выполнения 1004: ошибка, определенная приложением или объектом

Ссылки

Дополнительные сведения об ограничениях на передачу массивов в различные версии Excel см. в следующей статье базы знаний Майкрософт:

1 77991 XL: Ограничения на передачу массивов в Excel средствами автоматизации (Эта ссылка может указывать на содержимое полностью или частично на английском языке)

Для получения дополнительных сведений щелкните приведенные ниже номера статей базы знаний Майкрософт:

1 46406 XL: Загрузка таблицы из Access в Excel с помощью DAO (Эта ссылка может указывать на содержимое полностью или частично на английском языке)

(эта ссылка может указывать на содержимое полностью или частично на английском языке)

2 15965 XL2000: Для дат, предшествующих 1900 г., отображается время 12:00:00 (Эта ссылка может указывать на содержимое полностью или частично на английском языке)

2 43394 Использование MFC для копирования набора записей DAO в Excel средствами автоматизации (Эта ссылка может указывать на содержимое полностью или частично на английском языке)

2 47412 Способы передачи данных из Visual Basic в Excel