Excel-Daten importieren mit TransferSpreadsheet

Die TransferSpreadsheet-Methode des DoCmd-Objekts ist die erste Wahl, wenn es um das Importieren von Excel-Daten per VBA geht. Und auch wenn die Online-Hilfe diese Methode auf einer Bildschirmseite abhakt, gibt es doch einiges mehr zum praktischen Umgang damit zu berichten.

Grundsätzlich können Sie mit der TransferSpreadsheet-Methode nicht nur Daten importieren, sondern auch Verknüpfungen mit Excel-Tabellen erstellen und Daten aus Access-Tabellen nach Excel exportieren. Wir wollen uns in diesem Beitrag jedoch auf den Import konzentrieren. Die Syntax der TransferSpreadsheet-Methode sieht so aus (es gibt noch einen letzten Parameter, der aber nicht verwendet wird):

DoCmd.TransferSpreadsheet(<Transfertyp>, <Dateiformat>, <Tabellenname>, <Dateiname>, <Besitzt Feldnamen>, <Bereich>)

Da wir mit TransferSpreadsheet Daten aus Excel importieren möchten, kommt uns der erste Parameter ganz recht: Mit ihm legen Sie die Art des Datentransfers fest. Dazu verwenden wir den Parameter acImport (die übrigen Konstanten lauten acExport und acLink).

Als Nächstes müssen wir der Methode mitteilen, mit welcher Excel-Version wir es zu tun haben. Wenn Sie die TransferSpreadsheet-Methode für sich selbst verwenden, werden Sie wissen, mit welcher Excel-Version Sie arbeiten. Wenn Sie diese aber in eine Software einbauen, die Sie weitergeben möchten, ist dies möglicherweise nicht der Fall: Dann kann es durchaus vorkommen, dass nicht die gewünschte Excel-Version vorliegt. Also müssen wir herausfinden, um welche Version es sich handelt. Eine einfache Variante erfordert das Erzeugen einer Excel-Instanz und sieht so aus:

Public Function ExcelVersion() As String
    Dim objExcel As Excel.Application
    On Error Resume Next
    Set objExcel = _
    CreateObject("Excel.Application")
    If Err.Number = 0 Then
        With objExcel
        ExcelVersion = .Version
        End With
    End If
    objExcel.Quit
    Set objExcel = Nothing
End Function

Die letzten beiden Zeilen sind theoretisch nicht notwendig, weil die Excel-Instanz mit dem Verlassen des Gültigkeitsbereichs der Variablen objExcel ohnehin zerstört wird, aber Ordnung muss sein. Die obige Funktion liefert für Access 2007 beispielsweise den Wert 12.0 zurück – das ist nicht der Wert, den die TransferSpreadsheet-Methode für den Parameter SpreadsheetType erwartet. Die möchte vielmehr eine der dafür vorgesehenen Konstanten sehen, also beispielsweise acSpreadsheetTypeExcel12, was dem Zahlenwert 9 entspricht. Wie aber gelangen wir von einem String-Ausdruck wie 12.0 zu einer Konstanten wie acSpreadsheetTypeExcel12 Wir werden hier wohl nicht um den Einsatz eines Mappings herumkommen, das wie folgt aussieht:

Public Function GetSpreadsheet(strVersion As _
    String)
    Dim intSpreadsheetType As Integer
    Select Case Val(strVersion)
    Case 12 ''Excel 2007
    intSpreadsheetType = 9
    Case 11, 10, 9, 8 ''Excel 97-2003
    intSpreadsheetType = 8
    End Select
    GetSpreadsheetType = intSpreadsheetType
End Function

Excel-Versionen älter als Excel 97 werden hier nicht berücksichtigt.

Der folgende Parameter ist der Tabellenname der Zieltabelle. Hier können zwei Fälle auftreten: Entweder Sie geben eine Tabelle an, die bereits vorhanden ist, oder einen neuen Tabellennamen. Im ersten Fall fügt die TransferSpreadsheet-Methode die Daten an die angegebene Tabelle an, sonst erstellt sie die Tabelle automatisch neu.

Als Nächstes folgt der Name der Excel-Datei, aus der Sie Daten nach Access importieren möchten. Hier wissen Sie entweder von vornherein, wo die Datei liegt, oder Sie lassen den Benutzer dies auswählen (wie das funktioniert, zeigen wir beispielsweise im Beitrag Excel-Importassistent im Eigenbau, Shortlink 696).

Interessant werden dann wiederum die Parameter HasFieldNames und Range. Wenn Sie diese nicht angeben, liest Access das komplette erste Tabellenblatt der Excel-Datei ein. Der Standardwert für HasFieldNames lautet False. Das bedeutet, dass alle Zeilen des ersten Tabellenblatts ohne Rücksicht auf Verluste eingelesen und in die Zieltabelle geschrieben werden. Geben Sie hingegen den Wert True an, geht Access davon aus, dass die Excel-Tabelle in der ersten Zeile Feldnamen enthält. Dies hat Zweierlei zur Folge:

• Die Feldnamen der neu erstellten Tabelle werden entsprechend den in der ersten Zeile enthaltenen Werten definiert.
• Es werden erst die Daten ab der zweiten Zeile eingelesen.

Andersherum generiert Access die Feldnamen selbst, wenn Sie für den Parameter HasFieldNames den Wert False angeben. Die Feldnamen lauten dann F1, F2, F3 und so weiter.

Dies hat zur Folge, dass Sie nicht erst eine Tabelle mit HasFieldNames:=False erstellen und dann Daten mit HasFieldNames:=True hinzufügen können: Die Tabelle erhielte dann beim Anlegen mit dem ersten Aufruf von TransferSpreadsheet Feldnamen wie F1, F2 und F3 und würde beim zweiten Aufruf mit HasFieldNames:=True einen Fehler auslösen, weil Access dann in der soeben angelegten Tabelle für das erste Feld den Namen F1 vorfindet, die Excel-Tabelle aber in der ersten Zeile einen anderen Feldnamen liefert – andersherum funktioniert dies genauso wenig.

Vorgabe durch existierende Tabellen

Wenn Sie also eine bereits bestehende Access-Tabelle mit TransferSpreadsheet befüllen möchten, müssen Sie fast zwangsläufig die erste Zeile der Excel-Tabelle mit den durch die Access-Tabelle vorgegebenen Feldnamen füttern. Die einzige Alternative ist, TransferSpreadsheet mitzuteilen, dass die Excel-Tabelle keine Spaltenüberschriften enthält, und dafür die Feldnamen in der Zieltabelle auf F1, F2, F3 und so weiter anzupassen. Zu Beispielzwecken nehmen wir an, dass die zu importierende Tabelle ganz einfach wie in Bild 1 aussieht. Mit dem ersten TransferSpreadsheet-Aufruf erstellen wir die Tabelle, legen Feldnamen entsprechend der ersten Zeile der Excel-Tabelle an und fügen die Daten der folgenden Zeilen jeweils als neue Datensätze ein:

Bild 1: Beispieltabelle für den Import per TransferSpreadsheet

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblTest", "c:\test.xlsx", True

Das Ergebnis erscheint dann wie in der Tabelle aus Bild 2.

Bild 2: Frisch importierte Daten in einer neuen Accesstabelle

Bereich definieren

Wenn wir nun weitere Zeilen aus der Excel-Tabelle einlesen möchten, die möglicherweise weiter unten liegen, dann haben wir mit der Angabe des Range-Parameters die Möglichkeit, dies zu tun.Dieser erwartet beispielsweise die Angabe eines nackten Excel-Bereichs wie etwa "A1:C3". Dies würde die linke obere Matrix aus drei mal drei Elementen berücksichten. Genauso können Sie jeden weiteren zusammenhängenden Bereich angeben. Nehmen wir an, wir wollen die untere Zeile nochmals importieren (nicht, dass dies in der freien Natur so passieren würde – dies ist nur ein Beispiel …). Dann sollte der folgende Aufruf doch wohl funktionieren, der die Zeile mit den Überschriften ausklammert und die letzte Zeile unserer Beispieldatei referenziert:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblTest", "c:\test.xlsx", False, "A3:C3"

Es funktioniert aber nicht, denn da wir angegeben haben, dass wir der Excel-Tabelle keine Feldnamen entnehmen sollen, arbeitet TransferSpreadsheet wieder mit den generischen Feldnamen F1, F2, F3 und so weiter – und die passen nun einmal nicht zu den Feldnamen, die wir mit dem ersten Aufruf erzeugt haben. Da wir aber eine Zeile aus der Excel-Datei einlesen möchten, die nicht unmittelbar unter der Zeile mit den Feldnamen liegt, können wir die Feldnamen auch nicht aus der Excel-Tabelle beziehen. Also verwenden wir einen kleinen Trick: Wir gaukeln TransferSpreadsheet vor, dass die Felder der Tabelle die Namen F1, F2 und F3 tragen. Die notwendige Abfrage sieht wie in Bild 3 aus und enthält diese SQL-Anweisung:

Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...

den kompletten Artikel im PDF-Format mit Beispieldatenbank

diesen und alle anderen Artikel mit dem Jahresabo