Daten aus Excel-Sheets importieren

Dieser Artikel ist Teil des Magazins 'Access im Unternehmen', Ausgabe 6/2016.

Daten aus Excel-Sheets importieren

Gelegentlich werden Sie als Access-Entwickler vor der Aufgabe stehen, den Import aus Excel-Dateien zu implementieren. Manchmal gibt es dabei Sonderfälle, in denen etwa die Daten aus mehreren Sheets eingelesen oder bereitgestellt werden sollen. Wir schauen uns an, wie das mit mehreren Sheets gelingt.

Verschiedene Excel-Sheets importieren

Wenn Sie einmal verschiedene Seiten eines Excel-Dokuments importieren möchten, müssen Sie dafür bei der TransferSpreadsheet-Methode des DoCmd-Objekts einen bestimmten Ausdruck für den Parameter Range angeben.

Angenommen, Sie verwenden eine Excel-Datei, die zwei Sheets namens Tabelle1 und Tabelle2 wie in Bild 1 enthält.

Excel-Datei mit zwei Sheets

Bild 1: Excel-Datei mit zwei Sheets

Im ersten Beispiel wollen wir beide Sheets in jeweils eine eigene Tabelle einfügen – die Feldüberschriften sind ja bereits auf geeignete Weise ausgelegt.

Der dazu verwendete Befehl lautet DoCmd.TransferSpreadsheet. Normalerweise würden Sie diesen Befehl wie folgt einsetzen, um die Daten von Excel in eine Access-Datenbank zu importieren:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblTest1", _
    CurrentProject.Path & "Test.xlsx", True

Der erste Parameter legt dabei fest, dass es sich um einen Import und nicht etwa um eine Verknüpfung handelt. Der zweite legt die Art der Quelle fest. Der dritte Parameter bestimmt den Namen der Tabelle, in welche die Daten importiert werden sollen, der vierte die Quelldatei. Schließlich legen Sie mit dem Wert True für den fünften Parameter fest, dass die Werte der ersten Zeile als Spaltenüberschriften interpretiert werden sollen.

Um diese Anweisung testweise wiederholt aufrufen zu können, haben wir diese in die Prozedur aus Listing 1 eingefügt. Die ersten Zeilen dieser Anweisung versuchen, bei deaktivierter eingebauter Fehlerbehandlung eine eventuell vorhandene Tabelle namens tblTest1 zu löschen. Dies kann zum Beispiel fehlschlagen, weil die Tabelle noch gar nicht vorhanden ist – dieser Fehler wird ignoriert.

Public Sub ImportExcelEinfach()
     On Error Resume Next
     DoCmd.DeleteObject acTable, "tblTest1"
     If Err.Number = 2008 Then
         MsgBox "Die Tabelle kann nicht gelöscht und neu erstellt werden, da diese geöffnet ist."
         Exit Sub
     End If
     On Error GoTo 0
     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblTest1", _
         CurrentProject.Path & "Test.xlsx", True
End Sub

Listing 1: Import eines Excel-Sheets

Es kann jedoch auch sein, dass die Tabelle gerade geöffnet ist. In diesem Fall erscheint eine entsprechende Meldung und die Prozedur wird abgebrochen. Alternativ könnten Sie hier auch eine Anweisung voranstellen, welche die Tabelle zuvor schließt.

Das Ergebnis sieht wie in Bild 2 aus. Es werden natürlich nur die Daten der ersten gefundenen Tabelle der Excel-Datei importiert.

Import ohne Angabe des Quellsheets

Bild 2: Import ohne Angabe des Quellsheets

Zwei Sheets in zwei Tabellen

Im nächsten Schritt wollen wir die Daten der beiden Sheets in jeweils eine eigene Tabelle importieren. Hier wird es interessant: Spätestens in der zweiten DoCmd.TransferSpreadsheet-Anweisung müssen wir irgendwie festlegen, dass nicht das erste, sondern das zweite Sheet der Excel-Datei importiert werden soll.

Wenn man den Trick einmal kennt, ist es jedoch ganz einfach: Sie geben einfach für den sechsten Parameter namens Range den Namen des Sheets an, wie er unten im Reiter der Excel-Tabelle angegeben ist – in unserem Fall also etwa Tabelle1 oder Tabelle2. Das ist allerdings noch nicht der ganze Clou: Sie müssen diesen Bezeichnungen nämlich noch ein Ausrufezeichen anhängen, also beispielsweise Tabelle1! oder Tabelle2!. Wie dies aussieht, sehen Sie in Listing 2.

Public Sub ImportExcelNachTabelle()
     On Error Resume Next
     DoCmd.Close acTable, "tblTest1"
     DoCmd.Close acTable, "tblTest2"
     DoCmd.DeleteObject acTable, "tblTest1"
     DoCmd.DeleteObject acTable, "tblTest2"
     On Error GoTo 0
     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
         "tblTest1", CurrentProject.Path & "Test.xlsx", _
         True, "Tabelle1!"
     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
         "tblTest2", CurrentProject.Path & "Test.xlsx", _
         True, "Tabelle2!"
End Sub

Listing 2: Import zweier Excel-Sheets

Das Ergebnis finden Sie schließlich in Bild 3. Die Tabellen könnten auch unterschiedlich aufgebaut sein, da der Import einzeln erfolgt.

Import zweier Sheets in zwei Tabellen

Bild 3: Import zweier Sheets in zwei Tabellen

Zwei Sheets in eine Tabelle

Damit kommen wir zur nächsten Schwierigkeitsstufe: Wir gehen davon aus, dass die beiden zu importierenden Tabellen gleich aufgebaut sind (was praktischerweise in unserem Beispiel der Fall ist) und wollen die Daten beider Tabellen in eine einzige Access-Tabelle importieren (s. Listing 3).

Public Sub ImportExcelInEineTabelle()
     On Error Resume Next
     DoCmd.Close acTable, "tblTest1"
     DoCmd.Close acTable, "tblTest2"
     DoCmd.DeleteObject acTable, "tblTest1"
     DoCmd.DeleteObject acTable, "tblTest2"
     On Error GoTo 0
     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
         "tblTest1", CurrentProject.Path & "Test.xlsx", _
         True, "Tabelle1!"
     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
         "tblTest1", CurrentProject.Path & "Test.xlsx", _
         True, "Tabelle2!"
End Sub

Listing 3: Import zweier Excel-Sheets in eine Access-Tabelle

Die Lösung ist überraschend einfach: Wie brauchen einfach nur bei beiden Aufrufen der Methode DoCmd.TransferSpreadsheet die gleiche Zieltabelle anzugeben, in diesem Fall tblTest. Das Ergebnis zeigt Bild 4.

Import zweier Sheets in die gleiche Tabelle

Bild 4: Import zweier Sheets in die gleiche Tabelle

Nur bestimmte Bereiche importieren

Wenn wir schon einmal dabei sind, können wir uns den Parameter Range der TransferSpreadsheet-Methode auch gleich genauer ansehen. Mit diesem können Sie nämlich auch exakt festlegen, welche Zeilen und Spalten des Excel-Sheets importiert werden sollen.

Wenn Sie etwa nur die erste Spalte der ersten Tabelle importieren möchten, geben Sie für den Parameter Range etwa einen Ausdruck wie den folgenden an:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblTest2", _
    CurrentProject.Path & "Test.xlsx", True, "Tabelle2!A:A"

Zeilenweise importieren

Wenn Sie nur eine Matrix etwa der ersten zwei Zeilen importieren wollen, müssten Sie diesen Wert für den Range-Parameter übergeben:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblTest2", _
    CurrentProject.Path & "Test.xlsx", True, Tabelle2!1:3

Dies würde die Zeile mit den Überschriften als Feldnamen nutzen und die Werte der zweiten und dritten Zeile als Werte zweier neuer Datensätze.

Wenn Sie weiter unten liegende Zeilen importieren wollen, haben Sie das Problem, dass Sie nicht gleichzeitig die erste Zeile als Feldname importieren können. Eine Idee war, in zwei Schritten vorzugehen. Dabei wollten wir erst die erste Zeile importieren und den Parameter HasFieldNames auf True einstellen und dann die weiter unten liegenden Zeilen mit dem Wert False für HasFieldNames importieren. Dies gelingt allerdings leider nicht, weil ein Aufruf von TransferSpreadsheet mit HasFieldNames generische Feldnamen wie F1, F2 und so weiter verwenden möchte – und die sind ja durch den ersten Import nicht vorhanden. Die beiden folgenden Zeilen führen daher zu der Fehlermeldung aus Bild 5:

Fehlermeldung beim Importieren ohne Feldnamen in eine bestehende Tabelle

Bild 5: Fehlermeldung beim Importieren ohne Feldnamen in eine bestehende Tabelle

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblTest2", _
    CurrentProject.Path & "Test.xlsx", True, "Tabelle2!1:1"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblTest2", _
    CurrentProject.Path & "Test.xlsx", False, "Tabelle2!4:5"

Hier heißt es also im Zweifel: Alle Zeilen importieren und dabei die Feldnamen mitnehmen, überflüssige Zeilen anschließend wieder löschen.

Spaltenweise importieren

Für die erste Spalte eines Sheets wie oben schon erwähnt verwenden Sie diesen Range-Parameter:

Tabelle1!A:A

Dementsprechend können Sie auch zusammenhängende Spalten importieren:

Tabelle1!A:C

Die erste Spalte muss nicht zwingend eingebunden werden, aber es funktioniert einfach nur mit zusammenhängenden Spalten. Wenn Sie mehrere zusammenhängende Spaltenbereiche importieren wollen, etwa A bis C und E bis G, schlage ich vor, auch die dazwischenliegenden Spalten zu importieren und die überflüssigen Spalten dann wieder zu löschen.

Zusammenfassung und Ausblick

Mit verschiedenen Werten für den Range-Parameter der TransferSpreadsheet-Methode können Sie die gewünschten Sheets einer Excel-Datei und dort sogar spezielle Bereiche importieren.

Bitte geben Sie die Zeichenfolge in das nachfolgende Textfeld ein

Die mit einem * markierten Felder sind Pflichtfelder.

Neues aus unseren Magazinen
Listenfeld: Reihenfolge mehrerer Einträge...

Wir haben bereits in mehreren Beiträgen beschrieben, wie Sie die individuelle Reihenfolge von Elementen einer Tabelle über den Inhalt eines Feldes etwa namens »ReihenfolgeID« einstellen können –... [mehr]

Diagramme mit gefilterten Daten

In Ausgabe 2/2019 haben wir in zwei Artikeln die modernen Diagramme von Access vorgestellt. Im vorliegenen Beitrag zeigen wir Ihnen, wie Sie diese abhängig von den in einem Formular angezeigten... [mehr]

Benutzerverwaltung mit verschlüsselten...

Wenn Sie in einer Access-Anwendung Benutzer verwalten wollen, die sich per Benutzername und Kennwort an die Anwendung anmelden, sollten Sie sehr sensibel mit den in der Anwendung gespeicherten... [mehr]

HTML-Tabellen mit fester Kopfzeile

In den vorherigen Ausgaben von Access im Unternehmen und in der aktuellen Ausgabe arbeiten wir in einigen Beiträgen mit dem Webbrowser-Steuerelement und stellen Daten, die wir mit den Bordmitteln... [mehr]

Flexible HTML-Tabellen mit fester Kopfzeile

Im Beitrag »HTML-Tabellen mit fester Kopfzeile« haben wir gezeigt, wie Sie Daten aus einer bestimmten Abfrage in einem Webbrowser-Steuerelement so anzeigen, dass die Spaltenköpfe oben fixiert... [mehr]

Berechtigungen per HTML verwalten

Im Beitrag »Benutzerverwaltung mit verschlüsselten Kennwörtern« stellen wir eine Lösung vor, in der wir die Berechtigungen von Benutzergruppen an Datenbankobjekten definieren. Dort benötigen wir... [mehr]

Benutzer und Berechtigungen ermitteln

In den Beiträgen »Benutzerverwaltung mit verschlüsselten Kennwörtern« und »Berechtigungen per HTML verwalten« haben wir die Voraussetzungen für eine Benutzerverwaltung geschaffen. Im vorliegenden... [mehr]

Zugriffsrechte mit Datenmakros

Es gibt verschiedene Möglichkeiten, auf Basis des aktuell angemeldeten Benutzers sicherzustellen, dass dieser nur die für ihn vorgesehenen Aktionen mit Daten durchführen darf – beispielsweise durch... [mehr]

Kennwörter generieren

Für den einen oder anderen Zweck möchten Sie vielleicht Kennwörter generieren oder in einer Benutzeroberfläche die Möglichkeit zum Generieren von Kennwörtern anbieten. Wenn Sie etwa Benutzer zu... [mehr]

Neuer Datensatz von Frontend zu Backend

Für manche Themen gibt es keine kurze, prägnante Überschrift. In diesem Fall wollen wir zeigen, wie Sie einen neuen Datensatz anlegen, der in einer temporären Tabelle im Frontend gespeichert wird,... [mehr]