So wird's gemacht: Übertragen von Daten aus einem ADO-Recordset in Excel mit Automatisierung

Artikel
06/20/2023
Gilt für::

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

Zusammenfassung

Sie können den Inhalt eines ADO-Recordsets auf ein Microsoft Excel-Arbeitsblatt übertragen, indem Sie Excel automatisieren. Der Ansatz, den Sie verwenden können, hängt von der Version von Excel ab, die Sie automatisieren. Excel 97, Excel 2000 und Excel 2002 verfügen über eine CopyFromRecordset-Methode, mit der Sie ein Recordset in einen Bereich übertragen können. CopyFromRecordset in Excel 2000 und 2002 kann zum Kopieren eines DAO- oder ADO-Recordsets verwendet werden. CopyFromRecordset in Excel 97 unterstützt jedoch nur DAO-Recordsets. Um ein ADO-Recordset nach Excel 97 zu übertragen, können Sie ein Array aus dem Recordset erstellen und dann einen Bereich mit dem Inhalt dieses Arrays auffüllen.

In diesem Artikel werden beide Ansätze erläutert. Der dargestellte Beispielcode veranschaulicht, wie Sie ein ADO-Recordset in Excel 97, Excel 2000, Excel 2002, Excel 2003 oder Excel 2007 übertragen können.

Weitere Informationen

Das folgende Codebeispiel zeigt, wie Sie mithilfe der Automatisierung von Microsoft Visual Basic ein ADO-Recordset in ein Microsoft Excel-Arbeitsblatt kopieren. Der Code überprüft zunächst die Version von Excel. Wenn Excel 2000 oder 2002 erkannt wird, wird die CopyFromRecordset-Methode verwendet, da sie effizient ist und weniger Code erfordert. Wenn Excel 97 oder eine frühere Version erkannt wird, wird das Recordset jedoch zuerst mithilfe der GetRows-Methode des ADO-Recordset-Objekts in ein Array kopiert. Das Array wird dann transponiert, sodass sich Datensätze in der ersten Dimension (in Zeilen) und Felder in der zweiten Dimension (in Spalten) befinden. Anschließend wird das Array durch Zuweisen des Arrays zu einem Zellbereich in ein Excel-Arbeitsblatt kopiert. (Das Array wird in einem Schritt kopiert, anstatt jede Zelle im Arbeitsblatt in einer Schleife zu durchlaufen.)

Im Codebeispiel wird die Northwind-Beispieldatenbank verwendet, die in Microsoft Office enthalten ist. Wenn Sie bei der Installation von Microsoft Office den Standardordner ausgewählt haben, befindet sich die Datenbank in:

\Programme\Microsoft Office\Office\Samples\Northwind.mdb

Wenn sich die Northwind-Datenbank in einem anderen Ordner auf Ihrem Computer befindet, müssen Sie den Pfad der Datenbank im unten angegebenen Code bearbeiten.

Wenn die Northwind-Datenbank nicht auf Ihrem System installiert ist, können Sie die Add/Remove-Option für das Microsoft Office-Setup verwenden, um die Beispieldatenbanken zu installieren.

Hinweis Die Northwind-Datenbank wird nicht installiert, wenn Sie 2007 Microsoft Office installieren. Um Northwind 2007 zu erhalten, besuchen Sie die folgende Microsoft-Website:

Designs für Office-Vorlagen &

Schritte zum Erstellen eines Beispiels

Starten Sie Visual Basic, und erstellen Sie ein neues Standard EXE-Projekt. Form1 wird standardmäßig erstellt.
Fügen Sie form1 ein CommandButton-Objekt hinzu.
Klicken Sie im Menü "Projekt" auf "Verweise". Fügen Sie einen Verweis auf die Microsoft ActiveX Data Objects 2.1-Bibliothek hinzu.

Fügen Sie den folgenden Code in den Codeabschnitt von Form1 ein:

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

Drücken Sie F5, um das Projekt auszuführen. Form1 wird angezeigt.
Klicken Sie auf "CommandButton" in Form1, und beachten Sie, dass der Inhalt der Tabelle "Bestellungen" in einer neuen Arbeitsmappe in Excel angezeigt wird.

Verwenden von CopyFromRecordset

Aus Gründen der Effizienz und Leistung ist CopyFromRecordset die bevorzugte Methode. Da Excel 97 nur DAO-Recordsets mit CopyFromRecordset unterstützt, wird beim Versuch, ein ADO-Recordset an CopyFromRecordset mit Excel 97 zu übergeben, der folgende Fehler angezeigt:

Laufzeitfehler 430: Die Klasse unterstützt die Automatisierung nicht oder die erwartete Schnittstelle nicht. Im Codebeispiel können Sie diesen Fehler vermeiden, indem Sie die Excel-Version überprüfen, damit Sie CopyFromRecordset nicht für die Version 97 verwenden.

Hinweis Wenn Sie CopyFromRecordset verwenden, sollten Sie beachten, dass das verwendete ADO- oder DAO-Recordset keine OLE-Objektfelder oder Arraydaten wie hierarchische Recordsets enthalten kann. Wenn Sie Felder eines der Typen in ein Recordset einschließen, schlägt die CopyFromRecordset-Methode mit dem folgenden Fehler fehl:

Laufzeitfehler -2147467259: Fehler bei der Methode "CopyFromRecordset" des Objektbereichs.

Verwenden von GetRows

Wenn Excel 97 erkannt wird, verwenden Sie die GetRows-Methode des ADO-Recordsets, um das Recordset in ein Array zu kopieren. Wenn Sie das von GetRows zurückgegebene Array einem Zellbereich im Arbeitsblatt zuweisen, werden die Daten über die Spalten und nicht über die Zeilen nach unten geleitet. Wenn das Recordset beispielsweise zwei Felder und 10 Zeilen aufweist, wird das Array als zwei Zeilen und 10 Spalten angezeigt. Daher müssen Sie das Array mithilfe der TransposeDim()-Funktion transponieren, bevor Sie das Array dem Zellbereich zuweisen. Beim Zuweisen eines Arrays zu einem Zellbereich sind einige Einschränkungen zu beachten:

Die folgenden Einschränkungen gelten beim Zuweisen eines Arrays zu einem Excel Range-Objekt:

Das Array darf keine OLE-Objektfelder oder Arraydaten enthalten, z. B. hierarchische Recordsets. Beachten Sie, dass im Codebeispiel nach dieser Bedingung gesucht wird und "Arrayfeld" angezeigt wird, damit der Benutzer darauf hingewiesen wird, dass das Feld in Excel nicht angezeigt werden kann.
Das Array darf keine Datumsfelder enthalten, die ein Datum vor dem Jahr 1900 aufweisen. (Einen Microsoft Knowledge Base-Artikellink finden Sie im Abschnitt "Verweise".) Beachten Sie, dass im Codebeispiel Datumsfelder als Variantenzeichenfolgen formatiert werden, um dieses potenzielle Problem zu vermeiden.

Beachten Sie die Verwendung der TransposeDim()-Funktion, um das Array zu transponieren, bevor das Array in das Excel-Arbeitsblatt kopiert wird. Anstatt eine eigene Funktion zum Transponieren des Arrays zu erstellen, können Sie die Transponierungsfunktion von Excel verwenden, indem Sie den Beispielcode ändern, um das Array den Zellen wie unten gezeigt zuzuweisen:

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

Wenn Sie sich entscheiden, die Transpose-Methode von Excel anstelle der TransposeDim()-Funktion zum Transponieren des Arrays zu verwenden, sollten Sie die folgenden Einschränkungen bei der Transpose-Methode beachten:

Das Array darf kein Element enthalten, das größer als 255 Zeichen ist.
Das Array darf keine Nullwerte enthalten.
Die Anzahl der Elemente im Array darf 5461 nicht überschreiten.

Wenn die oben genannten Einschränkungen beim Kopieren eines Arrays in ein Excel-Arbeitsblatt nicht berücksichtigt werden, kann einer der folgenden Laufzeitfehler auftreten:

Laufzeitfehler 13: Typkonflikt

Laufzeitfehler 5: Ungültige Prozedur

Aufruf oder Argument Laufzeitfehler 1004: Anwendungs- oder Objekt definierter Fehler

References

Weitere Informationen zu Einschränkungen beim Übergeben von Arrays an verschiedene Versionen von Excel finden Sie in der Microsoft Knowledge Base in der folgenden Artikelnummer:

177991 XL: Einschränkungen beim Übergeben von Arrays an Excel mithilfe der Automatisierung

247412 INFO: Methoden zum Übertragen von Daten aus Visual Basic nach Excel