VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "XlsxExporter"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
'-------------------------------------------------------------------------------
'File         : XlsxExporter.cls
'               Copyright mpl by ERB software
'               All rights reserved
'               http://wiki.yaslaw.info/dokuwiki/doku.php/vba/access/xlsxexporter
'Environment  : VBA 2007+
'Required     : Referenz auf 'MicrosoftExcel XY ObjectLibrary'
'Version      : 1.0.1
'Name         : XlsxExporter
'Author       : Stefan Erb (ERS)
'History      : 27.01.2015 - ERS - Creation
'               28.01.2015 - ERS - Property Get sheet auf Public gewechselt
'-------------------------------------------------------------------------------

'-------------------------------------------------------------------------------
' Example:
' Datei Exportieren und die Erste Zeile Fett setzen
'   Public Sub exp()
'       With XlsxExporter("my_table", "c:\temp\__tt.xlsx")
'           .range("1:1").Font.Bold = True
'       End With
'   End Sub
'-------------------------------------------------------------------------------
Option Explicit

'-------------------------------------------------------------------------------
' -- Public Members
'-------------------------------------------------------------------------------
Public Enum xeParams
    xeNone = 0                  'Kein Parameter
    xeReplaceExistFile = 2 ^ 0  'Falls die Exportdatei bereits exisitert, kann diese ersetzt werden
    xeHasFieldNames = 2 ^ 1     'Feldnamen werden in die erste Zeile geschrieben
End Enum

'-------------------------------------------------------------------------------
' -- Public Members
'-------------------------------------------------------------------------------
Private pFilePath    As String
Private pSource     As String
Private pXlsx       As Excel.application
Private pWb         As Excel.Workbook
Private pSheet      As Excel.Worksheet
Private pParams     As xeParams

'-------------------------------------------------------------------------------
' -- Public Methodes
'-------------------------------------------------------------------------------
'/**
' * Exportiert die Quelle in eine Excel-Datei und gibt eine Instance dieser Klasse zurück
' * @param  Variant             Quelle
' * @param  String              Export-Pfad
' * @param  AcSpreadSheetType   Excel-Format
' * @param  iParams             Parameters
' */
Public Function instance( _
        ByVal iSource As String, _
        ByVal iFilePath As String, _
        Optional ByVal iSpreadSheetType As AcSpreadSheetType = acSpreadsheetTypeExcel12Xml, _
        Optional ByVal iParams As xeParams = xeReplaceExistFile + xeHasFieldNames _
) As XlsxExporter
Attribute instance.VB_UserMemId = 0
'Attribute instance.VB_UserMemId = 0
    Set instance = New XlsxExporter
    instance.export iSource, iFilePath, iSpreadSheetType, iParams
End Function

'/**
' * Exportiert die Quelle in eine Excel-Datei
' * @param  Variant             Quelle
' * @param  String              Export-Pfad
' * @param  AcSpreadSheetType   Excel-Format
' * @param  iParams             Parameters
' */
Public Sub export( _
        ByRef iSource As Variant, _
        ByVal iFilePath As String, _
        Optional ByVal iSpreadSheetType As AcSpreadSheetType = acSpreadsheetTypeExcel12Xml, _
        Optional ByVal iParams As xeParams = xeReplaceExistFile + xeHasFieldNames _
)
    pFilePath = iFilePath
    pSource = iSource
    pParams = iParams
    If (pParams And xeReplaceExistFile) And cFso.FileExists(pFilePath) Then cFso.DeleteFile (pFilePath)
    DoCmd.TransferSpreadsheet acExport, iSpreadSheetType, pSource, pFilePath, (pParams And xeHasFieldNames)
End Sub

'/**
' * Seichert und schliesst die Datei
' * Diese Methode wird beim Abbauen des Objektes ebenfalls ausgeführt
' * @param  Boolean     Flag ob gespeichert werden soll
' */
Public Sub quit(Optional ByVal iSave As Boolean = True)
    wb.Close iSave: Set wb = Nothing
    xlsx.quit:      Set xlsx = Nothing
End Sub

'-------------------------------------------------------------------------------
' -- Public Properties
'-------------------------------------------------------------------------------

'/**
' * gibt ein Range des Sheetes zurück
' * @params Analog zu Range() in Excel
' * @return Range
' */
Public Property Get range(ByRef iCell1 As Variant, Optional ByRef iCell2 As Variant = Null) As Excel.range
    If IsNull(iCell2) Then
        Set range = sheet.range(iCell1)
    Else
        Set range = sheet.range(iCell1, iCell2)
    End If
End Property

'/**
' * Das Worksheet mit den exportierten Daten
' */
Public Property Get sheet() As Excel.Worksheet
    If pSheet Is Nothing Then Set pSheet = wb.Worksheets(pSource)
    Set sheet = pSheet
End Property

'-------------------------------------------------------------------------------
' -- Private Properties
'-------------------------------------------------------------------------------

'/**
' * DieExcel-Instance
' */
Private Property Get xlsx() As Excel.application
    If pXlsx Is Nothing Then Set pXlsx = New Excel.application
    Set xlsx = pXlsx
End Property
Private Property Set xlsx(ByRef iXlsx As Excel.application)
    Set pXlsx = iXlsx
End Property

'/**
' * Das Workbook mitden Exportierten Daten
' */
Private Property Get wb() As Excel.Workbook
    If pWb Is Nothing Then Set pWb = xlsx.Workbooks.Open(pFilePath)
    Set wb = pWb
End Property
Private Property Set wb(ByRef iWb As Excel.Workbook)
    Set pWb = iWb
End Property

'/**
' * Das Worksheet mit den exportierten Daten
' * Det Getter ist in den Public Properties
' */
Private Property Set sheet(ByRef iSheet As Excel.Worksheet)
    Set pSheet = iSheet
End Property

'-------------------------------------------------------------------------------
' -- Private Event
'-------------------------------------------------------------------------------
Private Sub Class_Terminate()
    If Not pWb Is Nothing Then quit
End Sub

'-------------------------------------------------------------------------------
' -- Private Libraries
'-------------------------------------------------------------------------------

'/**
' * Gibt ein FileSystemObject zurück
' * @return FileSystemObject
' */
Private Property Get cFso() As Object
    Static cachedObj As Object
    If cachedObj Is Nothing Then Set cachedObj = CreateObject("Scripting.FileSystemObject")
    Set cFso = cachedObj
End Property