Sub AlleRechnungenErstellen()

      Dim members As Collection
    Set members = New Collection

    Dim memberAddrs As Object
    Set memberAddrs = CreateObject("Scripting.Dictionary")

    Dim row, row2 As Long
    Dim maxRow, maxRow2 As Long
    Dim workbooks2 As Object
    Dim sheet, sheet2 As Worksheet

    Set sheet2 = ActiveWorkbook.Worksheets(1)
    maxRow = sheet2.Cells(sheet2.Rows.Count, "A").End(xlUp).row

    Set workbooks2 = Workbooks.Open("C:\juni 2017.xlsx")

    Set sheet = workbooks2.Worksheets(1)

    maxRow2 = sheet.Cells(sheet.Rows.Count, "A").End(xlUp).row


    Dim answer As Integer
    Dim language As Integer
    Dim answer2 As Integer
    Dim curName As String
    Dim curHour As Double
    Dim curAddr As String
    Dim curName2 As String
    Dim curAddr2 As String
    Dim curAddressed As String
    Dim curNr As String
    Dim curAnrede As String
    Dim curPLZ As String
    Dim curOrt As String
    Dim curAuftragsDatum As String
    Dim curStundensatz As Integer
    Dim RechnungsSumme As Long
    Dim vormonat As Integer



    For row2 = maxRow To 2 Step -1  'Läuft von unten nach oben
        curName2 = sheet2.Cells(row2, 2)
        curAddr2 = sheet2.Cells(row2, 3)
        curAddressed = sheet2.Cells(row2, 6)
        vormonat = sheet2.Cells(row2, 22)



        If IsNumeric(sheet2.Cells(row2, 12)) Then
        curStundensatz = sheet2.Cells(row2, 12)
        End If

        curAnrede = sheet2.Cells(row2, 5)
        If InStr(1, sheet2.Cells(row2, 4), " ") Then
            curPLZ = Split(sheet2.Cells(row2, 4), " ")(0)
            curOrt = Split(sheet2.Cells(row2, 4), " ")(1)
        End If

        curAuftragsDatum = sheet2.Cells(row2, 14)

        If Not memberAddrs.Exists(curName2) Then
            Set newMember = New auftrag
            Call newMember.Create(curName2, 0, curAddr2, curAddressed, curAnrede, curPLZ, curOrt, curAuftragsDatum, curStundensatz, vormonat)
            Call members.Add(newMember)
            Call memberAddrs.Add(curName2, curAddr2)
        End If

    Next row2



    For row = 1 To maxRow2
        curName = sheet.Cells(row, 1)
        curAddr = memberAddrs(curName)
        curHour = Val(sheet.Cells(row, 7))

        If MemberExist(members, curName) Then

            Set checkMember = GetMemberByName(members, curName)
            checkMember.AddHours (Val(curHour))
       Else

        Set newMember = New auftrag
        Call newMember.Create(curName, 0, curAddr, "Nicht im Auftragsverzeichnis", "Nicht im Auftragsverzeichnis", "Nicht im Auftragsverzeichnis", "Nicht im Auftragsverzeichnis", "Nicht im Auftragsverzeichnis", 0, 0)
        Call members.Add(newMember)
        End If

    Next row

    Dim strEintrag As Integer
    strEintrag = InputBox("Tragen Sie die aktuelle Rechnungsnummer ein:")

    Dim member As auftrag


    For Each member In members

        If member.GetHours > 0 Then

        RechnungsSumme = (member.GetHours * member.GetStundensatz) + ((member.GetHours * member.GetStundensatz) / 100) * 19


        MsgBox ("Name: " & member.GetName & vbNewLine & "Stunden: " & member.GetHours & vbNewLine & "Straße: " & member.GetAddr & vbNewLine & "PLZ: " & member.GetPLZ & vbNewLine & "Ort: " & member.GetOrt & vbNewLine & "Anrede: " & member.GetAnrede & vbNewLine & "Ansprechpartner: " & member.GetAddressed & vbNewLine & "Stundensatz: " & member.GetStundensatz & vbNewLine & "Rechnungssumme: " & RechnungsSumme & vbNewLine & "Stunden Vormonat: " & member.GetStundenVormonat)


        If member.GetStundenVormonat > 0 Then


        answer2 = MsgBox("Es existieren Stunden aus dem Vormonat (" & member.GetStundenVormonat & "). ")

        Set checkMember = GetMemberByName(members, member.GetName)
        checkMember.AddHours (Val(member.GetStundenVormonat))


        End If


        answer = MsgBox("Wollen Sie eine Rechnung für " & member.GetName & " (Stunden: " & member.GetHours & ") stellen?", vbYesNo + vbQuestion, "Rechnungsstellung")


        If answer = vbYes Then
            strEintrag = strEintrag + 1

            Dim pfad As String
            Dim savePfad As String
            Dim objWDApp As Object  'Word.Application
            Dim objDocx As Object    'Word.Document





         'SPRACHE DER RECHNUNG AUSWÄHLEN UND PFAD ENTSPRECHEND WÄHLEN
         'Vorlage für Englische Rechnungen schreiben


            pfad = ThisWorkbook.Path & "\Makro Vorlagen\Rechnung.docx"
            savePfad = ThisWorkbook.Path & "\Makro Output\Rechnung " & member.GetName & " 2017-" & strEintrag & ".docx"

            If Dir(pfad) = "" Then
                MsgBox "Datei """ & pfad & """ nicht gefunden!"
                Exit Sub
            End If




            Application.ScreenUpdating = False

            If objWDApp Is Nothing Then
                'damit wird verhindert das Word ein zweites Mal
                'mit CreateObject geöffnet wird
                'die erstere lässt sich sonst aus dem Task nicht entfernen
                'Bei mehreren Versuchen erreicht man ganz schnell ein OutOfMemory
                Set objWDApp = CreateObject("Word.Application")
                bolWordLiefNicht = True
            End If



            'Word-Anwendung sichtbar starten
            Set objWDApp = CreateObject("Word.Application")
            objWDApp.Visible = True
            'Vorlage öffnen - schreibgeschützt
            Set objDocx = objWDApp.Documents.Open(pfad)

            'Werte aus Zellen in Excel an Textmarken im Worddokument einfügen




            If objDocx.Bookmarks.Exists("Auftraggeber") = True Then
                objDocx.Bookmarks("Auftraggeber").Range.Text = member.GetName
            End If
            If objDocx.Bookmarks.Exists("Straße") = True Then
                objDocx.Bookmarks("Straße").Range.Text = member.GetAddr
            End If
            If objDocx.Bookmarks.Exists("Rechnungsnummer") = True Then
                objDocx.Bookmarks("Rechnungsnummer").Range.Text = "2017-" & strEintrag
            End If
            If objDocx.Bookmarks.Exists("Stunden") = True Then
                objDocx.Bookmarks("Stunden").Range.Text = member.GetHours
            End If
            If objDocx.Bookmarks.Exists("Ort") = True Then
                objDocx.Bookmarks("Ort").Range.Text = member.GetOrt
            End If
            If objDocx.Bookmarks.Exists("PLZ") = True Then
                objDocx.Bookmarks("PLZ").Range.Text = member.GetPLZ
            End If
            If objDocx.Bookmarks.Exists("zuHaenden") = True Then
                objDocx.Bookmarks("zuHaenden").Range.Text = member.GetAddressed
            End If
            If objDocx.Bookmarks.Exists("Auftragserteilung") = True Then
                objDocx.Bookmarks("Auftragserteilung").Range.Text = member.GetAuftragsdatum
            End If
            If objDocx.Bookmarks.Exists("Stundensatz") = True Then
                objDocx.Bookmarks("Stundensatz").Range.Text = member.GetStundensatz
            End If


            Application.ScreenUpdating = True


            objDocx.SaveAs (savePfad)


            Else

            'Stunden abspeichern falls Rehcnung nicht gestellt wird

            MsgBox ("Speicher: " & member.GetHours & "Stunden")
            For row2 = maxRow To 3 Step -1
              If member.GetName = sheet2.Cells(row2, 2) Then
              sheet2.Cells(row2, 22).Value = member.GetHours
              Exit For
              End If
            Next row2



    End If
    End If


    Next member





End Sub
Private Function GetMemberByName(list As Collection, name As String) As auftrag
    Dim member As auftrag

    For Each member In list
        If member.GetName = name Then
            Set GetMemberByName = member
            Exit Function
        End If
    Next member
End Function
Private Function MemberExist(list As Collection, name As String) As Boolean
    Dim member As auftrag

    For Each member In list
        If member.GetName = name Then
            MemberExist = True
            Exit Function
        End If
    Next member

    MemberExist = False
End Function






Private M_name As String
Private M_hours As Double
Private M_addressed As String
Private M_addr As String
Private M_stundensatz As Integer
Private M_anrede As String
Private M_plz As String
Private M_ort As String
Private M_StundenVormonat As Integer
Private M_auftragsDatum As String
Public Sub Create(ByVal name As String, ByVal hours As Double, ByVal addr As String, ByVal addressed As String, ByVal anrede As String, ByVal plz As String, ByVal ort As String, ByVal auftrag As String, ByVal stundensatz As Integer, ByVal stundenVormonat As Integer)
    M_name = name
    M_hours = hours
    M_addr = addr
    M_addressed = addressed
    M_anrede = anrede
    M_plz = plz
    M_ort = ort
    M_auftragsDatum = auftrag
    M_stundensatz = stundensatz
    M_StundenVormonat = stundenVormonat
End Sub

Public Function GetStundensatz() As Integer
GetStundensatz = M_stundensatz
End Function
Public Function GetAnrede() As String
    GetAnrede = M_anrede
End Function
Public Function GetAuftragsdatum() As String
    GetAuftragsdatum = M_auftragsDatum
End Function
Public Function GetPLZ() As String
    GetPLZ = M_plz
End Function
Public Function GetOrt() As String
    GetOrt = M_ort
End Function
Public Function GetName() As String
    GetName = M_name
End Function
Public Function GetHours() As Double
    GetHours = M_hours
End Function
Public Function GetAddr() As String
    GetAddr = M_addr
End Function
Public Function GetStundenVormonat() As Integer
    GetStundenVormonat = M_StundenVormonat
End Function
Public Function GetAddressed() As String
    GetAddressed = M_addressed
End Function
Public Sub AddHours(hours As Double)
    M_hours = M_hours + hours
End Sub