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

Статья
06/20/2023
Применяется к:

Excel 2007, Excel 2003, Excel 2002, Excel 2000, Excel 97

Аннотация

Вы можете передать содержимое набора записей 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

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

Если в системе не установлена база данных Northwind, для установки примеров баз данных можно использовать параметр "Добавить или удалить" для установки Microsoft Office.

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

Темы шаблонов & Office

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

Запустите Visual Basic и создайте проект EXE уровня "Стандартный". Form1 создается по умолчанию.
Добавьте CommandButton в Form1.
Щелкните "Ссылки" в меню "Проект". Добавьте ссылку на библиотеку объектов данных Microsoft ActiveX 2.1.

Вставьте следующий код в раздел кода 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

' Set the string to the path of your Northwind database
    strDB ="c:\program files\Microsoft office\office11\samples\Northwind.mdb"

' Open connection to the database
    cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & strDB & ";"

''When using the Access 2007 Northwind database
    ''comment the previous code and uncomment the following code.
    'cnt.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    '    "Data Source=" & strDB & ";"

' Open recordset based on Orders table
    rst.Open "Select * From Orders", cnt

' Create an instance of Excel and add a workbook
    Set xlApp = CreateObject("Excel.Application")
    Set xlWb = xlApp.Workbooks.Add
    Set xlWs = xlWb.Worksheets("Sheet1")

' Display Excel and give user control of Excel's lifetime
    xlApp.Visible = True
    xlApp.UserControl = True

' Copy field names to the first row of the worksheet
    fldCount = rst.Fields.Count
    For iCol = 1 To fldCount
        xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
    Next

' Check version of Excel
    If Val(Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)) > 8 Then
        'EXCEL 2000,2002,2003, or 2007: Use CopyFromRecordset

' Copy the recordset to the worksheet, starting in cell A2
        xlWs.Cells(2, 1).CopyFromRecordset rst
        'Note: CopyFromRecordset will fail if the recordset
        'contains an OLE object field or array data such
        'as hierarchical recordsets

Else
        'EXCEL 97 or earlier: Use GetRows then copy array to Excel

' Copy recordset to an array
        recArray = rst.GetRows
        'Note: GetRows returns a 0-based array where the first
        'dimension contains fields and the second dimension
        'contains records. We will transpose this array so that
        'the first dimension contains records, allowing the
        'data to appears properly when copied to Excel

' Determine number of records

recCount = UBound(recArray, 2) + 1 '+ 1 since 0-based array

' Check the array for contents that are not valid when
        ' copying the array to an Excel worksheet
        For iCol = 0 To fldCount - 1
            For iRow = 0 To recCount - 1
                ' Take care of Date fields
                If IsDate(recArray(iCol, iRow)) Then
                    recArray(iCol, iRow) = Format(recArray(iCol, iRow))
                ' Take care of OLE object fields or array fields
                ElseIf IsArray(recArray(iCol, iRow)) Then
                    recArray(iCol, iRow) = "Array Field"
                End If
            Next iRow 'next record
        Next iCol 'next field

' Transpose and Copy the array to the worksheet,
        ' starting in cell A2
        xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
            TransposeDim(recArray)
    End If

' Auto-fit the column widths and row heights
    xlApp.Selection.CurrentRegion.Columns.AutoFit
    xlApp.Selection.CurrentRegion.Rows.AutoFit

' Close ADO objects
    rst.Close
    cnt.Close
    Set rst = Nothing
    Set cnt = Nothing

' Release Excel references
    Set xlWs = Nothing
    Set xlWb = Nothing

Set xlApp = Nothing

End Sub

Function TransposeDim(v As Variant) As Variant
' Custom Function to Transpose a 0-based array (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

Нажмите клавишу F5, чтобы запустить проект. Появится форма1.
Щелкните CommandButton в Form1 и обратите внимание, что содержимое таблицы Orders отображается в новой книге в Excel.

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

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

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

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

Ошибка времени выполнения -2147467259: сбой метода CopyFromRecordset объекта Range.

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

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

При назначении массива объекту Диапазона Excel применяются следующие ограничения:

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

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

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

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

Массив не может содержать элемент размером более 255 символов.
Массив не может содержать значения NULL.
Число элементов в массиве не может превышать 5461.

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

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

Ошибка времени выполнения 5. Недопустимая процедура

Call or argument Run-time Error 1004: Application defined or object defined error

Ссылки

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

177991 XL: ограничения передачи массивов в Excel с помощью автоматизации

247412 INFO: Методы передачи данных в Excel из Visual Basic