Wenn Sie viele Daten aus einer geschlossenen Arbeitsmappe importieren möchten, können Sie dies mit ADO und dem folgenden Makro tun.
Wenn Sie Daten aus einem anderen Arbeitsblatt als dem ersten Arbeitsblatt in der geschlossenen Arbeitsmappe abrufen möchten,
Sie müssen auf einen benutzerdefinierten benannten Bereich verweisen. Das folgende Makro kann wie folgt verwendet werden (in Excel 2000 oder höher):
GetDataFromClosedWorkbook "C:\FolderName\WorkbookName.xls", "A1:B21", ActiveCell, False GetDataFromClosedWorkbook "C:\FolderName\WorkbookName.xls", "MyDataRange", Range ("B3"), True Sub GetDataFromClosedWorkbook(SourceFile As String, SourceRange As String, _ TargetRange As Range, IncludeFieldNames As Boolean) ' erfordert einen Verweis auf die Microsoft ActiveX Data Objects-Bibliothek ' wenn SourceRange eine Bereichsreferenz ist: ' Dies gibt Daten aus dem ersten Arbeitsblatt in SourceFile zurück ' wenn SourceRange a . ist definierte Namensreferenz: ' Dies gibt Daten aus jedem Arbeitsblatt in SourceFile zurück ' SourceRange muss die Bereichsheader enthalten ' Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset Dim dbConnectionString As String Dim TargetCell As Range, i As Integer dbConnectionString = "DRIVER ={Microsoft Excel-Treiber (*.xls)};" & _ "ReadOnly=1;DBQ=" & SourceFile Set dbConnection = New ADODB.Connection On Error GoTo InvalidInput dbConnection.Open dbConnectionString ' die Datenbankverbindung öffnen Set rs = dbConnection.Execute("[" & SourceRange & "]") Set TargetCell = TargetRange.Cells(1, 1) If IncludeFieldNames Then For i = 0 To rs.Fields.Count - 1 TargetCell.Offset(0, i).Formula = rs.Fields(i).Name Next i Set TargetCell = TargetCell .Offset(1, 0) End If TargetCell.CopyFromRecordset rs rs.Close dbConnection.Close ' die Datenbankverbindung schließen Set TargetCell = Nothing Set rs = Nothing Set dbConnection = Nothing On Error GoTo 0 Exit Sub InvalidInput: MsgBox "Die Quelldatei oder Quellbereich ist ungültig!", _ vbExclamation, "Daten aus geschlossener Arbeitsmappe abrufen" End Sub
Eine andere Methode, die die CopyFromRecordSet-Methode nicht verwendet Mit dem Makro unten können Sie den Import durchführen und haben eine bessere Kontrolle über die Ergebnisse, die vom RecordSet zurückgegeben werden.
Sub TestReadDataFromWorkbook() ' füllt Daten aus einer geschlossenen Arbeitsmappe in die aktive Zelle Dim tArray As Variant, r As Long, c As Long tArray = ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "A1:B21") ' ohne Transponieren ' For r = LBound(tArray, 2) To UBound(tArray, 2) ' For c = LBound(tArray, 1) To UBound(tArray, 1) ' ActiveCell.Offset(r, c).Formula = tArray( c, r) ' Next c ' Next r ' mit Transponieren tArray = Application.WorksheetFunction.Transpose(tArray) For r = LBound(tArray, 1) To UBound(tArray, 1) For c = LBound(tArray, 2) To UBound (tArray, 2) ActiveCell.Offset(r - 1, c - 1).Formula = tArray(r, c) Next c Next r End Sub Private Function ReadDataFromWorkbook(SourceFile As String, SourceRange As String) As Variant ' erfordert eine Referenz in die Microsoft ActiveX Data Objects-Bibliothek ' wenn SourceRange eine Bereichsreferenz ist: ' Diese Funktion kann nur Daten aus dem ersten Arbeitsblatt in SourceFile zurückgeben ' wenn SourceRange eine definierte Namensreferenz ist: ' Diese Funktion kann Daten von m ein beliebiges Arbeitsblatt in SourceFile ' SourceRange muss die Bereichsheader enthalten ' Beispiele: ' varRecordSetData = ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "A1:A21") ' varRecordSetData = ReadDataFromWorkbook("C:\FolderName\SourceWbName. xls", "A1:B21") ' varRecordSetData = ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "DefinedRangeName") Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset Dim dbConnectionString As String dbConnectionString = "DRIVER= {Microsoft Excel Driver (*.xls)};ReadOnly=1;DBQ=" & SourceFile Set dbConnection = New ADODB.Connection On Error GoTo InvalidInput dbConnection.Open dbConnectionString ' die Datenbankverbindung öffnen Set rs = dbConnection.Execute("[" & SourceRange & "]") Bei Fehler GoTo 0 ReadDataFromWorkbook = rs.GetRows ' gibt ein zweidimensionales Array mit allen Datensätzen in rs zurück rs.Close dbConnection.Close ' schließt die Datenbankverbindung Set rs = Nothing Set dbConnection = Nothing On Error GoTo 0 Exit Function InvalidInput: MsgBox "Die Quelldatei oder Quellbereich ist ungültig!", vbExclamation, "Get data from closed workbook" Set rs = Nothing Set dbConnection = Nothing End Function
Im Makrobeispiel wird davon ausgegangen, dass Ihr VBA-Projekt einen Verweis auf die ADO-Objektbibliothek hinzugefügt hat.
Sie können dies innerhalb der VBE tun, indem Sie das Menü Extras, Referenzen auswählen und Microsoft . auswählen
ActiveX-Datenobjekte x.x-Objektbibliothek.
Verwenden Sie ADO, wenn Sie für den Datenimport oder -export zwischen ADO und DAO wählen können.