Home > Artikel > Ausgabe 3/2018 > Datenaustausch mit Excel per VBA

Datenaustausch mit Excel per VBA

Achtung: Sie sind nicht angemeldet. Wenn Sie Abonnent sind und sich anmelden, lesen Sie den kompletten Artikel, laden das PDF herunter oder probieren die Beispieldatenbank aus (sofern vorhanden).

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:

Auswahl der Befehle zum Importieren aus verschiedenen Quellen

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.

Assistent zum Importieren oder Verknüpfen von Excel-Daten

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?

Fehlerhafter Aufruf des Export-Assistenten

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:

Die Export-Befehle sind deaktiviert.

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).

Die Transfer...-Methoden des DoCmd-Objekts

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:

Die Parameterliste der TransferSpreadsheet-Methode

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

Für ein Beispiel des Exports nach Excel haben wir eine Abfrage namens qryArtikelMitKategorieUndLieferant vorbereitet, welche die Daten der Tabellen tblArtikel, tblKategorien und tblLieferanten zusammenfasst (siehe Bild 7). Diese wollen wir nun nach Excel exportieren.

Abfrage mit Artikeln, Kategorie- und Lieferantenbezeichnungen

Bild 7: Abfrage mit Artikeln, Kategorie- und Lieferantenbezeichnungen

Damit diese in einer neuen Excel-Datei im gleichen Verzeichnis wie die Datenbank landet, rufen wir den folgenden Befehl auf:

DoCmd.TransferSpreadsheet acExport, _

acSpreadsheetTypeExcel12Xml, _

"qryArtikelMitKategorieUndLieferant", _

CurrentProject.Path & _

"\qryArtikelMitKategorieUndLieferant.xlsx", True

Hier nutzen wir das mit Excel 2010 eingeführte XML-basierte Format .xlsx. Mit dem letzten Parameter geben wir an, dass die Feldnamen der Tabelle als Spaltenüberschriften in der Excel-Datei angelegt werden sollen. Das Ergebnis sieht wie in Bild 8 aus – also durchaus erwartungsgemäß.

Ergebnis des Exports in eine Excel-Datei

Bild 8: Ergebnis des Exports in eine Excel-Datei

Import einer Excel-Tabelle

Nun wollen wir die exportierten Daten wieder in eine Tabelle importieren. Dazu verwenden wir als ersten Parameter acImport statt acExport. Davon abgesehen wollen wir nur den Namen der Zieltabelle ändern, und zwar in tblArtikelMitKategorieUndLieferant. Die vollständige Anweisung sieht nun also wie folgt aus:

DoCmd.TransferSpreadsheet acImport, _

acSpreadsheetTypeExcel12Xml, _

"tblArtikelMitKategorieUndLieferant", _

CurrentProject.Path & _

"\qryArtikelMitKategorieUndLieferant.xlsx", True

Die Datenblattansicht der importierten Tabelle zeigt die Daten genauso an wie die Excel-Tabelle, bis auf die Werte Ja und Nein statt der Werte WAHR und FALSCH für das ursprüngliche Ja/Nein-Feld.

Hier scheint in Excel also an irgendeiner Stelle auch der Datentyp gespeichert worden zu sein.

Wenn wir dann in die Entwurfsansicht der beim Import entstandenen Tabelle schauen, finden wir dort auch tatsächlich den Datentyp Ja/Nein vor (siehe Bild 9). Außerdem sehen wir hier, dass beim Import kein Primärschlüssel für das Feld ArtikelID definiert wurde, was aber auch nicht zu erwarten war.

Entwurf der importierten Tabelle

Sie haben das Ende des frei verfügbaren Teil dieses Artikels erreicht!

Wenn Sie mehr lesen und auf viele weitere Artikel zugreifen möchten, melden Sie sich als Abonnent unter Login an. Falls nicht, bestellen Sie doch einfach ein Jahresabonnement!