Excel als Report-Generator? Das hört sich doch nach Arbeit an! Zugegeben, ich mag die oft angepriesene Interaktivität zwischen den Office- Produkten nicht so gerne anwenden. Für die meisten Anforderungen bringt Access alles mit, was man benötigt, außer man muss Graphen und Pivot-Tabellen nutzen. Und in Zeiten, in denen man mindestens vier verschiedene Office- Versionen beim Kunden vorfinden könnte, ist mir der Ärger mit Inkompatibilitäten und Unterschieden zwischen den Versionen meist zu groß. Allerdings haben wir auch Zeiten, wo Software nicht mehr all zuviel kosten darf, um die Gunst der Kunden zu erlangen. Es gibt einfach zu viele Mitbewerber. Flexibilität heißt das Zauberwort, jedenfalls meiner Meinung nach.
Der Access-Report-Generator ist schon ein recht mächtiges und vor allen einfach zu bedienendes Werkzeug. Aber was nützt einem das, wenn man seine Anwendung als MDE vertreibt, damit niemand den Quellcode sieht? Änderungenam Entwurf sind dann nicht mehr möglich. Das Layout einer Rechnung ist in diesen Fällen schon ziemlich stark auf die Vorgaben des Entwicklers beschränkt. Anpassungen sind zeitaufwendig, und somit teuer, und kollidieren nicht selten mit der Updatefähigkeit einer Anwendung.Für solche Fälle habe ich Excel als Report-Generator entdeckt. Mit Excel kennen sich die meistenAnwender aus und arbeiten oft auch gerne damit. Was liegt also näher, als dem Kunden dasBerichtslayout zu überlassen? Das soll freilich nicht heißen, dass jeder Bericht zukünftig mit Excel ausgegeben werden soll, dafür fehlen hier elementare Funktionen. Aber kundenspezifische Formulare oder Berichte, die nahe an die Funktionalität von Tabellenkalkulationen kommen, sind hervorragende Kandidaten dafür. Fast nichts eignet sich besser für die Manipulation zur Ausgabe eines Graphen als Excel. Und Sie werden sich vielleicht wundern wie gering der Aufwand wirklich ist, gemessen an den Vorteilen.
Beispiel eines einfachen Berichts
Mit einem einfachen Bericht ist der Druck eines einzelnen Datensatzes gemeint, im vorliegenden Beispiel der Druck eines Preisschildes. In Bild 1 sieht man die stark vereinfachte Form eines Artikeldatensatzes. Wie die mögliche Ausgabe nach Excel aussehen kann, sieht man in Bild 2. Dabei habe ich die neusten Erkenntnisse der Marketing-Wissenschaften über das beste Layout eines Preisschildes außer Acht gelassen. Es soll ja nur ein Beispiel sein.
Ohne Metadaten läuft bei mir nichts
Wer meine Artikel kennt, weiß inzwischen, dass Ich Metadaten liebe. Auch in diesem Fall ist es sinnvoll, Profil-Tabellen anzulegen, die es ermöglichen, dieses Beispiel in Windeseile anzupassen beziehungsweise zu erweitern. In den Metadatentabellen soll die Art und Weise, wie und wohin Daten nach Excel exportiert werden, beschrieben werden.
In Bild 3 sieht man das Ergebnis dieser Profil- Tabelle mit dem Namen USys_FUR_Profile. Die Feldnamen und ihre wichtigsten Eigenschaften sind in der Fotomontage rechts unten eingeblendet. In diesem Beispiel werden nicht alle dort angegebenen Felder benötigt, aber ich mag es nun mal ausführlich. Um ein Report-Profil zu beschreiben, wird unbedingt der Key benötigt.Dieses ist somit der Name des Profils, worüber das Profil später aufgerufen wird. Der Profilname und die Sortierung sind zum Beispiel nützlich, um die Profile in einer Auswahlliste anzuzeigen. Sehr wichtig ist die Information über den Pfad und den Namen der Excel-Datei, in welcher die Daten ausgegeben werden sollen. Die Angabe der Tabelle, aus der der Datensatz stammt, hat bei diesem Beispiel nur informativen Charakter.
Mit der Spalte aktiviert kann man Profile kurzfristig deaktivieren, ohne dabei den Datensatz – etwa zu Testzwecken – löschen oder verstümmeln zu müssen.
Feldnamen angeben
Die Tabelle USys_FUR_Field aus Bild 4 beinhaltet die 1:n-verknüpften Details zu einem Profil. In dieser Tabelle ist, neben der Verknüpfung zum Hauptdatensatz, vor allem die Angabe des Feldnamens wichtig. Diese Feldnamen müssen später beim Export nach Excel in einer Abfrage beziehungsweise einem zu exportierenden Recordset verwendet werden. Ferner muss definiert werden, wohin die Daten dieses Feldes exportiert werden. Neben der Angabe des Zielarbeitsblatts sind die Koordinaten der Zielzelle in der Excel- Datei wichtig.
Bei umfangreichen Excel-Reports ist es an dieser Stelle sinnvoll, über die Verwendung von Namen in Excel nachzudenken, da es schon recht mühsam sein kann, alle Metadaten anzupassen, wenn man in der Excel-Arbeitsmappe eine Spalte oder Zeile zwischendrin einfügt.
Über das Design der Metatabellen und die Aufteilung der Informationen darin kann man natürlich unterschiedlicher Auffassung sein. Zum Beispiel könnte der Pfad und der Dateiname auch in der Tabelle USys_FUR_Field abgelegt werden. Somit könnte ein Profil gleich in mehrere unterschiedliche Excel-Dateien Daten exportieren.
Namensgebung der Objekte
Objekte mit dem Präfix USys werden von Access als benutzerdefinierte Systemtabellen behandelt. Sie sind das Pendant zu MSys-Tabellen, den Access-eigenen Systemtabellen. Systemtabellen haben die Eigenschaft, unsichtbar zu sein, es sei denn, man stellt unter Extras/Optionen/Ansicht ein, dass sie sichtbar sein sollen. FUR steht in meiner Analogie für Flexible User Reports, weil dieser Artikel aus einer Lösung extrahiert wurde, die es dem Anwender erlaubt, eigene Berichte mit Excel zu entwerfen und mit Daten aus der Anwendung zu füllen.
Anmerkungen zum Code
Der Code zum Druck eines einfachen Berichts ist in Listing 1 abgedruckt. Die Verwendung dieser Funktion beziehungsweise dessen Aufruf zeigt Listing 2. Die Hauptfunktion erwartet drei Parameter:
'# HEADER: ************************************************
'- Funktion ____ FUR_PrintOut
'- Kurzinfo ____ Flexible Berichte mit Excel drucken
'- Autor _______ Michael W. Welling - TECTUM SoftwareDESIGN
'- Erstellung __ Version 001 - 12.12.2005 00:00:00
'**********************************************************
Public _
Function FUR_PrintOut( _
ByVal DataSource As DAO.Recordset, _
ByVal ProfileKey As String, _
Optional ByVal PrintDirectly As Boolean = False) As Long
On Error GoTo RunError
Dim sql As String
'**********************************************************
'* Plausibilitätskontrollen
If DataSource Is Nothing = True Then Exit Function
If DataSource.RecordCount = 0 Then Exit Function
'*******************************************************
'* aktuelle Datenbank referenzieren
Dim dbs As DAO.Database
Set dbs = CurrentDb()
'*******************************************************
'* Datei-Pfad und -Namen aus den Profildaten ermitteln
'* Auswahl-Abfrage generieren
sql = ""
sql = sql & "SELECT"
sql = sql & " *"
sql = sql & " FROM"
sql = sql & " [USys_FUR_Profile]"
sql = sql & " WHERE"
sql = sql & " [ProfileKey]='" & ProfileKey & "'"
sql = sql & " AND"
sql = sql & " [activated]=-1"
sql = sql & ";"
'*******************************************************
'* wurden Profildaten gefunden?
Dim mta As DAO.Recordset
Set mta = dbs.OpenRecordset(sql, dbOpenDynaset)
If mta.RecordCount = 0 Then
mta.Close
dbs.Close
FUR_PrintOut = 1
GoTo RunError
End If
'*******************************************************
'* Dateiname der Berichts-Datei ermitteln
Dim ReportFile As String
ReportFile = mta![ReportFile]
If ReportFile & "" = "" Then
FUR_PrintOut = 3
GoTo RunError
End If
'* relativen Pfad der Berichts-Datei ermitteln
Dim ReportPath As String
ReportPath = mta![ReportPath]
'* Tabelle schliessen
mta.Close
'*******************************************************
'* aktuellen Pfad der Datenbank ermitteln
Dim seperator As String
seperator = "\"
Dim pos As Long
pos = 0
Dim DBPath As String
DBPath = dbs.Name
Dim CurPath As String
pos = InStr(1, DBPath, seperator)
While pos > 0
CurPath = Left(DBPath, pos - 1)
pos = InStr(pos + 1, DBPath, seperator)
Wend
'*******************************************************
'* Komplette Pfadangabe zusammenbauen
'* (Netzwerk-Pfade berücksichtigen)
Dim FileName As String
FileName = ""
If Left(ReportPath, 2) = "\\" Then
FileName = FileName & ReportPath
ElseIf Left(ReportPath, 1) = "\" Then
FileName = FileName & CurPath
FileName = FileName & ReportPath
Else
FileName = FileName & ReportPath
End If
FileName = FileName & "\"
FileName = FileName & ReportFile
'* Instanz von Excel erstellen
Dim off_exc As Object
Set off_exc = CreateObject("Excel.Application")
'* xls-Datei öffnen
Dim exc_wkb As Object
Set exc_wkb = off_exc.Workbooks.Open( _
FileName:=FileName, _
ReadOnly:=False, _
Editable:=True)
'*******************************************************
'* Meta-Daten ermitteln
sql = ""
sql = sql & "SELECT"
sql = sql & " *"
sql = sql & " FROM"
sql = sql & " [USys_FUR_Field]"
sql = sql & " WHERE"
sql = sql & " [ProfileKey]='" & ProfileKey & "'"
sql = sql & " AND"
sql = sql & " [activated]=-1"
sql = sql & ";"
Set mta = Nothing
Set mta = dbs.OpenRecordset(sql, dbOpenDynaset)
If mta.RecordCount = 0 Then
mta.Close
dbs.Close
End If
'**********************************************************
'* Worksheet-Objekt-Variable definieren
Dim exc_wks As Object
'*******************************************************
'* Daten aus Tabelle in Report übertragen
Dim Sheet As String
Dim Row As Long
Dim col As Long
'* Für jeden Datensatz durchschleifen, ...
DataSource.MoveFirst
While Not DataSource.EOF
'* Für jedes Feld in Metadaten durchschleifen, ...
mta.MoveFirst
While Not mta.EOF
'* Feld-Name ermitteln
Dim FieldName As String
FieldName = mta![FieldName]
'* Zeile und Spalte müssen angegeben sein, ...
If (mta![Row] & "" <> "") And _
(mta![Column] & "" <> "") Then
'* Zeile ermitteln
Row = mta![Row]
'* Spalte ermitteln
col = mta![Column]
'* Arbeitsblatt ermitteln
Sheet = mta![Worksheet]
'* Seite im Excel-Sheet referenzieren
Set exc_wks = exc_wkb.Worksheets(Sheet)
'* Datum in Zelle einfügen
exc_wks.Cells(Row, col).Value = _
DataSource(FieldName)
End If
'* nächstes Feld
mta.MoveNext
Wend
'* nächsten Datensatz
Fortsetzung Listing 1
DataSource.MoveNext
Wend
'**********************************************************
'* Drucken oder Vorschau
Select Case PrintDirectly
Case True
exc_wks.PrintOut
exc_wkb.Close (False)
Case False
off_exc.Visible = True
exc_wks.Activate
End Select
RunError:
Select Case Err.Number
Case 0
Case 1004 '* Aktion abgebrochen
MsgBox "Zeile: " & Row & ", Spalte: " & col
Resume Next
Case Else
MsgBox Err.Description, _
vbCritical, _
"Nr. " & Err.Number
End Select
RunTerminate:
'* Excel wieder schlissen
Select Case PrintDirectly
Case True
If off_exc Is Nothing = False Then
off_exc.Quit
End If
Case False
End Select
'* Excel-Objekt-Variablen terminieren
Set exc_wks = Nothing
Set exc_wkb = Nothing
Set off_exc = Nothing
'* DAO-Objekt-Variablen terminieren
Set DataSource = Nothing
Set mta = Nothing
Set dbs = Nothing
End Function
Ausdruck eines Preisschildes
Private _
Function PrintPreisschild( _
Optional ByVal PrintDirectly As Boolean = False) _
As Long
On Error GoTo RunError
'* Wenn kein Datensatz aktiv, dann Abbruch
If Me![ArtikelIndex] & "" = "" Then Exit Function
'* Auswahl-Abfrage generieren
Dim sql As String
sql = ""
sql = sql & "SELECT"
sql = sql & " *"
sql = sql & " FROM"
sql = sql & " [tbl_Artikel]"
sql = sql & " WHERE"
sql = sql & " [ArtikelIndex]=" & Me![ArtikelIndex]
Dim dbs As DAO.Database
Set dbs = CurrentDb()
Dim rst As DAO.Recordset
Set rst = dbs.OpenRecordset(sql, dbOpenSnapshot)
'* Bericht drucken, Profil "ps" anwenden
Dim rtn As Long
rtn = FUR_PrintOut(rst, "ps", PrintDirectly)
rst.Close
dbs.Close
RunError:
Select Case Err.Number
Case 0
Case Else
MsgBox Err.Description, _
vbCritical, _
"Nr. " & Err.Number
End Select
RunTerminate:
Set rst = Nothing
Set dbs = Nothing
End Function
-
das DAO-Recordset mit dem Datensatz, der gedruckt werden soll,
-
den Namen des Profils aus der Metadaten-Tabelle, nach dessen Schema die Ausgabe erfolgen soll, sowie
-
die Information, ob man den Bericht direkt drucken oder als Vorschau angezeigt bekommen möchte.
Der Großteil der Funktion befasst sich damit, den Pfad zur Excel-Datei zu ermitteln und die Bestandteile zusammenzubauen. Was nützt einem die schönste Lösung, wenn man auf jedem Zielrechner erst mal die Pfade anpassen muss? Deshalb kann diese Lösung mit relativen Pfaden umgehen. Das ist auch der Grund, warum in der Spalte Dateipfad der Tabelle USys_FUR_Profile ein \-Zeichen (Backslash) steht. Es signalisiert, dass es sich bei der Angabe um einen Pfad relativ zur ausgeführten Access-Anwendung handelt. Dieser kann natürlich auch erweitert werden, zum Beispiel so: \reports. Alle anderen Angaben, die nicht mit \ beginnen, werden als absolute Pfadangaben interpretiert.
Anschließend wird die Metadaten-Tabelle mit den Details referenziert und geöffnet, eine Excel- Instanz erstellt, die Excel-Datei geladen, und schließlich werden die Daten anhand des Schemas in den Zellen der Arbeitsblätter platziert. Als Letztes wird, je nach Parameterangabe, das Arbeitsblatt gedruckt und die Excel-Instanz wieder geschlossen oder zwecks Druckvorschau sichtbar gemacht.
Wie geht es weiter?
Im zweiten Teil der Artikelfolge widmen wir uns etwas komplexeren Berichten, zum Beispiel einer Liste oder gar einer Kombination aus beiden, zum Beispiel einer Rechnung. Vorweg gesagt: Die Realisierung von Unterberichten zu Detail-Datensätzen gestaltet sich mit Excel etwas schwierig und ist nicht Bestandteil des zweiten Teils.