Yaslaw.Info

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
<const>
    version=2.0.0
    vdate=05.02.2015
    fname=xlsxexporter.cls
    ns=%NAMESPACE%
    fpath=/vba/access/
</const>
====== [VBA][Access] XlsxExporter 2.x ======
//Exporter MS Access nach Excel. Die komplexe Version.//

==Version %%version%% %%vdate%%==
<WRAP round download 50%>
><fc #808080>//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.//</fc>
>{{popup>:vba:vba_importfile.png|Bild zum Import}}
{{:vba:access:xlsxexporter.cls|Download %%fname%% (V-%%version%%)}}
</WRAP>

Eine <del>kleine</del> 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 =====
**<fc #ff0000>Im VBA musss eine Referenz auf '%%MicrosoftExcel XY ObjectLibrary%%' gesetzt sein</fc>**

===== 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
<code vb>    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
</code>

==== Wichtige Methoden ====
=== initialize() ===
Initializiert eine Klasseninstanz mit den wichtigsten Paramtern

<code vb>'/**
' * 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 _
)</code>

Beispiel:
<code vb>Dim exp As New XlsxExporter
exp.initialize "source", "target.xlsx"</code>

=== 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
<code vb>'/**
' * 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</code>

Beispiel:
<code vb>Dim exp As XlsxExporter
Set exp = XlsxExporter.instance("source", "target.xlsx")
'oder dank Attribute instance.VB_UserMemId = 0
Set exp = XlsxExporter("source", "target.xlsx")</code>

=== 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
<code vb>'/**
' * 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 _
)</code>

=== quit() ===
Wendet bei aktivem ''paramRunDefaultFormating'' und vorhandenem Header die Headerformatierung an, speichert und schliesst die Datei.
>Diese Funktion wird bei ''Class_Terminate()'' automatisch aufgerufen.
<code vb>'/**
' * 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 _
)</code>

=== 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
<code vb>'/**
' * 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 _
)</code>

<code vb>
exp.doFormat "mein_formatname", "1:1"
exp.doFormat .format("mein_formatname"), .range("1:1")
exp.doFormat "mein_formatname", "1:1", "A3", .myRange
</code>

=== doFormatDefault() ===
Wendet die ''defaultFormat'' auf das ganze Sheet an. 
>Wird bei aktivem ''paramRunDefaultFormating'' am Ende der Funktion ''export()'' automatisch ausgeführt
<code vb>'/**
' * Formatiert das ganze Sheet mit der Standartformatierung
' */
Public Sub doFormatDefault()</code>

=== 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
<code vb>'/**
' * 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 _
)</code>

=== autoFit() ===
Passt die Spaltenbreiten den Daten an
>Wird bei ativem ''paramRunDefaultFormating'' im ''quit()'' automatisch ausgeführt
<code vb>'/**
' passt die Spaltenbreite an. Wird bei paramRunDefaultFormating=true automatisch durchgeführt
' */
Public Sub autoFit()</code>

=== requeryDefaultFormats() ===
Wenn man Anpassungen an dem ''defaultFormat'' vornimmt, kann man mit dieser Methode das neue Standardformat auf die restlichen Typenformate anwenden.
<code vb>'/**
' * Berechnet die Standardformate neu, basierend auf dem defaultFormat
' */
Public Sub requeryDefaultFormats()</code>

==== 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.
<code vb>'/**
' * 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</code>

Beispiel
<code vb>'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</code>

=== 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
<code vb>'/**
' * Direktzugirff auf das Standardformat
' */
Public Property Get defaultFormat() As Excel.range</code>

<code vb>exp.defaultFormat.font.size=10
exp.requeryDefaultFormats</code>

=== headerFormat ===
Direkter Zugriff auf das Format des Headers
<code vb>'/**
' * Direktzugirff auf die Headerformatierung
' */
Public Property Get headerFormat() As Excel.range</code>

<code vb>exp.headerFormat.font.bold = true</code>

=== range ===
Gibt einen Excel.Range des %%DataSheets%% zurück. 
<code vb>'/**
' * 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.range</code>

<code vb>Dim 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</code>

----
===== Beispiele =====
==== Einfacher Export mit ====
=== Standardformatierung ===
Exportiert eine Tabelle mit Standardformatierung {{:vba:access:xlsxeporter:exportonly1.xlsx|}}
<code vb>XlsxExporter("my_table", "c:\temp\_exportOnly1.xlsx").export</code>
=== ohne Formatierung ===
Exportiert eine Tabelle ohne Standardformatierung {{:vba:access:xlsxeporter:exportonly2.xlsx|}}
<code vb>XlsxExporter("my_table", "c:\temp\_exportOnly2.xlsx", , xeNone).export</code>

==== Mit eigenen Formaten ====
Eigene Formate erstellen und diese auf den export anwenden. {{:vba:access:xlsxeporter:exportformated1.xlsx|}}
<code vb>    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</code>

==== 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|}}

<code vb>    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</code>

==== 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|}}
<code vb>     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</code>

==== 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]]
<code vb>    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</code>

==== 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|}}
<code vb>    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>

----
===== Code =====
<WRAP round download 90%>
><fc #808080>//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.//</fc>
>{{popup>:vba:vba_importfile.png|Bild zum Import}}

{{:vba:access:xlsxexporter.cls|Download %%fname%% (V-%%version%%)}}
</WRAP>
<source '%%fpath%%/%%fname%%' vb>
Yaslaw.Info

User Tools

Site Tools

Page Tools
