Export nach Excel

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:

  • Aktivieren Sie das Datenbankfenster.
  • Markieren Sie die Tabelle oder Abfrage, deren Daten Sie exportieren wollen.
  • Klicken Sie in der Symbolleiste auf den nach unten zeigenden Pfeil neben dem Symbol Office-Verknüpfungen (siehe Bild 1)
  • Wählen Sie Analysieren mit MS Excel aus der Liste des Symbols.
  • 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:

  • öffnen Sie die Entwurfsansicht eines beliebigen Formulars.
  • Legen Sie auf dem Formular eine neue Befehlsschaltfläche an.
  • öffnen Sie das Eigenschaftenfenster für die Schaltfläche.
  • Klicken Sie auf das Editor-Symbol der Eigenschaft Bild.
  • Wählen Sie aus der sich öffnenden übersicht der Symbole den Eintrag Ausgabe an MS Excel.
  • 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:

  • Klicken Sie die Schaltfläche mit der rechten Maustaste an, und wählen Sie Ereignis aus dem Kontextmenü.
  • Wählen Sie den Eintrag Code-Generator aus der Liste der Generatoren.
  • Geben Sie die beiden folgenden Programmzeilen ein:
  • 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

    Schreibe einen Kommentar