In Unterformularen in der Datenblattansicht lassen sich Daten prima filtern oder sortieren. Mit der DoCmd-Methode TransferSpreadsheet lassen sich Daten einer Tabelle oder Abfrage einfach in eine Excel-Datei exportieren. Aber wie bekommen wir beides unter einen Hut? Wir möchten also in einem Unterformular die Daten filtern und sortieren können und diese in dieser Ansicht in eine Excel-Datei exportieren können. Dazu brauchen wir ein wenig VBA und Kenntnisse der Eigenschaften eines Formulars. In diesem Beitrag zeigen wir, wie wir die Daten der Datenherkunft des Unterformulars wie im Unterformular angegeben filtern und sortieren und so in eine Excel-Datei schreiben.
Beispieldatenbank
Wir verwenden ein einfaches Beispiel, in dem eine Tabelle namens tblBeispiele mit den beiden Feldern ID und Beispieltext die Daten liefert (siehe Bild 1).
Bild 1: Beispieltabelle mit einfachen Texten
Dazu legen wir zwei Formulare an. Das Unterformular sfmExportExcel verwendet die Tabelle tblBeispiele als Datensatzquelle und zeigt die beiden Felder ID und Beispieltext im Detailbereich an. Seine Eigenschaft Standardansicht stellen wir auf Datenblatt ein.
Dieses Unterformular fügen wir in das Hauptformular frmExportExcel ein. Außerdem legen wir im Hauptformular eine Schaltfläche namens cmdExport an.
Diese soll den Export der aktuell im Unterformular angezeigten Datensätze in eine Excel-Datei starten (siehe Bild 2).
Bild 2: Haupt- und Unterformular mit den Beispieldaten
Vorgehensweise für das Ermitteln von Filter und Sortierung und den Export
Unsere Lösung basiert darauf, dass wir für ein Unterformular, das der Benutzer gefiltert und/oder sortiert hat, den Filterausdruck als auch den Sortierausdruck aus entsprechenden Eigenschaften des Unterformulars auslesen können. Wir wissen auch, welche Datensatzquelle das Unterformular verwendet. Damit können wir folgendes tun:
- Filter auslesen
- Sortierung auslesen
- SQL-Ausdruck zusammenstellen, der per SELECT alle Datensätze der Tabelle liefert und für die Schlüsselwörter WHERE und ORDER BY die angewendeten Filter und Sortierungen einträgt
- Aus diesem SQL-Ausdruck eine Abfrage erstellen und speichern und diese Abfrage dann als Datenquelle für den Export mit DoCmd.TransferSpreadsheet verwenden
- Die Abfrage wieder löschen
Aktuellen Filter ermitteln
Die grobe, auf unser konkretes Beispiel ausgelegte Prozedur für diesen Zweck finden wir in Listing 1. Die Prozedur legt in der Variablen strExcel den Pfad der Zieldatei fest, die im aktuellen Verzeichnis unter dem Namen Beispiele.xlsx gespeichert werden soll.
Private Sub cmdExport_Click() Dim db As DAO.Database Dim qdf As QueryDef Dim strQuery As String Dim strSQL As String Dim strFilter As String Dim strExcel As String Dim strOrderBy As String strExcel = CurrentProject.Path & "\Beispiele.xlsx" On Error Resume Next Kill strExcel On Error GoTo 0 Set db = CurrentDb strQuery = "qryTemp" strSQL = "SELECT * FROM tblBeispiele" If Me!sfmExportExcel.Form.FilterOn = True Then strFilter = Me!sfmExportExcel.Form.Filter End If If Me!sfmExportExcel.Form.OrderByOn = True Then strOrderBy = Me!sfmExportExcel.Form.OrderBy# End If If Not Len(strFilter) = 0 Then strSQL = strSQL & " WHERE " & strFilter End If If Not Len(strOrderBy) = 0 Then strSQL = strSQL & " ORDER BY " & strOrderBy End If Set qdf = db.CreateQueryDef(strQuery, strSQL) DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strQuery, strExcel, True db.QueryDefs.Delete strQuery End Sub
Listing 1: Exportieren der aktuellen Daten des Unterformulars in eine Excel-Tabelle
Gegebenenfalls ist diese Datei bereits vorhanden. Für diesen Fall führen die Kill-Anweisung mit dem Pfad aus strExcel aus – dies bei deaktivierter Fehlerbehandlung, damit keine Fehlermeldung erscheint, wenn die Datei nicht vorhanden sein sollte und die Kill-Anweisung ins Leere läuft.
Dann referenziert die Prozedur das aktuelle Datenbankobjekt und legt in strQuery den Namen für die temporär zu erzeugende Abfrage fest, hier qryTemp.
Die grundlegende SQL-Anweisung legen wir als SELECT-Anweisung über alle Felder der Tabelle tblBeispiele fest. Dann prüft die Prozedur anhand der Eigenschaft FilterOn des Unterformulars, ob der Filter aktiviert ist. In diesem Fall trägt sie den aktuellen Filter in die Variable strFilter ein. Das Gleiche erledigen wir auch für die Sortierung, indem wir den Wert von OrderByOn prüfen und gegebenenfalls den Inhalt der Eigenschaft OrderBy in die Variable strOrderBy eintragen.
Hat strFilter einen Wert, hängen wir an die SELECT-Anweisung in strSQL eine entsprechende WHERE-Klausel an. Gleiches erledigen wir für die ORDER BY-Klausel, wenn strOrderBy keine leere Zeichenkette enthält.
Schließlich erstellen wir mit der CreateQueryDef-Methode des Database-Objekts eine neue Abfrage mit dem Namen aus strQuery und der SQL-Anweisung aus strSQL.
Damit können wir nun den eigentlichen Export starten. Dazu nutzen wir die Methode TransferSpreadsheet der DoCmd-Klasse. Diese nutzt für die beiden ersten Parameter die Werte acExport und acSpreadsheetTypeExcel12Xml, damit ein Export in eine .xlsx-Datei angestoßen wird. Der dritte Parameter enthält den Namen der Datenquelle, in diesem Fall der mit strQuery angegebenen Abfrage. Mit dem vierten Parameter geben wir die zu erstellende Datei an und mit dem fünften die Information, dass Spaltenüberschriften mit exportiert werden sollen. Schließlich löschen wir die Abfrage aus strQuery wieder.
Anschließend führen wir eine absteigende Sortierung nach dem Feld ID und eine Filterung nach dem Feld Beispieltext aus, der nur noch Werte liefert, die ein A enthalten. Dann öffnen wir die erstellte Excel-Datei und finden genau die Daten vor, wie wir sie im Unterformular definiert haben (siehe Bild 3).
Bild 3: Der Export mit Filter und Sortierung
Variable Gestaltung der Prozedur für den Export nach Excel
Damit haben wir eine recht spezifische Implementierung geschaffen. Diese werden wir nun ein wenig flexibler gestalten, sodass sich diese leicht auf andere Formulare übertragen lässt. Warum aber sollten wir das überhaupt tun?
- Weil wir sonst immer die Referenz zum Unterformular anpassen müssten.
- Weil wir die Tabelle, auf der die Datensatzherkunft des Unterformulars und somit der Excel-Export basiert, bisher fix angegeben haben. Auch diese wollen wir dynamisch ermitteln.
- Weil wir noch nicht berücksichtigen, dass die Datensatzquelle des Unterformulars selbst auch noch Filter und Sortierungen enthalten könnten.
Auswahl der Zieldatei
Dazu fügen wir zunächst ein Textfeld namens txtExportdatei hinzu sowie eine Schaltfläche namens cmdDateiauswahl. Diese statten wir für das Ereignis Beim Klicken mit der folgenden Prozedur aus:
Private Sub cmdDateiauswahl_Click() Dim objFiledialog As Office.FileDialog Set objFiledialog = _ Application.FileDialog(msoFileDialogSaveAs) With objFiledialog .Title = "Zieldatei festlegen" .InitialFileName = _ CurrentProject.Path & "\Export.xlsx" If .Show = True Then Me!txtExportdatei = .SelectedItems.Item(1) End If End With End Sub
Diese öffnet einen Dialog, mit dem wir die zu erstellende Datei festlegen können. Die gewählte Datei landet im Textfeld txtExportdatei.
Funktion zum Exportieren der Excel-Datei
Für maximale Flexibilität haben wir eine Funktion geschrieben, die Sie von einer Schaltfläche aus aufrufen können. Die Beispielschaltfläche cmdExportFlexibel nutzt dazu den folgenden Aufruf:
Private Sub cmdExportFlexibel_Click() ExportFlexibel Me!txtExportdatei, Me!sfmExportExcel.Form End Sub
Die Funktion ExportFlexibel aus Listing 2 verwendet diese Daten. Alles weitere ist tatsächlich flexibel – Sie können diese Funktion für alle weiteren Formulare mit Unterformular so nutzen.
Public Sub ExportFlexibel(strExcelpath As String, sfm As Form) Dim db As DAO.Database Dim qdf As QueryDef Dim strQuery As String Dim strSQL As String Dim strFilter As String Dim strOrderBy As String Dim strRecordsource As String On Error Resume Next Kill strExcelpath On Error GoTo 0 Set db = CurrentDb strQuery = "qryTemp" If Left(sfm.RecordSource, 6) = "SELECT" Then strSQL = sfm.RecordSource Else strSQL = "SELECT * FROM " & sfm.RecordSource End If If sfm.FilterOn = True Then strFilter = sfm.Filter End If If sfm.OrderByOn = True Then strOrderBy = sfm.OrderBy End If strSQL = AddWhereAndOrderByToSQL(strSQL, strFilter, strOrderBy) Set qdf = db.CreateQueryDef(strQuery, strSQL) DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strQuery, strExcelpath, True db.QueryDefs.Delete strQuery End Sub
Listing 2: Exportieren der aktuellen Daten des Unterformulars in eine Excel-Tabelle, flexible Variante
Auch diese Prozedur löscht zunächst eine eventuell bereits vorhandene Zieldatei. Wir verwenden weiterhin den Namen qryTemp für die temporäre Datei.
Der nächste Schritt unterscheidet sich bereits deutlich von der ersten Variante. Hier verwenden wir nämlich nicht einfach die Abfrage SELECT * FROM tblBeispiele als Datensatzherkunft für die zu erstellende Abfrage als Quelle für den Export, sondern untersuchen die Datensatzquelle des Unterformulars explizit.
Dazu prüfen wir, ob die ersten sechs Buchstaben der Datensatzquelle dem Ausdruck SELECT entsprechen. In diesem Fall enthält die Datensatzquelle gegebenenfalls selbst eine WHERE– oder ORDER BY-Klausel. Hier weisen wir den Inhalt von sfm.RecordSource direkt der Variablen strSQL zu:
If Left(sfm.RecordSource, 6) = "SELECT" Then strSQL = sfm.RecordSource
Anderenfalls erstellen wir eine SELECT-Anweisung auf Basis der angegebenen Tabelle oder Abfrage:
Else strSQL = "SELECT * FROM " & sfm.RecordSource End If
Dann tragen wir, sofern der Filter und die Sortierung aktiviert sind, die entsprechenden Ausdrücke aus den Eigenschaften Filter und OrderBy in die Variablen strFilter und strOrderBy ein.
SQL-Ausdruck zusammenstellen
Nun folgt eine größere Operation, nämlich die Untersuchung des Inhalts von strSQL auf eventuell bereits vorhandene WHERE– oder ORDER BY-Klauseln und das Einfügen der entsprechenden Klauseln aus dem Unterformular in diese Ausdrücke. Diesen Teil haben wir in die Funktion AddWhereAndOrderByToSQL ausgegliedert, der wir drei Informationen übergeben:
- den aktuellen SQL-Ausdruck, also die gegebenenfalls um SELECT * FROM erweiterte Datensatzquelle des Unterformulars,
- den Filterausdruck aus strFilter und
- den Sortierausdruck aus strOrderBy.
Das Ergebnis dieser Funktion, die wir weiter unten beschreiben, landet wieder in der Variablen strSQL. Daraus erstellen wir wieder das QueryDef-Objekt, das wir als Datenquelle für den Aufruf von DoCmd.TransferSpreadsheet nutzen. Danach löschen wir wie zuvor noch die für temporäre Zwecke erstellte Abfrage.
Anreichern eines SQL-Ausdrucks um weitere WHERE- und ORDER BY-Ausdrücke
Warum brauchen wir eine eigene Funktion, um die für das Unterformular angegebenen Filter und Sortierungen hinzuzufügen, statt diese einfach anzuhängen?
Der Grund ist, dass wir als Datensatzquelle des Unterformulars durchaus einen Ausdruck wie den folgenden vorfinden können:
SELECT * FROM tblBeispiele WHERE Beispiel LIKE ''A*'' ORDER BY Beispiel DESC
Hier können wir die weiteren Filter und Sortierungen nicht einfach anhängen, sondern wir müssen diese gegebenenfalls einbauen und müssen auch noch darauf achten, dass Schlüsselwörter wie WHERE und ORDER BY nicht doppelt verwendet werden.
Also nutzen wir die Funktion AddWhereAndOrderToSQL aus Listing 3, um diese Aufgabe zu erledigen. Diese nimmt die oben erwähnten Parameter entgegen und schreibt den Wert von strSQL zunächst in die Variable strTemp.
Public Function AddWhereAndOrderByToSQL(strSQL As String, Optional strFilter As String, _ Optional strOrderBy As String) As String Dim strTemp As String strTemp = strSQL If Not Len(strFilter) = 0 Then If InStr(1, strTemp, "WHERE") = 0 Then If InStr(1, strTemp, "ORDER BY") = 0 Then strTemp = strTemp & " WHERE " & strFilter Else strTemp = Replace(strTemp, " ORDER BY ", " WHERE " & strFilter & " ORDER BY ") End If Else If InStr(1, strTemp, "ORDER BY") = 0 Then strTemp = strTemp & " AND " & strFilter Else strTemp = Replace(strTemp, " ORDER BY ", " AND " & strFilter & " ORDER BY ") End If End If End If If Not Len(strOrderBy) = 0 Then If InStr(1, strTemp, " ORDER BY ") = 0 Then strTemp = strTemp & " ORDER BY " & strOrderBy Else strTemp = strTemp & ", " & strOrderBy End If End If AddWhereAndOrderByToSQL = strTemp End Function
Listing 3: Funktion zum Zusammenstellen der Abfrage mit Filtern und Sortierungen
Dann prüft sie, ob mit dem Parameter strFilter ein Wert übergeben wurde. Falls ja, untersucht die Funktion, ob die aktuelle Version der Abfrage bereits das Schlüsselwort WHERE enthält. Ist das nicht der Fall, untersucht sie weiter, ob die Abfrage eine ORDER BY-Klausel enthält. Ist auch das nicht gegeben, können wir der Abfrage einfach das Schlüsselwort WHERE gefolgt vom Inhalt aus strFilter übergeben.
Falls noch keine WHERE-Klausel enthalten ist, aber eine ORDER BY-Klausel, dann müssen wir schon gezielter arbeiten: Die WHERE-Klausel mit dem Inhalt von strFilter müssen wir dann nämlich vor der ORDER BY-Klausel einfügen. Dazu ersetzen wir ORDER BY einfach mit der Replace-Funktion durch WHERE plus strFilter plus ORDER BY.
Für den Fall, dass bereits eine WHERE-Bedingung in der Abfrage aus strTemp enthalten ist, untersuchen wir auch wieder, ob auch eine ORDER BY-Klausel vorliegt. Ist das nicht der Fall, können wir die Bedingung aus strFilter ähnlich wie im ersten Fall hinten anhängen – diesmal allerdings nicht mit dem führenden Schlüsselwort WHERE, sondern mit AND. Das WHERE-Schlüsselwort ist ja bereits vorhanden und es darf auch nur einmal verwendet werden.
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