Einleitung
In meiner jüngsten Projektarbeit stand ich vor einer komplexen Herausforderung: dem Export einer Access-Tabelle nach Excel, wobei zusätzliche Berechnungen und eine flexible Spaltenauswahl erforderlich waren. Die Aufgabe erforderte einen Ansatz, der es ermöglicht, nur ausgewählte Spalten zu exportieren und diese Entscheidung durch eine Abfrage konfigurierbar zu machen.
Ausgangslage
Die Basis bildete eine einfache Tabelle in Access, genannt “Haupttabelle”, und eine Ansicht namens “auszugebende_Spalten”.
Ansatz 1: Festlegung der Spalten für den Export
Ermittlung der Spaltenanzahl
Um zu verstehen, welche Spalten exportiert werden sollen, gibt es zwei Methoden:
(a) Mittels RecordSet
| Public Function Columns_Count(strTable As String) As Integer | |
| Dim db As DAO.Database | |
| Dim rs As DAO.Recordset | |
| Dim iFieldCount As Integer | |
| Set db = CurrentDb | |
| Set rs = db.OpenRecordset("Select * from " & strTable, dbOpenSnapshot) | |
| iFieldCount = rs.Fields.Count | |
| Columns_Count = iFieldCount | |
| rs.Close | |
| db.Close | |
| End Function |
by (b) Mittels TableDefs/QueryDefs
| Public Function Columns_Count2(strTable As String) As Integer | |
| Dim db As DAO.Database | |
| Set db = CurrentDb | |
| Dim tdf As DAO.TableDef | |
| For Each tdf In db.TableDefs | |
| If tdf.Name = strTable Then | |
| Columns_Count2 = db.TableDefs(strTable).Fields.Count | |
| db.Close | |
| Exit Function | |
| End If | |
| Next | |
| Dim qdf As DAO.QueryDef | |
| For Each qdf In db.QueryDefs | |
| If qdf.Name = strTable Then | |
| Columns_Count2 = db.QueryDefs(strTable).Fields.Count | |
| db.Close | |
| Exit Function | |
| End If | |
| Next | |
| End Function |
Ermittlung der Spaltennamen
Ähnliche Methoden können zur Ermittlung der Spaltennamen verwendet werden:
Methode 1
| Public Function Read_Columns(strTable As String) As Collection | |
| Dim db As DAO.Database | |
| Dim rs As DAO.Recordset | |
| Dim Field As DAO.Field | |
| Dim myColl As Collection | |
| Set myColl = New Collection | |
| Set db = CurrentDb | |
| Set rs = db.OpenRecordset("Select * from " & strTable, dbOpenSnapshot) | |
| For Each Field In rs.Fields | |
| myColl.Add Field.Name | |
| Next | |
| rs.Close | |
| db.Close | |
| Set rs = Nothing | |
| Set db = Nothing | |
| Set Read_Columns = myColl | |
| Set myColl = Nothing | |
| End Function |
Methode 2
| Public Function Read_Columns2(strTable As String) As Collection | |
| ' Ähnlicher Code wie Methode 1, aber mit Prüfung auf Tabelle oder Abfrage | |
| ' und Sammeln der Spaltennamen | |
| End Function |
Export2Excel
Für den Export nach Excel habe ich eine Funktion entwickelt, die die ermittelten Spalten und Berechnungsergebnisse in eine Excel-Tabelle exportiert.
| Public Sub sExport2Excel1() | |
| ' (1) Ermitteln der auszugebenden Spalten und (2) Erstellen einer Excel Datei | |
| ' Code zum Erstellen der Excel-Tabelle und Setzen der Spaltenüberschriften | |
| ' (3) Auslesen des Recordsets & Übertragung ins Excel | |
| ' Code zum Durchlaufen des Recordsets und Übertragen der Daten nach Excel | |
| ' (4) Excel anzeigen | |
| appExcel.Visible = True | |
| End Sub |
Ansatz 2: Ansicht programmatisch um die benötigten Spalten erweitern
Um die für Berechnungen erforderlichen Spalten dynamisch hinzuzufügen, habe ich eine Funktion entwickelt, die eine bestehende Abfrage um zusätzliche Felder erweitert.
| Public Function AddFields2Query(strQuery As String, strQuellTabelle As String, Fields As Collection) As String | |
| ' Code zum Erweitern einer bestehenden Abfrage um zusätzliche Felder | |
| End Function |
Export2Excel mit erweiterter Abfrage
| Public Sub sExport2Excel2() | |
| ' (1) bis (5) Ermitteln der Spalten und Erstellen der erweiterten Abfrage | |
| ' Code zum Erstellen der Excel-Datei, Festlegen der Felder und Ermitteln der erweiterten Abfrage | |
| ' (6) Datenbank abfragen und (7) Excel anzeigen | |
| ' Code zum Durchlaufen der erweiterten Abfrage und Übertragen der Daten nach Excel | |
| End Sub |
Fazit
Die Entwicklung dieser Exportfunktionen zeigt die Flexibilität von VBA bei der Bearbeitung von Datenbankinhalten. Mit VBA können wir komplexe Anforderungen wie dynamische Spaltenauswahl und zusätzliche Berechnungen während des Exports nach Excel effizient umsetzen.
