DetailPage-MSS-KB

Knowledge Base

Artikel-ID: 246335 - Geändert am: Montag, 7. Mai 2007 - Version: 5.0

Dieser Artikel ist eine Übersetzung des folgenden englischsprachigen Artikels der Microsoft Knowledge Base:
246335  (http://support.microsoft.com/kb/246335/EN-US/ ) How to transfer data from an ADO Recordset to Excel with automation
Bitte beachten Sie: Bei diesem Artikel handelt es sich um eine Übersetzung aus dem Englischen. Es ist möglich, dass nachträgliche Änderungen bzw. Ergänzungen im englischen Originalartikel in dieser Übersetzung nicht berücksichtigt sind. Die in diesem Artikel enthaltenen Informationen basieren auf der/den englischsprachigen Produktversion(en). Die Richtigkeit dieser Informationen in Zusammenhang mit anderssprachigen Produktversionen wurde im Rahmen dieser Übersetzung nicht getestet. Microsoft stellt diese Informationen ohne Gewähr für Richtigkeit bzw. Funktionalität zur Verfügung und übernimmt auch keine Gewährleistung bezüglich der Vollständigkeit oder Richtigkeit der Übersetzung.

Auf dieser Seite

Zusammenfassung

Sie können den Inhalt eines ADO-Recordsets mithilfe der Automatisierung in Excel in ein Microsoft Excel-Arbeitsblatt übertragen. Das hierfür zu verwendende Verfahren ist von der Version von Excel abhängig, die Sie automatisieren möchten. Excel 97, Excel 2000 und Excel 2002 stellen eine CopyFromRecordset-Methode bereit, mit der Sie ein Recordset in einen Bereich übertragen können. In Excel 2000 und 2002 kann CopyFromRecordset zum Kopieren von DAO- und ADO-Recordsets verwendet werden. In Excel 97 unterstützt die CopyFromRecordset-Methode jedoch nur DAO-Recordsets. Um ein ADO-Recordset in Excel 97 zu übertragen, erstellen Sie zunächst ein Array aus dem Recordset, und füllen Sie anschließend einen Bereich mit dem Inhalt dieses Arrays auf.

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

Weitere Informationen

Der Beispielcode weiter unten zeigt, wie Sie ein ADO-Recordset mithilfe der Automatisierung von Microsoft Visual Basic in ein Microsoft Excel-Arbeitsblatt kopieren. Der Code ermittelt zunächst die Version von Excel. Wird die Version Excel 2000 oder 2002 ermittelt, dann wird die CopyFromRecordset-Methode verwendet, da sie sehr effizient ist und weniger Code erfordert. Ermittelt der Code jedoch Excel 97 oder frühere Excel-Versionen, wird das Recordset zunächst mithilfe der GetRows-Methode des ADO-Recordset-Objekts in ein Array kopiert. Das Array wird anschließend transponiert, sodass sich die Datensätze in der ersten Dimension (in Zeilen) und die Felder in der zweiten Dimension (in Spalten) befinden. Danach wird das Array durch Zuweisen des Arrays zu einem bestimmten Zellbereich in ein Excel-Arbeitsblatt kopiert. (Das Array wird in einem Schritt kopiert, d. h. es erfolgt kein schleifenweises Hinzufügen zu den einzelnen Zellen im Arbeitsblatt.)

Im Codebeispiel wird die in Microsoft Office enthaltene Beispieldatenbank Nordwind verwendet. Wenn Sie Microsoft Office im Standardinstallationsordner installiert haben, befindet sich die Datenbank im folgenden Verzeichnispfad:

\Programme\Microsoft Office\Office\Samples\Nordwind.mdb

Wenn sich die Datenbank Nordwind in einem anderen Ordner auf Ihrem Computer befindet, müssen Sie den Datenbankpfad im obigen Beispiel entsprechend bearbeiten.

Wenn die Datenbank Nordwind nicht auf Ihrem System installiert ist, können Sie die Option zum Hinzufügen/Entfernen des Microsoft Office-Setupprogramms verwenden, um die Beispieldatenbanken zu installieren.

Hinweis: Die Datenbank Nordwind wird bei der Installation von Microsoft Office 2007 nicht installiert. Sie erhalten die Datenbank Nordwind 2007 auf folgender Microsoft-Website:
http://office.microsoft.com/de-de/templates/TC012289971031.aspx?pid=CT101428651031 (http://office.microsoft.com/de-de/templates/TC012289971031.aspx?pid=CT101428651031)

Anleitung zum Erstellen des Beispielcodes

  1. Starten Sie Visual Basic, und erstellen Sie ein neues Standard-EXE-Projekt. Form1 wird standardmäßig erstellt.
  2. Fügen Sie eine Befehlsschaltfläche (CommandButton) zu Form1 hinzu.
  3. Klicken Sie im Menü Projekt auf Verweise. Fügen Sie einen Verweis auf die Bibliothek Microsoft ActiveX Data Objects 2.1 hinzu.
  4. Fügen Sie den folgenden Code in das Codefenster 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
    
  5. Drücken Sie die Taste [F5], um das Projekt auszuführen. Form1 wird angezeigt.
  6. Klicken Sie auf die CommandButton (Befehlsschaltfläche) in Form1. Sie werden feststellen, dass der Inhalt der Tabelle "Bestellungen" in einer neuen Arbeitsmappe in Excel angezeigt wird.
Verwendung von CopyFromRecordset

Aus Effizienz- und Leistungsgründen ist CopyFromRecordset die bevorzugte Methode. Da Excel 97 nur DAO-Recordsets mit CopyFromRecordset unterstützt, wird die folgende Fehlermeldung angezeigt, wenn Sie versuchen, in Excel 97 ein ADO-Recordset an CopyFromRecordset zu übergeben:
Laufzeitfehler 430:
Klasse unterstützt keine Automatisierung oder unterstützt die erwartete Schnittstelle nicht.
Im Codebeispiel können Sie diese Fehlermeldung vermeiden, indem Sie zunächst die Version von Excel ermitteln, sodass die Methode CopyFromRecordset nicht für Excel 97 verwendet wird.

Hinweis: Sie sollten bei der Verwendung von CopyFromRecordset berücksichtigen, dass das von Ihnen verwendete ADO- oder DAO-Recordset keine OLE-Objektfelder oder Arraydaten, wie etwa hierarchische Recordsets, enthalten darf. Sind Felder eines dieser beiden Typen in einem Recordset enthalten, schlägt die Methode CopyFromRecordset fehl und die folgende Fehlermeldung wird angezeigt:
Laufzeitfehler -2147467259:
Die Methode 'CopyFromRecordset' für das Objekt 'Range' ist fehlgeschlagen.
Verwendung von GetRows

Wird Excel 97 ermittelt, verwenden Sie die GetRows-Methode des ADO-Recordsets zum Kopieren des Recordsets in ein Array. Wenn Sie das von GetRows zurückgebene Array einem Zellbereich im Arbeitsblatt zuweisen, verlaufen die Daten nicht vertikal entlang den Zeilen, sondern horizontal entlang den Spalten. Wenn das Recordset beispielsweise zwei Felder und 10 Zeilen besitzt, werden für das Array zwei Zeilen und zehn Spalten angezeigt. Daher müssen Sie das Array mithilfe der Funktion TransposeDim() transponieren, bevor Sie es dem Zellbereich zuweisen. Beim Zuweisen eines Arrays zu einem Zellbereich sind einige Einschränkungen zu berücksichtigen.

Die folgenden Einschränkungen gelten beim Zuweisen eines Arrays zu einem "Range"-Objekt in Excel:
  • Das Array darf keine OLE-Objektfelder oder Arraydaten, wie etwa hierarchische Recordsets, enthalten. Beachten Sie, dass der Beispielcode dies überprüft und die Meldung "Array Field" anzeigt, um den Benutzer darüber zu informieren, dass das Feld nicht in Excel angezeigt werden kann.

  • Das Array darf keine Datumsfelder mit einem Datum vor 1900 enthalten. (Weitere Informationen hierzu erhalten Sie, wenn Sie im Abschnitt "Informationsquellen" dieses Artikels auf den entsprechenden Microsoft Knowledge Base-Artikellink klicken.) Beachten Sie, dass der Beispielcode Datumsfelder als Variantzeichenfolgen formatiert, um dieses potentielle Problem zu vermeiden.
Beachten Sie die Verwendung der Funktion TransposeDim() zum Transponieren des Arrays, bevor dieses in das Excel-Arbeitsblatt kopiert wird. Anstatt eine eigene Funktion zum Transponieren des Arrays zu erstellen, können Sie auch die Funktion Transponieren von Excel verwenden, indem Sie den Beispielcode so bearbeiten, dass das Array wie folgt den Zellen zugewiesen wird:
   xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
      xlApp.WorksheetFunction.Transpose(recArray)
				
Wenn Sie sich statt der TransposeDim()-Funktion für die Verwendung der Excel-Funktion Transponieren entscheiden, um das Array zu transponieren, sollten Sie die folgenden Einschränkungen im Zusammenhang mit der Methode Transponieren berücksichtigen:
  • Das Array darf keine Elemente enthalten, die eine Länge von mehr als 255 Zeichen besitzen.
  • Das Array darf keine Null-Werte enthalten.
  • Die Anzahl der Elemente im Array darf 5461 nicht übersteigen.
Wenn die obigen Einschränkungen beim Kopieren eines Arrays in ein Excel-Arbeitsblatt nicht berücksichtigt werden, kann es zur Anzeige einer der folgenden Laufzeitfehlermeldungen kommen:
Laufzeitfehler 13: Typenkonflikt
Laufzeitfehler 5: Ungültiger Prozeduraufruf oder ungültiges Argument
Laufzeitfehler 1004: Anwendungs- oder objektdefinierter Fehler

Informationsquellen

Weitere Informationen über die Beschränkungen beim Weitergeben von Arrays an verschiedene Excel-Versionen finden Sie im folgenden Artikel der Microsoft Knowledge Base:
177991  (http://support.microsoft.com/kb/177991/DE/ ) XL: Beschränkungen beim Weitergeben von Arrays an Excel mithilfe der Automatisierung
Weitere Informationen finden Sie in folgenden Artikeln der Microsoft Knowledge Base:
146406  (http://support.microsoft.com/kb/146406/DE/ ) Wie Abrufen einer Tabelle vor Zugriff in Excel, das DAO verwendet
215965  (http://support.microsoft.com/kb/215965/DE/ ) XL2000: Daten wird 12 :00:00 zuvor vor 1900 angezeigt
243394  (http://support.microsoft.com/kb/243394/DE/ ) HOWTO: verwenden Sie MFC, um in Excel mit Automatisierung ein DAO-Recordset zu kopieren
247412  (http://support.microsoft.com/kb/247412/DE/ ) Methoden zum Übertragen von Daten von Visual Basic zu Excel

Die Informationen in diesem Artikel beziehen sich auf:
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Visual Basic 5.0 Professional Edition
  • Microsoft Visual Basic 6.0 Professional Edition
  • Microsoft Visual Basic 5.0 Enterprise Edition
  • Microsoft Visual Basic 6.0 Enterprise Edition
  • Microsoft ActiveX Data Objects 2.0
  • Microsoft ActiveX Data Objects 2.1
  • Microsoft ActiveX Data Objects 2.5
Keywords: 
kbexpertiseinter kbautomation kbhowto KB246335
Microsoft stellt Ihnen die in der Knowledge Base angebotenen Artikel und Informationen als Service-Leistung zur Verfügung. Microsoft übernimmt keinerlei Gewährleistung dafür, dass die angebotenen Artikel und Informationen auch in Ihrer Einsatzumgebung die erwünschten Ergebnisse erzielen. Die Entscheidung darüber, ob und in welcher Form Sie die angebotenen Artikel und Informationen nutzen, liegt daher allein bei Ihnen. Mit Ausnahme der gesetzlichen Haftung für Vorsatz ist jede Haftung von Microsoft im Zusammenhang mit Ihrer Nutzung dieser Artikel oder Informationen ausgeschlossen.
Freigeben
Weitere Supportoptionen
Microsoft Community-Supportforen
Kontaktieren Sie uns direkt
Zertifizierten Partner finden
Microsoft Store
Folgen Sie uns: