version=2.0.0
vdate=05.02.2015
fname=xlsxexporter.cls
ns=%NAMESPACE%
fpath=/vba/access/
====== [VBA][Access] XlsxExporter 2.x ======
//Exporter MS Access nach Excel. Die komplexe Version.//
==Version %%version%% %%vdate%%==
>//Die Klasse hat versteckte Attribute. Damit diese aktiv übernommen werden reicht es nicht aus, den Code in ein neues Modul zu kopieren. Man muss das Modul aus der Datei nach VBA importieren.//
>{{popup>:vba:vba_importfile.png|Bild zum Import}}
{{:vba:access:xlsxexporter.cls|Download %%fname%% (V-%%version%%)}}
Eine kleine Klasse um schnell Daten aus Access nach Excel zu exportieren. Sie bietet die Möglichkeit, dann gleich noch einige Excel-Formatierungen durchzuführen
Meine erste Version [[vba:access:classes:xlsxeporter_101]] ist zwar nicht schlecht, bietet mir aber zu wenig.
Darum hier die neue, bessere & komplexere Version 2.x
Um zu verstehen worum es geht und was die Klasse macht, empfehle ich zuerst einmal die [[#Beispiele]] anzuschauen.
===== Referenzen =====
**Im VBA musss eine Referenz auf '%%MicrosoftExcel XY ObjectLibrary%%' gesetzt sein**
===== Grober ablauf =====
In der folgenden Reihenfolge sollten die versch. Schritte aufgerufen werden. //Kursiv// geschrieben Punkte sind optional.
> Bei einer anderen Reihenfolge kann es zu unerwarteten Resultaten führen.
- Objekt erstellen/initialisieren
- Instanzieren mit [[#initialize]] oder [[#instance]]
- //Paramters setzen, falls man das nicht bereits beim Instanzieren gemacht hat [[#Enumerator xeParams]]//
- //Formate definieren://
- //Standardformat anpassen [[#defaultFormat]]//
- //Standardformat auf alle vorfinierten Formate anpassen [[#requeryDefaultFormats]]//
- //Dateitypenformate anpassen [[#format]]//
- //Weitere Formate definieren [[#format]]//
- Exportiere die Daten [[#export]]
- //Formatierungen anwenden;//
- //Standartformat über das ganze Sheet [[#doFormatDefault]]//
- //Spalten nach Datentyp [[#doFormatColumnsByType]]//
- //Speziele andere Formate [[#doFormat]]//
- //Spezielle Anpassungen an Ranges [[#range]]//
- Objekt abbauen ([[#quit]] oder zerstören des Objektes)
===== Definitionen =====
Wird das Objekt terminiert, wird automatisch die Methode quit() ausgeführt. Die Änderungen werden dabei gespeichert und die internen Objekte (Excel-Instance,Workbook etc.) geschlossen und abgebaut.
Die meisten Methoden und Properties sind im Code sauber beschrieben. Wie man sie anwendet sieht man in den Beispielen.
Die Formate sind einfache Excel-Ranges. Alle Formateigenschaften die ein Range haben kann, kann zugeordnet werden.
Beim terminate() der Klasse wird das Excelsheet gespeichert und die Excelinstanz geschlossen. Darum kann man das ganze in einem With-Plock handeln
==== Enumerator xeParams ====
Die folgenden Parameter exisitieren und können kombiniert als xeParams beim Initialisieren übergeben werden.
Alle Parameter können auch einzeln als Objektproperty angesteuert werden
***xeNone** Kein Parameter gesetzt
== Datei-Handler ==
***xeReplaceExistFile** Falls die Exportdatei bereits exisitert, kann diese ersetzt werden
== Header-Settings ==
***xeCaptionAsHeader** Es wird versucht die Titel-Eigenschaft des Tabellenfeldes auszulesen
***xeReadableHeader** Der Technische Name wird möglichst in ein lesbaeren Namen Konvertiert
***xeTechnicalHeader** Der Technische Name (Feld-Name) wird für den Header verwendet
== Formatierung ==
***xeRunDefaultFormating** Nach dem Export wird das Default-Formating angewendet
== WeitereSettings ==
***xeNotRemoveFormatSheet** Das Sheet mit den Formatierungen wird am Schluss nicht entfernt
== Standard, wenn nichts angegeben wird ==
***xeDefaultParams** = xeReplaceExistFile + xeTechnicalHeader + xeRunDefaultFormating
Hier ein Beispiel, auf welche Arten man die Paramter setzen kann
Dim exp As XlsxExporter
Set exp = XlsxExporter("v_data", "C:\temp\_v_data.xlsx", , xeReadableHeader + xeReplaceExistFile)
'entspircht
Set exp = New XlsxExporter
exp.initialize "v_data", "C:\temp\_v_data.xlsx", , xeReadableHeader + xeReplaceExistFile
'entspircht
Set exp = XlsxExporter("v_data", "C:\temp\_v_data.xlsx", , xeNone)
exp.paramReadableHeader = True
exp.paramReplaceExistFile = True
==== Wichtige Methoden ====
=== initialize() ===
Initializiert eine Klasseninstanz mit den wichtigsten Paramtern
'/**
' * Initialisiert die Klasse mit allen wichtigen Paramtern
' * @param String Quelle
' * @param String Export-Pfad
' * @param AcSpreadSheetType Excel-Format
' * @param iParams Parameters
' */
Public Sub initialize( _
Optional ByVal iSource As String, _
Optional ByVal iFilePath As String, _
Optional ByVal iSpreadSheetType As AcSpreadSheetType = acSpreadsheetTypeExcel12Xml, _
Optional ByVal iParams As xeParams = xeDefaultParams _
)
Beispiel:
Dim exp As New XlsxExporter
exp.initialize "source", "target.xlsx"
=== instance() ===
Erstellt und initialisiert ein Objekt dieser Klasse. Diese Methode hat das Attribut ''Attribute instance.VB_UserMemId = 0''. Das bedeutet, wenn an die Klasse ohne Methode initialisiert, wird diese MEthode automatisch aufgerufen
'/**
' * Exportiert die Quelle in eine Excel-Datei und gibt eine Instance dieser Klasse zurück
' * @param String Quelle
' * @param String Export-Pfad
' * @param AcSpreadSheetType Excel-Format
' * @param iParams Parameters
' */
Public Function instance( _
Optional ByVal iSource As String, _
Optional ByVal iFilePath As String, _
Optional ByVal iSpreadSheetType As AcSpreadSheetType = acSpreadsheetTypeExcel12Xml, _
Optional ByVal iParams As xeParams = xeDefaultParams _
) As XlsxExporter
Beispiel:
Dim exp As XlsxExporter
Set exp = XlsxExporter.instance("source", "target.xlsx")
'oder dank Attribute instance.VB_UserMemId = 0
Set exp = XlsxExporter("source", "target.xlsx")
=== export() ===
Diese Methode exportiert die Daten in das Excel-Sheet. wenn die Parameter bereits in einer der vorherigen Methoden aufgerufen wurde, können sie hier weggelassen werden.
>Bei aktivem ''paramRunDefaultFormating'' wird ''doFormatColumnsByType()'' automatisch ausgeführt
'/**
' * Exportiert die Quelle in eine Excel-Datei und führt ggf das Standardformting aus
' * @param String Quelle
' * @param String Export-Pfad
' */
Public Sub export( _
Optional ByVal iSource As String, _
Optional ByVal iFilePath As String _
)
=== quit() ===
Wendet bei aktivem ''paramRunDefaultFormating'' und vorhandenem Header die Headerformatierung an, speichert und schliesst die Datei.
>Diese Funktion wird bei ''Class_Terminate()'' automatisch aufgerufen.
'/**
' * Seichert und schliesst die Datei
' * Diese Methode wird beim Abbauen des Objektes ebenfalls ausgeführt
' * @param Boolean Flag ob gespeichert werden soll. True = Cancel = Kein Speichern
' */
Public Sub quit( _
Optional ByVal iCancel As Boolean = False _
)
=== doFormat() ===
Führt eine Formatierung anhand einer Formatvorlage durch.
* Die Quelle ist entweder ein ''format'' oder eine Formatdefinition analog zum Property ''format''
* Die Definition des Target entspricht dem Property ''range''.
* Es können mehrere Targets mitKomma getrennt mitgegeben werden
'/**
' * Formatiert Ranges
' * @param Variant Ein Range/Formatname der als Formatvorlage dient. Meistens ein Range aus format()
' * @param Variant Mehrere Ziele. Die folgenden werden akzeptiert
' * NUMMER: Spaltennummer
' * STRING: Spalte mit dem Feldnamen iTarget oder Excel-Range-String ("A1:B13")
' * RANGE: Der Range wird entsprechend formatiert
' */
Public Sub doFormat( _
ByRef iFormat As Variant, _
ParamArray iTargets() As Variant _
)
exp.doFormat "mein_formatname", "1:1"
exp.doFormat .format("mein_formatname"), .range("1:1")
exp.doFormat "mein_formatname", "1:1", "A3", .myRange
=== doFormatDefault() ===
Wendet die ''defaultFormat'' auf das ganze Sheet an.
>Wird bei aktivem ''paramRunDefaultFormating'' am Ende der Funktion ''export()'' automatisch ausgeführt
'/**
' * Formatiert das ganze Sheet mit der Standartformatierung
' */
Public Sub doFormatDefault()
=== doFormatColumnsByType() ===
Formatiert Spalten Anhand ihres Feldtype der Quelle. Sie kann auf einzelne Spalten oder auf das ganez Sheet angewendet werden.
>Wird bei aktivem ''paramRunDefaultFormating'' am Ende der Funktion ''export()'' automatisch ausgeführt
'/**
' * formatiert Spalten anhand der Spaltentypen
' * @param Variant Range-Definition. Wenn diese weggelassen wird, werden alleSpalten nach Spaltentypen formatiert
' */
Public Sub doFormatColumnsByType( _
Optional ByRef iRangeDef As Variant = Null _
)
=== autoFit() ===
Passt die Spaltenbreiten den Daten an
>Wird bei ativem ''paramRunDefaultFormating'' im ''quit()'' automatisch ausgeführt
'/**
' passt die Spaltenbreite an. Wird bei paramRunDefaultFormating=true automatisch durchgeführt
' */
Public Sub autoFit()
=== requeryDefaultFormats() ===
Wenn man Anpassungen an dem ''defaultFormat'' vornimmt, kann man mit dieser Methode das neue Standardformat auf die restlichen Typenformate anwenden.
'/**
' * Berechnet die Standardformate neu, basierend auf dem defaultFormat
' */
Public Sub requeryDefaultFormats()
==== Interessante Properties ====
=== format ===
Das Format-Property gibt ein Excel.Range zurück. Diesen kann man dann nach belieben Formatieren und dient für die Methode ''doFormat()'' als Vorlage.
\\ Formate können über einen Namen oder einen %%DAO.DataTypeEnum%% definiert werden.
* Die Typenformate werden in der Methode ''doFormatColumnsByType()'' anhand des Feldtypes der Datenbank auf die Spalten angewendet.
* Es können auch freie Formate mit eigenen Namen verwaltet werden
* In der Internen Methode ''setDefaultFormats()'' sind einige Formate vordefiniert.
'/**
' * Ein Excel.Range,der als FOrmatvorlage dient
' * @param Variant Name oder dao.DataTypeEnum um das Format zu definieren
' * @param Variant Format/Range oder FormatName der als Formatvorlage dient
' * @param Boolean Bestehende Formateinstellungen verwerfen
' */
Public Property Get format( _
ByVal iNameOrType As Variant, _
Optional ByRef iPattern As Variant = C_DEFAULT_FORMAT_NAME, _
Optional ByVal iOverwrite As Boolean = False _
) As Excel.range
Beispiel
'Ausführlecher Weg ein benanntes Format zu erstellen & definieren
Dim frmt As Excel.Range
Set frmt = exp.format("Mein format")
frmt.font.bold = true
frmt.font.italic = true
'Vereinfachte Anwendung
exp.format("Mein format").font.bold = true
exp.format("Mein format").font.italic = true
=== defaultFormat ===
Direktzugriff auf die Formatvorlage für das Standardformat über das ganze Sheet.
>Diese Formatierungen sollte vor allen anderen Formatierungen angepasst werden, da sie die Grundlage für alle weiteren Formatierungen ist
>
>Nach Anpassungen dieses Formats unbedingt die Methode ''requeryDefaultFormats()'' aufrufen
'/**
' * Direktzugirff auf das Standardformat
' */
Public Property Get defaultFormat() As Excel.rangeexp.defaultFormat.font.size=10
exp.requeryDefaultFormats
=== headerFormat ===
Direkter Zugriff auf das Format des Headers
'/**
' * Direktzugirff auf die Headerformatierung
' */
Public Property Get headerFormat() As Excel.rangeexp.headerFormat.font.bold = true
=== range ===
Gibt einen Excel.Range des %%DataSheets%% zurück.
'/**
' * gibt ein Range des DataSheets zurück
' * @param Variant Spaltennummer/Spaltenname/Range/Cell2 & Cell2/Excel-Range-String ("A1", "A1:C3" etc)
' * @param Variant Spaltennummer/Spaltenname/Range/Cell2 & Cell2/Excel-Range-String ("A1", "A1:C3" etc)
' * @return Range
' */
Public Property Get range( _
ByRef iRangeDef1 As Variant, _
Optional ByRef iRangeDef2 As Variant = Null _
) As Excel.rangeDim rng As Excel.Range
Set rng = exp.range(1) 'Spalte 1
Set rng = exp.range("Mein Feldname") 'Spalte die dem Feld [Mein Feldname] der Quelle entspricht
Set rng = exp.range("A3") 'Zelle A3
Set rng = exp.range("1:1") 'Erste Zeile
Set rng = exp.range("A3:B4") 'Zellen A3 bis B4
Set rng = exp.range(myRange) 'Enspricht dem Range myRange
Set rng = exp.range("A3", "B4") 'Zellen A3 bis B4
Set rng = exp.Range(myRange, "Z:Z") 'Alles von myRange bis zur SpalteZ
----
===== Beispiele =====
==== Einfacher Export mit ====
=== Standardformatierung ===
Exportiert eine Tabelle mit Standardformatierung {{:vba:access:xlsxeporter:exportonly1.xlsx|}}
XlsxExporter("my_table", "c:\temp\_exportOnly1.xlsx").export
=== ohne Formatierung ===
Exportiert eine Tabelle ohne Standardformatierung {{:vba:access:xlsxeporter:exportonly2.xlsx|}}
XlsxExporter("my_table", "c:\temp\_exportOnly2.xlsx", , xeNone).export
==== Mit eigenen Formaten ====
Eigene Formate erstellen und diese auf den export anwenden. {{:vba:access:xlsxeporter:exportformated1.xlsx|}}
With XlsxExporter("my_table", "c:\temp\_exportFormated1.xlsx")
'Format definieren. Nur mit Namen
.format("BOLD").Font.Bold = True 'Neuer benanntes Format erstellen
'Format als eigene Variable
Dim frmtGood As Excel.range
Set frmtGood = .format("GOOD")
frmtGood.Style = "Good" 'Noch eines mit einem Excel-Style
frmtGood.Font.Bold = True 'und bei demselben auch noch Bold einstellen
'Datei exportieren
.export
'Formate anwednen
.doFormat "BOLD", "A:A" 'Bold auf die erste Spalte anwenden
.doFormat frmtGood, "B:B", .range("C2", "D4") 'Good auf Spalte B und auf den Range von C2 bis D4 anwednen
End With
==== Standardformatierung des Sheets überschreiben ====
Bei diesem Beispiel wird die Standardformatierung des Exporters berschrieben. Zudem werden die Parameter über Quelle und Ziel erst beim export() mitgegeben. {{:vba:access:xlsxeporter:exportformated2.xlsx|}}
With XlsxExporter() 'Achtung! Die Klammern müssen gesetzt sein, damit die Instanzierung sauber funktioniert
'property setzen.
'Per default ist xeReplaceExistFile + xeTechnicalHeader + xeRunDefaultFormating gesetzt
.paramCaptionAsHeader = True
'Standardformat anpassen
With .defaultFormat
.Font.size = 8
.Font.Name = "Courier New"
End With
'Standardformat auf alle typenformate anwenden
.requeryDefaultFormats
'exportieren. Die Quelle und das Zeil können auch beim export() metgegeben werden
.export "my_table", "c:\temp\_exportFormated2.xlsx"
End With
==== Weiteres Beispiel mit formaten ====
In diesem Beispiel wird dieFormatierung des Headers angepasst. Zudem greifen wir über einen Feldnamen der Access-Tabelle auf die Spalte im Excel zu. {{:vba:access:xlsxeporter:exportformated3.xlsx|}}
With XlsxExporter("my_table", "c:\temp\_exportFormated3.xlsx")
'property setzen.
'Per default ist xeReplaceExistFile + xeTechnicalHeader + xeRunDefaultFormating gesetzt
.paramCaptionAsHeader = True
'Typenformat definieren/ergänzen/überscheiben
'Die Typenformatierungen werden in der Methode export() bei
'aktivem Flag xeRunDefaultFormating automatisch vorgenommen
.format(dbDate).NumberFormat = "yyyy-mm-dd"
'Das Standard-Headerformat ändern
'Der Header wird beim schliessen und aktivem xeRunDefaultFormating formatiert
.headerFormat.Font.size = 12
.headerFormat.Font.Color = rgbGreen
.export
'"FX_RATES" ist ein vordefiniertes Format (siehe private Sub setDefaultFormats())
'"F_RATE" ist ein Spaltenname. Ich kann über den Namen direkt auf diese Spalte zugreiffen
.doFormat "FX_RATE", "F_RATES"
End With
==== Sinnvolles Beispiel für die vordefinierten Formate ====
Es macht ja wenig Sinn, für jede Spalte extra eine Formatierung anzulegen. Deshalb hier mal ein Beispiel in dem es sich lohnnt. Eine View mit mehreren Spalten die gleich formtiertwerden müssen.
\\ {{:vba:access:xlsxeporter:v_data.xlsx|}}
\\ für einfachere Fälle siehe auch [[#Direkte Formatierung]]
With XlsxExporter("v_data", "C:\temp\_v_data.xlsx")
.format("PERCENT").NumberFormat = "0.0000%" 'Prozentzahl mit 4 Nachkomma
.format("THOUSEND").NumberFormat = "#'##0'" 'Nur ab Tausend anzeigen 15'000 -> 15'
.export
.doFormat "FX_RATE", "fx_rate" 'fx_rate mit der vordefinierten FX_RATE-Format formatieren
.doFormat "PERCENT", "PERC_CHF", "PERC_PC" 'Prozentformatierung auf alle Prozentwerte anwenden
'Viele Felder mit der Spezialformatierung formatieren
.doFormat "THOUSEND", "PRICE_CHF_NOW", "PRICE_CHF_Yesterday", "DIFF_CHF", "PRICE_PC_NOW", "PRICE_PC_YESTERDAY", "DIFF_PC"
End With
==== Direkte Formatierung ====
Die obigen Beispiele sind natürlich nur nützlich, wenn an ein Format auf mehrere Spalten anwenden will (siehe [[#Sinnvolles Beispiel für die vordefinierten Formate]].
\\ Für einfacher Sachen kann man auch direkt die Range-Formate mitgeben. {{:vba:access:xlsxeporter:exportformated4.xlsx|}}
With XlsxExporter("my_table", "c:\temp\_exportFormated4.xlsx", acSpreadsheetTypeExcel12Xml, xeReplaceExistFile)
.export
'Spalte 1 direkt Formatieren
.range(1).Font.size = 13
'Das Feld f_bool aus der Tabelle
.range("f_bool").Font.Italic = True
'Und ein beliebiger Range. In dem Fall Zeile 1 mit dem Header
.range("1:1").Font.Bold = True
End With
----
===== Code =====
>//Die Klasse hat versteckte Attribute. Damit diese aktiv übernommen werden reicht es nicht aus, den Code in ein neues Modul zu kopieren. Man muss das Modul aus der Datei nach VBA importieren.//
>{{popup>:vba:vba_importfile.png|Bild zum Import}}
{{:vba:access:xlsxexporter.cls|Download %%fname%% (V-%%version%%)}}