VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "ThisWorkbook"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = True
Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Assignments.Close True
End Sub

Private Sub Workbook_Open()
    Dim AssignmentsFile As String
    AssignmentsFile = Categories.Range("Zuweisungen")
    Application.DisplayStatusBar = True
    If Details.Cells(1, 1) = "" Then
        'Import downloaded Telekom's sdk-file or close workbook
        Dim Target
        Target = Application.GetOpenFilename("SYLK-Dateien (*.slk), *.slk")
        If Target <> False Then
            Application.StatusBar = "Daten werden importiert ..."
            Dim I As Integer 'multipurpose
            
            'Copy sdk-file's data into this worksheet
            Dim Import
            Set Import = GetObject(Target, "Excel.Sheet")
            Import.ActiveSheet.UsedRange.Copy Details.Cells(1, 1)
            Import.Close
            Set Import = Nothing
            
            'Delete unnecessary columns, find shorter headers etc.
            InChange = True
            For I = 1 To 4
                Details.Rows(1).Delete
            Next
            Details.Columns(1).Delete
            Details.Columns(2).Delete
            Details.Columns(6).Insert
            Details.Columns(9).Delete
            Details.Columns(9).Delete
            Details.Cells(1, 1).Value = "Anbieter"
            Details.Cells(1, 2).Value = "Anschluss"
            Details.Cells(1, 3).Value = "Datum"
            Details.Cells(1, 4).Value = "Beginn"
            Details.Cells(1, 5).Value = "Dauer"
            Details.Cells(1, 6).Value = "Nutzer"
            Details.Cells(1, 7).Value = "Nummer"
            Details.Cells(1, 8).Value = "Ziel"
            Details.Cells(1, 9).Value = "Betrag"
            'Details.Cells.NumberFormat = "@" 'Text, at least for "Nutzer"?
            Details.Columns(2).Replace "642100", ""
            Details.Columns.AutoFit
            Details.Columns(6).ColumnWidth = Categories.Columns(1).ColumnWidth
            For I = 1 To Details.UsedRange.Columns.Count
                Details.UsedRange.Columns(I).Name = Details.Cells(1, I).Value
            Next I
            InChange = False

'            'Save as proper named file (name by date of bill extracted from "Target")
'            For I = 1 To Len(Target) - 9
'                If Mid(Target, I, 3) = "evn" Then Exit For
'            Next I
'            ThisWorkbook.SaveAs "TelefonRechnung" + _
'                IIf(I < Len(Target) - 8, Mid(Target, I + 7, 2) + Mid(Target, I + 3, 2), "") + ".xls"
                
            ' Get now a filename for the bill.
            Dim StartOfTimeRange As Date, EndOfTimeRange As Date, TelefonRechnungsName$
            StartOfTimeRange = Details.Range("C2").Value
            EndOfTimeRange = Details.Range("C" & Details.Range("A1").SpecialCells(xlLastCell).Row).Value
            ' Dieser Name ist unabhängig von der Benennung der Datei durch die Telekom.
            ' Die Reihenfolge Jahr_Monat_tag wurde gewählt, damit die alphabetische Sortierung der Dateien der zeitlichen Reihenfolge entspricht.
            TelefonRechnungsName = "TelefonRechnung" & " " & Year(StartOfTimeRange) & "_" & Month(StartOfTimeRange) & "_" & Day(StartOfTimeRange) & "-" & Year(EndOfTimeRange) & "_" & Month(EndOfTimeRange) & "_" & Day(EndOfTimeRange) & ".xls"
            
            On Error Resume Next
                Err.Clear
                ThisWorkbook.SaveAs TelefonRechnungsName
                Select Case Err.Number
                Case 0
                    ' allet paletti
                Case 1004 ' Datei existiert, und Nein oder Abbrechen wurde gewählt
                    ' weiter ohne Speichern
                Case Else
                    MsgBox "Beim Speichern der Datei '" & TelefonRechnungsName & "' ist der Fehler Nr. " & Err.Number & " aufgetreten:" & Chr(13) & Err.Description, vbOKOnly, "Fehler Nr. " & Err.Number & " beim speichern"
                End Select
            On Error GoTo 0
        Else
            ThisWorkbook.Close
        End If
    End If
    
    
        
    'Connect to list of assignments (of numbers and extensions)
    Application.StatusBar = "Datei mit Zuweisungen " & AssignmentsFile & " wird geladen ..."
    Dim Item As Workbook
    For Each Item In Workbooks
        If Item.FullName = AssignmentsFile Then
            Set Assignments = Item
            Exit For
        End If
    Next
    If IsEmpty(Assignments) Then
        On Error Resume Next
        Set Assignments = Workbooks.Open(AssignmentsFile)
        Set Numbers = Assignments.Worksheets(NumbersSheet)
        Set Extensions = Assignments.Worksheets(ExtensionsSheet)
        If Err.Number <> 0 Then
            Application.StatusBar = "Datei mit Zuweisungen fehlt. Erzeuge Datei " & _
                AssignmentsFile & "..."
            Application.SheetsInNewWorkbook = 2
            Set Assignments = Workbooks.Add
            Set Numbers = Assignments.Worksheets(NumbersSheet)
            Numbers.Name = "Nummern"
            Numbers.Range("A1:D1") = Array("Nummer", "Nutzer", "Ort", "Bemerkungen")
            Numbers.Rows(1).Font.Bold = True
            Numbers.Columns(1).ColumnWidth = 15
            Numbers.Columns(3).ColumnWidth = 30
            Numbers.Columns(4).ColumnWidth = 40
            Numbers.Range.NumberFormat = "@" 'Text
            Set Extensions = Assignments.Worksheets(ExtensionsSheet)
            Extensions.Name = "Anschlüsse"
            Extensions.Range("A1:D1") = Array("Anschluss", "Nutzer", "Faktor", "Bemerkungen")
            Extensions.Rows(1).Font.Bold = True
            Extensions.Columns(1).ColumnWidth = 10
            Extensions.Columns(3).ColumnWidth = 6
            Extensions.Columns(4).ColumnWidth = 40
            Extensions.Range.NumberFormat = "@" 'Text
            Assignments.SaveAs AssignmentsFile
        End If
        On Error GoTo 0
    Else
        Set Numbers = Assignments.Worksheets(NumbersSheet)
        Set Extensions = Assignments.Worksheets(ExtensionsSheet)
    End If
    Numbers.Columns(2).ColumnWidth = Categories.Columns(1).ColumnWidth

    'From this list assign already known owners to calls
    Application.StatusBar = "Bekannte Nummern werden zugewiesen ..."
    If Not Categories.Evaluated Then AssignCalls
    
    'For proper functionality of replace mechanism
    Application.EnableAutoComplete = False
    
    'Redefine usage of <Ctrl>+<Up>/<Down> keys
    Application.OnKey "^{DOWN}", "FindNextEmptyEntry"
    Application.OnKey "^{UP}", "FindPrevEmptyEntry"
    
    'Find starting position
    Details.Activate
    Cells(1, 6).Activate
    FindNextEmptyEntry
    
    Application.StatusBar = "Bereit"
End Sub