Datenaustausch mit Excel per VBA

Die Methoden für das Importieren, Exportieren oder Verknüpfen von Daten zwischen Access und Excel haben Sie in den Artikeln “Access und Excel: Import und Export” und “Access und Excel: Verknüpfungen” bereits vor einiger Zeit kennengelernt. Hier knüpfen wir nun an und zeigen Ihnen, wie Sie per VBA die in diesen Artikeln gezeigten Methoden nutzen können.

Beispieldatenbank

Die Beispiele dieses Artikels finden Sie in der Datenbank 1803_ExceldatenPerVBA.accdb.

Assistenten per VBA

Bevor wir die volle Automatisierung betrachten, schauen wir uns an, wie Sie die Assistenten, die Sie in den oben genannten Artikeln kennengelernt haben, per VBA starten. Wozu das Nun: Gegebenenfalls möchten Sie dem Benutzer die Möglichkeit geben, in einer von Ihnen gefertigten Anwendung auf die Daten aus einer Excel-Anwendung zuzugreifen und ihm dabei größtmögliche Freiheit lassen – und dabei dennoch die üblichen Elemente der Benutzeroberfläche dafür bereit zu stellen, also die eingebauten Ribbon-Befehle. Eine professionelle Access-Anwendung sollte nämlich ein von Ihnen erstelltes und auf die Anwendung zugeschnittenes Ribbon enthalten, mit dem Benutzer nur die für diese Anwendung vorgesehenen Funktionen starten darf.

Die Befehle, die üblicherweise über die Benutzeroberfläche gestartet werden, finden Sie unter VBA meist als Aufrufe der Methode RunCommand mit verschiedenen Parametern. Dazu geben Sie beispielsweise die Anweisung RunCommand ins Direktfenster ein und tippen dann die Anfangsbuchstaben der gewünschten Aktion, in Bild 1 zum Beispiel acCmdI… – und erhalten dann alle Methoden, die mit dem Import von Daten zu tun haben. Uns interessiert in diesem Fall beispielsweise die Methode mit dem Parameter acCmdImportAttachExcel:

Bild 1: Auswahl der Befehle zum Importieren aus verschiedenen Quellen

RunCommand acCmdImportAttachExcel

Dieser zeigt nach dem Aufruf etwa den Dialog aus Bild 2 an, mit dem Sie Daten importieren oder verknüpfen können.

Bild 2: Assistent zum Importieren oder Verknüpfen von Excel-Daten

Wenn Sie hingegen Daten exportieren wollen, rufen Sie diesen Befehl mit dem Parameter acCmdExport-Excel auf:

RunCommand acCmdExportExcel

Wenn Sie diesen Befehl aufrufen, kann es allerdings sein, dass Sie die Meldung aus Bild 3 erhalten. Warum das Das geschieht doch nie, wenn Sie den entsprechenden Ribbon-Befehl aufrufen

Bild 3: Fehlerhafter Aufruf des Export-Assistenten

Die Lösung liegt nahe: Werfen Sie beim Auftreten dieses Fehlers einen Blick auf das Ribbon-Tab Externe Daten, finden Sie in der Gruppe Exportieren lediglich deaktivierte Einträge vor (siehe Bild 4). In diesem Fall ist schlicht und einfach gerade kein Element im Navigationsbereich markiert, dass Sie exportieren könnten. Zur Sicherheit können Sie die Fehlerbehandlung in einer Prozedur zum Aufruf dieser Methode durch eine benutzerdefinierte Fehlerbehandlung ersetzen, die prüft, ob das öffnen des Assistenten einen Fehler auslöst und gegebenenfalls eine entsprechende Meldung anzeigen:

Bild 4: Die Export-Befehle sind deaktiviert.

Public Sub ExcelExportieren()
    On Error Resume Next
    RunCommand acCmdExportExcel
    If Err.Number = 2046 Then
        MsgBox "Sie müssen eine Tabelle oder Abfrage                    markieren, bevor Sie den Befehl zum                                   Exportieren aufrufen."
    End If
    On Error GoTo 0
End Sub

Export mit Parametern

Nun wollen Sie dem Benutzer aber vermutlich möglichst viel Arbeit abnehmen und mögliche Fehlerquellen bei der Verwendung von Assistenten abnehmen. Daher gibt es noch tiefergehende Möglichkeiten, Daten zu importieren, verknüpfen oder exportieren. Diese bietet die Methode TransferSpreadsheet des DoCmd-Objekts an. Neben der Anweisung RunCommand ist das DoCmd-Objekt mit seinen verschiedenen Methoden die zweite Möglichkeit, die Befehle, die Sie sonst über die Benutzeroberfläche starten, auszuführen. TransferSpreadsheet ist dabei nur eine von verschiedenen Möglichkeiten, Daten mit anderen Datenquellen auszutauschen (siehe Bild 5).

Bild 5: Die Transfer…-Methoden des DoCmd-Objekts

Für den Austausch mit tabellenartig aufgebauten Dateien wie es etwa bei den Excel-Tabellen der Fall ist, nutzen wir die Methode TransferSpreadsheet. Diese bietet, wie wir Bild 6 entnehmen können, eine Reihe von Parametern, die wir im Folgenden erläutern:

Bild 6: Die Parameterliste der TransferSpreadsheet-Methode

• TransferType: Einer der Werte acExport, acImport oder acLink. acExport exportiert aus der angegebenen Tabelle, acImport importiert in die angegeben Tabelle und acLink erstellt eine Verknüpfung zu der angegebenen Datei.
• SpreadsheetType: Gibt das Format der Quell-/Zieldatei an beziehungsweise die Excel-Version, welche die Datei lesen kann/erstellt hat. Mögliche Werte: acSpreadsheetTypeExcel3, acSpreadsheetTypeExcel4, acSpreadsheetTypeExcel5, acSpreadsheetTypeExcel7, acSpreadsheetTypeExcel8, acSpreadsheetTypeExcel9, acSpreadsheetTypeExcel12 und acSpreadsheetTypeExcel12XML.
• TableName: Name der Tabelle, aus der die Daten stammen (bei acExport) oder in welche die Daten geschrieben (acImport) beziehungsweise mit der die Daten verknüpft werden sollen (acLink).
• FileName: Name der Quell- beziehungsweise Zieldaten, je nach Import oder Export, inklusive Verzeichnis.
• HasFieldNames: Gibt an, ob die Quell-/Zieldatei Spaltenüberschriften in der ersten Zeile enthält oder diese beim Exportieren angelegt werden sollen.
• Range: Gibt beim Import den Bereich der Zellen an, die importiert werden sollen. Diesen geben Sie entweder durch die per Doppelpunkt verknüpfen Namen der Zellen links oben und unten rechts an (zum Beispiel A1:Z26) oder durch Angabe eines benannten Bereiches. Diese müssen Sie zuvor in der Excel-Tabelle definieren – dazu später mehr.

Beispiel für den Export nach Excel

Username or Email

Passwort

Angemeldet bleiben