Autor: Manfred Hoffbauer, Düsseldorf
Eine häufige Anforderung an die Datenbank Microsoft Access besteht darin, die gespeicherten Daten auch unter Excel zur Verfügung zu stellen. Excel eignet sich hervorragend für Auswertungen und Diagramme, die ad hoc am Bildschirm erstellt werden. Hier kann auch der Anwender ohne Programmierfahrung mit entsprechender Fachkenntnis schnell zu überzeugenden Ergebnissen kommen.
Export ganz schnell
Aber wie gelangen die Daten aus der Datenbank in ein Arbeitsblatt Die einfachste Variante bietet das Office-Symbol von Microsoft Access. Um die Daten einer Tabelle oder Abfrage an Excel zu übergeben, gehen Sie wie folgt vor:
Der weitere Ablauf erfolgt automatisch: Access exportiert die Daten in eine .xls-Datei und öffnet diese mit der Version von Microsoft Excel, die auf Ihrem PC als Standard für .xls-Dateien definiert ist.
Vor- und Nachteile des Office-Symbols
Der Vorteil dieser Vorgehensweise liegt auf der Hand: Die gesamte Abwicklung mit Excel übernimmt Access selbständig. Es ist also wirklich keine Programmierung erforderlich. Die Programmfunktion zum Exportieren nach Excel steht sofort nach der Installation von Microsoft Access zur Verfügung.
Bild 2: So sehen die Artikeldaten im Excel-Arbeitsblatt aus.
Für das Erstellen der Abbildungen wurde die Artikeltabelle der Nordwind-Datenbank nach Excel 2000 exportiert. Hier fällt ein weiterer Vorteil des Office-Symbols auf: Das entstehende Arbeitsblatt erhält automatisch auch einige Formatierungen, die beispielsweise die überschriftenzeile besonders hervorheben (siehe Bild 2).
Ein weiterer Vorteil des Office-Symbols führt gleichzeitig zu einem triftigen Nachteil: Access hält sich beim Excel-Export an die Programmfunktion von Excel 5.0 als kleinsten gemeinsamen Nenner. Das ist vermutlich der Grund dafür, dass generell nur Texte mit einer Länge von bis zu 255 Zeichen exportiert werden können. Insbesondere beim Export von Memofeldern tritt das Problem auf, das Excel darüber hinausgehende Texte einfach abschneidet.
Bestimmte Formatierungen von Zahlenwerten (z.B. Währungssymbole), Hyperlinks und Zeilenumbrüchen in Textfeldern gehen ebenfalls beim Export verloren oder werden fehlerhaft umgewandelt. So wandelt Excel einige Zahlen in Texte um und das führt dazu, dass Excel mit diesen Werten nicht rechnen kann.
Die genannten Probleme lassen sich allesamt lösen, erfordern jedoch ein wenig Programmieraufwand. Die folgenden Abschnitte beschreiben die erforderlichen Lösungsansätze.
Export automatisch
Wie bei Access nicht anders zu erwarten, können Sie die Programmfunktion zum Export nach Excel auch in VBA benutzen. Um dies an einem einfachen Beispiel nachvollziehen zu können, öffnen Sie die Nordwind-Datenbank oder die Beispieldatenbank ExportToExcel.mdb.
Abfrage entwerfen
Mit einer Abfrage stellen Sie die zu exportierenden Daten zusammen. Sie können entweder eine eigene Abfrage entwerfen oder die Abfrage qryBestellungenUndKunden aus der Beispieldatenbank zu diesem Beitrag verwenden (siehe Bild 3).
Fügen Sie der Abfrage alle Felder hinzu, die Sie exportieren möchten. Definieren Sie auch die Kriterien, mit denen Sie die zum Export vorgesehenen Datensätze selektieren. Um das VBA-Programm für den Export zu entwerfen, gehen Sie wie folgt vor:
Als Ergebnis dieser Schritte erhalten Sie eine Befehlsschaltfläche (siehe Bild 4). Das Bild der Schaltfläche gleicht dem des Office-Symbols für den Export nach Excel. Um die Schaltfläche zu programmieren, gehen Sie wie folgt vor:
DoCmd.OpenQuery _ "qryBestellungenUndKunden" DoCmd.RunCommand acCmdOutputToExcel
Mit den genannten Schritten haben Sie die Ereignisprozedur für das BeimKlicken-Ereignis der Schaltfläche vollständig definiert. In der Formularansicht können Sie das Ergebnis Ihrer Arbeit überprüfen und die Schaltfläche anklicken (siehe Bild 5). Access öffnet daraufhin das Datenblatt der Abfrage qryBestellungenUndKunden und exportiert die Daten nach Excel. Der Export hat die gleichen Vor- und Nachteile wie die Verwendung des Office-Symbols.
Export in eine Datei
Die beiden bisher beschriebenen Methoden führen zu dem gleichen Ergebnis: Access exportiert die Daten in eine Excel-Datei und öffnet diese mit Excel. Die entstehende Datei enthält auch einige Formatierungen, die automatisch übertragen werden.
Das Generieren der Formatierungen ist zwar komfortabel, benötigt aber Zeit. Wenn Sie große Datenmengen an Excel weitergeben wollen, dann sollten Sie stattdessen die TransferText- oder die TransferSpreadSheet-Methode verwenden. Beide Methoden stehen als VBA-Befehl und als Makroaktion zur Verfügung.
Export als .csv-Datei
Mit der TransferText-Methode können Sie die Daten beispielsweise in eine .csv-Datei exportieren. .csv-Dateien enthalten für jeden Datensatz eine eigene Textzeile.
Die Felder des Datensatzes sind durch Semikola getrennt. Textfelder sind durch doppelte Anführungszeichen eingeklammert.
Der folgende Befehl exportiert die Daten der Abfrage qryBestellungenUndKunden in die Textdatei Bestellungen.csv:
DoCmd.TransferText acExportDelim, "", _ "qryBestellungenUndKunden", _ "C:\bestellungen.txt", True, ""
Der Vorteil von .csv-Dateien besteht darin, dass Sie aus reinem Text bestehen. Sie können Sie also auch mit einem Editor wie Notepad öffnen und ansehen (siehe Bild 6).
Export als XLS-Datei
Für den Export in eine Datei mit dem Format von Excel-Dateien hält Access einen eigenen Befehl bereit. Die folgende VBA-Anweisung exportiert die Daten der Abfrage qryBestellungenUndKunden in die .xls-Datei Bestellungen.xls:
DoCmd.TransferSpreadsheet acExport, _ 8, "qryBestellungenUndKunden", _ "C:\Bestellungen.xls", True, ""
Der Vorteil dieser Methode besteht darin, dass Sie die .xls-Datei ohne Konvertierung direkt mit Excel öffnen können. Mit dem zweiten Parameter, der im Beispiel den Wert 8 hat, geben Sie übrigens die Excel-Version an, deren Dateiformat der Export verwenden soll. Die 8 steht für acSpreadsheetTypeExcel8, was dem Dateiformat von Excel 2000 entspricht.
Vor- und Nachteile von .csv- und.xls-Dateien
.csv- und .xls-Dateien sind aufgrund der guten Performance beim Erzeugen der Dateien insbesondere für den Austausch großer Datenmengen sehr gut geeignet. Da .csv-Dateien wirklich nur die reinen Daten (zwar als Text- und nicht als Binärdaten) enthalten, benötigen sie außerdem vergleichsweise wenig Speicherplatz.
Der größte Nachteil besteht darin, dass Sie keine Formatierungen an Excel übergeben können. So lassen sich beispielsweise Zahlenwerte nicht ohne weiteres auch direkt mit dem Währungssymbol in Excel formatieren. Die Tätigkeit des Formatierens muss der Anwender nach jedem Datenexport selbst vornehmen.
Export mit Automatisierung
Die optisch ansprechendste und gleichzeitig langsamste Methode zum Export von Daten nach Excel ist die Verwendung der Automatisierung (ehemals: OLE-Automation). Mit dieser Methode können Sie die Daten einer Tabelle oder Abfrage sozusagen zellenweise in ein Excel-Arbeitsblatt übertragen.
Da Sie bei der Automatisierung auf alle Excel-Methoden und -Eigenschaften zugreifen können, stehen Ihnen bei der Formatierung des Arbeitsblatts alle Möglichkeiten von Excel zur Verfügung. Aber auch der Nachteil dieser Methode soll an dieser Stelle nicht verschwiegen werden:
Um alle Möglichkeiten ausschöpfen zu können, müssen Sie praktisch jeden einzelnen Schritt mit VBA programmieren. Der Preis für die Vielfalt an Möglichkeiten besteht also in einem vergleichsweise hohen Programmieraufwand. Außerdem ist die OLE-Automation die langsamste Methode zur übertragung von Daten nach Excel.
Anlegen einer Befehlsschaltfläche
Als Grundlage für den Datenexport dient die Abfrage qryBestellungenUndKunden aus der Beispieldatenbank zu diesem Beitrag. Die Datenbank enthält das Formular frmBestellungenFiltern, mit dem sich Bestellungen filtern und als Liste anzeigen lassen. Das Formular enthält die Schaltfläche btnExportToExcel mit der Bitmap MS Excel als Symbol. Bei einem Klick auf die Schaltfläche führt Access die Prozedur aus Quellcode 1 aus.
Die Ereignisprozedur fragt mit Me.Filter den Filter des aktuellen Formulars ab und speichert ihn in der String-Variablen sFilter. Die folgende If-Anweisung prüft, ob überhaupt ein Filter definiert ist. Falls nein, fragt die MsgBox-Funktion, ob der Anwender alle Datensätze an Excel übergeben möchte. Wenn der Anwender diese Frage mit einem Klick auf die Schaltfläche Nein beantwortet, beendet die Exit Sub-Anweisung die Prozedur. Andernfalls speichert Access die folgende SQL-Anweisung als Datenquelle für den Datenexport:
SELECT * FROM qryBestellungenUndKunden
Wenn das Formular einen Filter hat, soll dieser auch für den Datenexport verwendet werden. In diesem Fall fügt Access den gesamten Ausdruck einfach als Where-Klausel an die Select-Anweisung an. Wenn der Anwender die Bestellungen beispielsweise nach Alfreds Futterkiste gefiltert hat, führt der Ausdruck
sSQL = "SELECT * FROM qryBestellungenUndKunden WHERE " & sFilter
zu folgendender Select-Anweisung:
SELECT * FROM qryBestellungenUndKunden WHERE [Kunden-Code] = "ANTON"
Private Sub btnExportToExcel_Click()
Dim sFilter As String
Dim sSQL As String
sFilter = Me.Filter
If Len(Nz(sFilter)) = 0 Then
If MsgBox("Wollen Sie alle Datensätze exportieren", vbYesNo, _ "Kein Filter definiert.") = vbNo Then
Exit Sub
Else
'' Datenquelle für alle Datensätze definieren
sSQL = "SELECT * FROM qryBestellungenUndKunden"
End If
Else
'' Datenquelle mit Filter definieren
sSQL = "SELECT * FROM qryBestellungenUndKunden WHERE " & sFilter
End If
Ob nun mit oder ohne Filter: Die Ereignisprozedur speichert die SQL-Anweisung in der String-Variablen sSQL. Für den weiteren Ablauf ist aber eine Abfrage als Datenquelle für den Export erforderlich.
Aus diesem Grund weisen die folgenden Anweisungen die SELECT-Anweisung der SQL-Eigenschaft eines QueryDefs-Objekts zu. Da das QueryDefs-Objekt nicht jedes Mal neu generiert wird, muss es vor Aufruf der Prozedur bereits in der aktuellen Datenbank enthalten sein. Dabei genügt es allerdings, irgendeine Auswahlabfrage als Rahmen unter dem Namen qryExportToExcel zu speichern.
Dim dbs As Database
Set dbs = CurrentDb
dbs.QueryDefs("qryExportToExcel").SQL _ = sSQL
dbs.QueryDefs("qryExportToExcel").Close
ExportQueryToExcel
Den Abschluss der Ereignisprozedur bildet der Aufruf der Prozedur ExportQueryToExcel. Diese Prozedur ist im Modul modExcelTransfer der Beispieldatenbank enthalten und führt den Export der Daten nach Excel durch.
Die erste Aufgabe der Prozedur ExportQueryToExcel besteht darin, einen Namen für die neue Excel-Datei zu ermitteln. Dies geschieht durch einen Aufruf der Funktion ExcelDateiNameErmitteln( ), die einen Dateinamen inklusive Pfad als Funktionsparameter zurückgibt. Aber nach welchen Regeln wird der neue Name gebildet Die Funktion ermittelt zunächst mit GetDBPath( ) den Pfad des aktuellen Projektes. Der Pfad wird um den Dateinamen ergänzt, der sich aus dem Text Bestellungen, dem aktuellen Tagesdatum im Format JJJJMMTT, einer dreistelligen fortlaufenden Nummer und der Erweiterung .xls zusammensetzt.. Das Listing aus Quellcode 2 zeigt die Funktion im Detail.
Public Function ExcelDateiNameErmitteln() As String
Dim sDateiname As String
sDateiname = GetDBPath() & "\Bestellungen" & Format(Date, "yyyymmdd")
Dim sTestname As String
Dim i As Integer
i = 0
Dim lDateiVorhanden As Boolean
lDateiVorhanden = True
'' Ende, wenn bereits 1.000 Dateien an diesen angelegt wurden.
While lDateiVorhanden And i <= 999
sTestname = sDateiname & Format(i, "000") & ".xls"
If Not FileExists(sTestname) Then
lDateiVorhanden = False
sDateiname = sTestname
End If
i = i + 1
Wend
If Not lDateiVorhanden Then
ExcelDateiNameErmitteln = sDateiname
End If
End Function
Quellcode 2
Damit keine Datei unbeabsichtigt überschrieben wird, überprüft die Funktion auch die vorhandenen Dateien. Wenn sich die Beispieldatenbank im Verzeichnis C:\ExportToExcel befindet, liefert die Funktion beim ersten Aufruf den Dateinamen
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