Jak przesyłać dane z zestawu rekordów ADO do programu Excel za pomocą automatyzacji

Artykuł
06/20/2023
Dotyczy:

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

Podsumowanie

Zawartość zestawu rekordów ADO można przenieść do arkusza programu Microsoft Excel, automatyzując program Excel. Podejście, którego można użyć, zależy od wersji programu Excel, którą automatyzujesz. Program Excel 97, Excel 2000 i Excel 2002 mają metodę CopyFromRecordset, która umożliwia przeniesienie zestawu rekordów do zakresu. Element CopyFromRecordset w programach Excel 2000 i 2002 może służyć do kopiowania obiektu DAO lub zestawu rekordów ADO. Jednak copyFromRecordset w programie Excel 97 obsługuje tylko zestawy rekordów DAO. Aby przenieść zestaw rekordów ADO do programu Excel 97, możesz utworzyć tablicę na podstawie zestawu rekordów, a następnie wypełnić zakres zawartością tej tablicy.

W tym artykule omówiono oba podejścia. Przedstawiony przykładowy kod ilustruje sposób przenoszenia zestawu rekordów ADO do programu Excel 97, Excel 2000, Excel 2002, Excel 2003 lub Excel 2007.

Więcej informacji

Poniższy przykład kodu przedstawia sposób kopiowania zestawu rekordów ADO do arkusza programu Microsoft Excel przy użyciu automatyzacji z programu Microsoft Visual Basic. Kod najpierw sprawdza wersję programu Excel. W przypadku wykrycia programu Excel 2000 lub 2002 używana jest metoda CopyFromRecordset, ponieważ jest wydajna i wymaga mniejszej ilości kodu. Jeśli jednak zostanie wykryty program Excel 97 lub starszy, zestaw rekordów zostanie najpierw skopiowany do tablicy przy użyciu metody GetRows obiektu zestawu rekordów ADO. Tablica jest następnie transponowana tak, aby rekordy były w pierwszym wymiarze (w wierszach), a pola w drugim wymiarze (w kolumnach). Następnie tablica jest kopiowana do arkusza programu Excel poprzez przypisanie tablicy do zakresu komórek. (Tablica jest kopiowana w jednym kroku, a nie w pętli przez każdą komórkę w arkuszu).

W przykładowym kodzie użyto przykładowej bazy danych Northwind dołączonej do pakietu Microsoft Office. Jeśli podczas instalowania pakietu Microsoft Office wybrano folder domyślny, baza danych znajduje się w następujących lokalizacjach:

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

Jeśli baza danych Northwind znajduje się w innym folderze na komputerze, musisz edytować ścieżkę bazy danych w kodzie podanym poniżej.

Jeśli nie masz zainstalowanej bazy danych Northwind w systemie, możesz użyć opcji Dodaj/Usuń dla instalatora pakietu Microsoft Office, aby zainstalować przykładowe bazy danych.

Uwaga Baza danych northwind nie jest zainstalowana podczas instalacji pakietu Microsoft Office 2007. Aby uzyskać program Northwind 2007, odwiedź następującą witrynę sieci Web firmy Microsoft:

Motywy szablonów & pakietu Office

Kroki tworzenia przykładu

Uruchom program Visual Basic i utwórz nowy projekt standardu EXE. Formularz 1 jest tworzony domyślnie.
Dodaj element CommandButton do formularza Form1.
Kliknij pozycję Odwołania z menu Projekt. Dodaj odwołanie do biblioteki Microsoft ActiveX Data Objects 2.1.

Wklej następujący kod do sekcji kodu formularza 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

Naciśnij klawisz F5, aby uruchomić projekt. Zostanie wyświetlony formularz Form1.
Kliknij przycisk Polecenia w formularzu Form1 i zwróć uwagę, że zawartość tabeli Orders jest wyświetlana w nowym skoroszycie w programie Excel.

Używanie polecenia CopyFromRecordset

Dla wydajności i wydajności preferowaną metodą jest CopyFromRecordset. Ponieważ program Excel 97 obsługuje tylko zestawy rekordów DAO z elementem CopyFromRecordset, w przypadku próby przekazania zestawu rekordów ADO do programu CopyFromRecordset z programem Excel 97 występuje następujący błąd:

Błąd czasu wykonywania 430: Klasa nie obsługuje automatyzacji lub nie obsługuje oczekiwanego interfejsu. W przykładzie kodu można uniknąć tego błędu, sprawdzając wersję programu Excel, aby nie używać polecenia CopyFromRecordset dla wersji 97.

Uwaga W przypadku korzystania z zestawu CopyFromRecordset należy pamiętać, że używany zestaw rekordów ADO lub DAO nie może zawierać pól obiektów OLE ani danych tablicy, takich jak hierarchiczne zestawy rekordów. W przypadku uwzględnienia pól dowolnego typu w zestawie rekordów metoda CopyFromRecordset kończy się niepowodzeniem z powodu następującego błędu:

Błąd czasu wykonywania -2147467259: Metoda CopyFromRecordset zakresu obiektów nie powiodła się.

Korzystanie z funkcji GetRows

Jeśli zostanie wykryty program Excel 97, użyj metody GetRows zestawu rekordów ADO, aby skopiować zestaw rekordów do tablicy. Jeśli przypiszesz tablicę zwracaną przez funkcję GetRows do zakresu komórek w arkuszu, dane będą przechodzić przez kolumny, a nie w dół wierszy. Jeśli na przykład zestaw rekordów ma dwa pola i 10 wierszy, tablica jest wyświetlana jako dwa wiersze i 10 kolumn. W związku z tym należy transponować tablicę przy użyciu funkcji TransposeDim() przed przypisaniem tablicy do zakresu komórek. Podczas przypisywania tablicy do zakresu komórek istnieją pewne ograniczenia, o których należy pamiętać:

Podczas przypisywania tablicy do obiektu zakresu programu Excel obowiązują następujące ograniczenia:

Tablica nie może zawierać pól obiektów OLE ani danych tablicy, takich jak hierarchiczne zestawy rekordów. Zwróć uwagę, że przykładowy kod sprawdza ten warunek i wyświetla wartość "Pole tablicy", aby użytkownik był świadomy, że pole nie może być wyświetlane w programie Excel.
Tablica nie może zawierać pól Daty, które mają datę wcześniejszą niż rok 1900. (Zobacz sekcję "Odwołania", aby uzyskać link do artykułu bazy wiedzy Microsoft Knowledge Base). Należy pamiętać, że przykładowy kod formatuje pola Daty jako ciągi wariantów, aby uniknąć tego potencjalnego problemu.

Zwróć uwagę na użycie funkcji TransposeDim() do transponowania tablicy przed skopiowaniem tablicy do arkusza programu Excel. Zamiast tworzyć własną funkcję transponowania tablicy, możesz użyć funkcji Transpose programu Excel, modyfikując przykładowy kod w celu przypisania tablicy do komórek, jak pokazano poniżej:

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

Jeśli zdecydujesz się użyć metody Transpose programu Excel zamiast funkcji TransposeDim(), aby transponować tablicę, należy pamiętać o następujących ograniczeniach w metodzie Transpose:

Tablica nie może zawierać elementu większego niż 255 znaków.
Tablica nie może zawierać wartości Null.
Liczba elementów w tablicy nie może przekraczać 5461.

Jeśli powyższe ograniczenia nie zostaną uwzględnione podczas kopiowania tablicy do arkusza programu Excel, może wystąpić jeden z następujących błędów czasu wykonywania:

Błąd czasu wykonywania 13: niezgodność typu

Błąd 5 czasu wykonywania: nieprawidłowa procedura

wywołanie lub argument Błąd czasu wykonywania 1004: błąd zdefiniowany przez aplikację lub zdefiniowany obiekt

Informacje

Aby uzyskać dodatkowe informacje o ograniczeniach przekazywania tablic do różnych wersji programu Excel, kliknij następujący numer artykułu, aby wyświetlić artykuł w bazie wiedzy Microsoft Knowledge Base:

177991 XL: ograniczenia przekazywania tablic do programu Excel przy użyciu automatyzacji

247412 INFO: Metody przesyłania danych do programu Excel z programu Visual Basic