So wird's gemacht: Übertragen von Daten aus einem ADO-Recordset in Excel mit Automatisierung
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:
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