Access-Unterformulare: Filtern & gezielt nach Excel exportieren

2024, 3/2024, Interaktiv

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

Beispieltabelle mit einfachen Texten

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

Haupt- und Unterformular mit den Beispieldaten

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

Der Export mit Filter und Sortierung

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.

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