lerone revised this gist . Go to revision
1 file changed, 5 insertions, 2 deletions
gistfile1.txt
| @@ -52,10 +52,13 @@ Sub SerienKalendereintraegeErstellen() | |||
| 52 | 52 | Set olItems = selectedCalendar.Items | |
| 53 | 53 | olItems.Sort "[Start]" | |
| 54 | 54 | olItems.IncludeRecurrences = True | |
| 55 | + | ||
| 55 | 56 | Dim gefilterteItems As Object | |
| 56 | - | Set gefilterteItems = olItems | |
| 57 | + | Dim filterStart As String | |
| 58 | + | filterStart = "[Start] >= '" & Format(Date, "ddddd h:nn AMPM") & "'" | |
| 59 | + | Set gefilterteItems = olItems.Restrict(filterStart) | |
| 57 | 60 | ||
| 58 | - | Debug.Print "Kalender geladen: " & selectedCalendar.Name & ", " & gefilterteItems.Count & " Elemente gefunden" | |
| 61 | + | Debug.Print "Kalender geladen: " & selectedCalendar.Name & ", " & gefilterteItems.Count & " gefilterte Einträge" | |
| 59 | 62 | Set ws = ThisWorkbook.Sheets(1) | |
| 60 | 63 | lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row | |
| 61 | 64 | ReDim alreadyProcessed(1 To lastRow) | |
lerone revised this gist . Go to revision
1 file changed, 1 insertion, 3 deletions
gistfile1.txt
| @@ -55,9 +55,7 @@ Sub SerienKalendereintraegeErstellen() | |||
| 55 | 55 | Dim gefilterteItems As Object | |
| 56 | 56 | Set gefilterteItems = olItems | |
| 57 | 57 | ||
| 58 | - | Dim gefilterteItems As Object | |
| 59 | - | Set gefilterteItems = olItems | |
| 60 | - | End If | |
| 58 | + | Debug.Print "Kalender geladen: " & selectedCalendar.Name & ", " & gefilterteItems.Count & " Elemente gefunden" | |
| 61 | 59 | Set ws = ThisWorkbook.Sheets(1) | |
| 62 | 60 | lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row | |
| 63 | 61 | ReDim alreadyProcessed(1 To lastRow) | |
lerone revised this gist . Go to revision
1 file changed, 9 insertions, 1 deletion
gistfile1.txt
| @@ -65,7 +65,15 @@ Sub SerienKalendereintraegeErstellen() | |||
| 65 | 65 | Set eintraege = New Collection | |
| 66 | 66 | ||
| 67 | 67 | For i = 2 To lastRow | |
| 68 | - | Debug.Print "Verarbeite Zeile " & i & " – " & ws.Cells(i, "B").Text & " – Produkt: " & ws.Cells(i, "G").Text | |
| 68 | + | Debug.Print "---" | |
| 69 | + | Debug.Print "Verarbeite Zeile: " & i | |
| 70 | + | Debug.Print "Datum: " & ws.Cells(i, "B").Text | |
| 71 | + | Debug.Print "Startzeit: " & ws.Cells(i, "C").Text | |
| 72 | + | Debug.Print "Endzeit: " & ws.Cells(i, "D").Text | |
| 73 | + | Debug.Print "Produkt: " & ws.Cells(i, "G").Text | |
| 74 | + | Debug.Print "Hinweis für Trainer: " & ws.Cells(i, "L").Text | |
| 75 | + | Debug.Print "Veranstaltungsnummer: " & ws.Cells(i, "F").Text | |
| 76 | + | Debug.Print "Ort: " & ws.Cells(i, "J").Text | |
| 69 | 77 | If alreadyProcessed(i) Then GoTo Weiter | |
| 70 | 78 | If Not IsDate(ws.Cells(i, "B").Value) Then | |
| 71 | 79 | ws.Cells(i, "M").Value = "übersprungen (Datum fehlt)" | |
lerone revised this gist . Go to revision
1 file changed, 6 insertions, 4 deletions
gistfile1.txt
| @@ -22,8 +22,7 @@ Sub SerienKalendereintraegeErstellen() | |||
| 22 | 22 | ||
| 23 | 23 | startTime = Timer | |
| 24 | 24 | ||
| 25 | - | If Not DRYRUN Then | |
| 26 | - | On Error Resume Next | |
| 25 | + | On Error Resume Next | |
| 27 | 26 | Set olApp = GetObject(, "Outlook.Application") | |
| 28 | 27 | If olApp Is Nothing Then Set olApp = CreateObject("Outlook.Application") | |
| 29 | 28 | On Error GoTo 0 | |
| @@ -51,8 +50,10 @@ Sub SerienKalendereintraegeErstellen() | |||
| 51 | 50 | Set selectedCalendar = allCalendars(selectedIndex) | |
| 52 | 51 | MsgBox "Ausgewählter Kalender: " & selectedCalendar.Name, vbInformation, "Kalenderbestätigung" | |
| 53 | 52 | Set olItems = selectedCalendar.Items | |
| 54 | - | olItems.Sort "[Start]" | |
| 55 | - | olItems.IncludeRecurrences = True | |
| 53 | + | olItems.Sort "[Start]" | |
| 54 | + | olItems.IncludeRecurrences = True | |
| 55 | + | Dim gefilterteItems As Object | |
| 56 | + | Set gefilterteItems = olItems | |
| 56 | 57 | ||
| 57 | 58 | Dim gefilterteItems As Object | |
| 58 | 59 | Set gefilterteItems = olItems | |
| @@ -64,6 +65,7 @@ Sub SerienKalendereintraegeErstellen() | |||
| 64 | 65 | Set eintraege = New Collection | |
| 65 | 66 | ||
| 66 | 67 | For i = 2 To lastRow | |
| 68 | + | Debug.Print "Verarbeite Zeile " & i & " – " & ws.Cells(i, "B").Text & " – Produkt: " & ws.Cells(i, "G").Text | |
| 67 | 69 | If alreadyProcessed(i) Then GoTo Weiter | |
| 68 | 70 | If Not IsDate(ws.Cells(i, "B").Value) Then | |
| 69 | 71 | ws.Cells(i, "M").Value = "übersprungen (Datum fehlt)" | |
lerone revised this gist . Go to revision
1 file changed, 234 insertions
gistfile1.txt(file created)
| @@ -0,0 +1,234 @@ | |||
| 1 | + | ' Serien-Kalendereinträge aus Excel erstellen mit Serienlogik, Löschung alter Einträge, Logging, Laufzeitmessung, Zusammenfassung und Simulationsmodus | |
| 2 | + | ||
| 3 | + | Sub SerienKalendereintraegeErstellen() | |
| 4 | + | ' Benutzer wird gefragt, ob Simulation gewünscht ist | |
| 5 | + | Dim userResponse As VbMsgBoxResult | |
| 6 | + | userResponse = MsgBox("Möchtest du eine Simulation durchführen (keine Einträge werden erstellt oder gelöscht)?", vbYesNo + vbQuestion, "Simulationsmodus") | |
| 7 | + | Dim DRYRUN As Boolean | |
| 8 | + | DRYRUN = (userResponse = vbYes) ' True = Simulation, False = Echtbetrieb | |
| 9 | + | ||
| 10 | + | Dim olApp As Object, olNS As Object | |
| 11 | + | Dim allCalendars As Collection, selectedCalendar As Object | |
| 12 | + | Dim calendarNames() As String, selectedIndex As Variant | |
| 13 | + | Dim olItems As Object, ws As Worksheet | |
| 14 | + | Dim lastRow As Long, i As Long, j As Long, erstelltCount As Long, serienCount As Long, geloeschtCount As Long, uebersprungenCount As Long | |
| 15 | + | Dim eintraege As Collection | |
| 16 | + | Dim datum As Date, startzeit As Date, endzeit As Date | |
| 17 | + | Dim produkt As String, hinweis As String, veranstaltungsnummer As String | |
| 18 | + | Dim titel As String, ort As String | |
| 19 | + | Dim alreadyProcessed() As Boolean | |
| 20 | + | Dim startTime As Double | |
| 21 | + | Dim logSheet As Worksheet | |
| 22 | + | ||
| 23 | + | startTime = Timer | |
| 24 | + | ||
| 25 | + | If Not DRYRUN Then | |
| 26 | + | On Error Resume Next | |
| 27 | + | Set olApp = GetObject(, "Outlook.Application") | |
| 28 | + | If olApp Is Nothing Then Set olApp = CreateObject("Outlook.Application") | |
| 29 | + | On Error GoTo 0 | |
| 30 | + | If olApp Is Nothing Then | |
| 31 | + | MsgBox "Outlook konnte nicht gestartet werden.", vbCritical | |
| 32 | + | Exit Sub | |
| 33 | + | End If | |
| 34 | + | Set olNS = olApp.GetNamespace("MAPI") | |
| 35 | + | Set allCalendars = New Collection | |
| 36 | + | ||
| 37 | + | Dim store As Object, folder As Object | |
| 38 | + | For Each store In olNS.Folders | |
| 39 | + | For Each folder In store.Folders | |
| 40 | + | If folder.DefaultItemType = 1 Then allCalendars.Add folder | |
| 41 | + | Next folder | |
| 42 | + | Next store | |
| 43 | + | ||
| 44 | + | ReDim calendarNames(1 To allCalendars.Count) | |
| 45 | + | For i = 1 To allCalendars.Count | |
| 46 | + | calendarNames(i) = allCalendars(i).Parent.Name & " – " & allCalendars(i).Name | |
| 47 | + | Next i | |
| 48 | + | selectedIndex = Application.InputBox("Wähle den Zielkalender aus (Zahl):" & vbCrLf & Join(calendarNames, vbCrLf), "Kalenderauswahl", Type:=1) | |
| 49 | + | If Not IsNumeric(selectedIndex) Or selectedIndex < 1 Or selectedIndex > allCalendars.Count Then Exit Sub | |
| 50 | + | ||
| 51 | + | Set selectedCalendar = allCalendars(selectedIndex) | |
| 52 | + | MsgBox "Ausgewählter Kalender: " & selectedCalendar.Name, vbInformation, "Kalenderbestätigung" | |
| 53 | + | Set olItems = selectedCalendar.Items | |
| 54 | + | olItems.Sort "[Start]" | |
| 55 | + | olItems.IncludeRecurrences = True | |
| 56 | + | ||
| 57 | + | Dim gefilterteItems As Object | |
| 58 | + | Set gefilterteItems = olItems | |
| 59 | + | End If | |
| 60 | + | Set ws = ThisWorkbook.Sheets(1) | |
| 61 | + | lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row | |
| 62 | + | ReDim alreadyProcessed(1 To lastRow) | |
| 63 | + | ||
| 64 | + | Set eintraege = New Collection | |
| 65 | + | ||
| 66 | + | For i = 2 To lastRow | |
| 67 | + | If alreadyProcessed(i) Then GoTo Weiter | |
| 68 | + | If Not IsDate(ws.Cells(i, "B").Value) Then | |
| 69 | + | ws.Cells(i, "M").Value = "übersprungen (Datum fehlt)" | |
| 70 | + | uebersprungenCount = uebersprungenCount + 1 | |
| 71 | + | GoTo Weiter | |
| 72 | + | End If | |
| 73 | + | If ws.Cells(i, "C").Value = "" Then | |
| 74 | + | ws.Cells(i, "M").Value = "übersprungen (Startzeit fehlt)" | |
| 75 | + | uebersprungenCount = uebersprungenCount + 1 | |
| 76 | + | GoTo Weiter | |
| 77 | + | End If | |
| 78 | + | If ws.Cells(i, "G").Value = "" Then | |
| 79 | + | ws.Cells(i, "M").Value = "übersprungen (kein Produkt)" | |
| 80 | + | uebersprungenCount = uebersprungenCount + 1 | |
| 81 | + | GoTo Weiter | |
| 82 | + | End If | |
| 83 | + | ||
| 84 | + | datum = DateValue(ws.Cells(i, "B").Value) | |
| 85 | + | If IsDate(ws.Cells(i, "C").Value) Then | |
| 86 | + | startzeit = TimeValue(ws.Cells(i, "C").Value) | |
| 87 | + | Else | |
| 88 | + | ws.Cells(i, "M").Value = "übersprungen (ungültige Startzeit)" | |
| 89 | + | GoTo Weiter | |
| 90 | + | End If | |
| 91 | + | If IsDate(ws.Cells(i, "D").Value) Then | |
| 92 | + | endzeit = TimeValue(ws.Cells(i, "D").Value) | |
| 93 | + | Else | |
| 94 | + | ws.Cells(i, "M").Value = "übersprungen (ungültige Endzeit)" | |
| 95 | + | GoTo Weiter | |
| 96 | + | End If | |
| 97 | + | ||
| 98 | + | produkt = Trim(ws.Cells(i, "G").Value) | |
| 99 | + | veranstaltungsnummer = Trim(ws.Cells(i, "F").Value) | |
| 100 | + | hinweis = Trim(ws.Cells(i, "L").Value) | |
| 101 | + | ort = Trim(ws.Cells(i, "J").Value) | |
| 102 | + | ||
| 103 | + | If InStr(1, hinweis, "Lehrgang: ", vbTextCompare) > 0 Then | |
| 104 | + | hinweis = Mid(hinweis, InStr(1, hinweis, "Lehrgang: ", vbTextCompare) + 10) | |
| 105 | + | End If | |
| 106 | + | titel = produkt & " - " & hinweis | |
| 107 | + | If titel = " - " Or titel = "-" Then | |
| 108 | + | ws.Cells(i, "M").Value = "übersprungen (kein gültiger Titel)" | |
| 109 | + | GoTo Weiter | |
| 110 | + | End If | |
| 111 | + | ||
| 112 | + | ' Duplikatprüfung: Wenn in Outlook bereits ein Termin mit Datum und Veranstaltungsnummer vorhanden ist | |
| 113 | + | Dim skip As Boolean: skip = False | |
| 114 | + | If Not DRYRUN Then | |
| 115 | + | Dim existingItem As Object, itemDate As Date | |
| 116 | + | For Each existingItem In gefilterteItems | |
| 117 | + | If existingItem.Class = 26 Then | |
| 118 | + | itemDate = Int(existingItem.Start) | |
| 119 | + | If itemDate = datum Then | |
| 120 | + | If InStr(existingItem.Body, veranstaltungsnummer) > 0 Then | |
| 121 | + | If existingItem.Subject <> titel Then | |
| 122 | + | Dim msg As VbMsgBoxResult | |
| 123 | + | msg = MsgBox("Ein Eintrag mit gleicher Veranstaltungsnummer aber abweichendem Titel existiert: " & existingItem.Subject & vbCrLf & _ | |
| 124 | + | "Neuer Titel wäre: " & titel & vbCrLf & "Trotzdem ersetzen?", vbYesNo + vbExclamation) | |
| 125 | + | If msg = vbNo Then skip = True | |
| 126 | + | Else | |
| 127 | + | skip = True | |
| 128 | + | End If | |
| 129 | + | End If | |
| 130 | + | End If | |
| 131 | + | End If | |
| 132 | + | Next existingItem | |
| 133 | + | End If | |
| 134 | + | If skip Then | |
| 135 | + | ' Bestehenden Termin ggf. löschen | |
| 136 | + | If Not DRYRUN Then | |
| 137 | + | For Each existingItem In gefilterteItems | |
| 138 | + | If existingItem.Class = 26 And Int(existingItem.Start) = datum Then | |
| 139 | + | If InStr(existingItem.Body, veranstaltungsnummer) > 0 Then | |
| 140 | + | existingItem.Delete | |
| 141 | + | geloeschtCount = geloeschtCount + 1 | |
| 142 | + | Exit For | |
| 143 | + | End If | |
| 144 | + | End If | |
| 145 | + | Next | |
| 146 | + | End If | |
| 147 | + | ws.Cells(i, "M").Value = "ersetzt (gelöscht + neu)" | |
| 148 | + | GoTo Weiter | |
| 149 | + | End If | |
| 150 | + | ||
| 151 | + | ' Erstellung des Outlook-Termins oder Simulation | |
| 152 | + | If DRYRUN Then | |
| 153 | + | ws.Cells(i, "M").Value = "(Simulation) " & titel | |
| 154 | + | ws.Cells(i, "M").Interior.Color = RGB(200, 200, 200) | |
| 155 | + | Else | |
| 156 | + | Dim appt As Object | |
| 157 | + | Set appt = olApp.CreateItem(1) | |
| 158 | + | With appt | |
| 159 | + | .Start = datum + startzeit | |
| 160 | + | .End = datum + endzeit | |
| 161 | + | .Subject = titel | |
| 162 | + | .Location = ort | |
| 163 | + | .Body = veranstaltungsnummer | |
| 164 | + | .ReminderSet = False | |
| 165 | + | .BusyStatus = 2 | |
| 166 | + | .Save | |
| 167 | + | End With | |
| 168 | + | ws.Cells(i, "M").Value = titel | |
| 169 | + | erstelltCount = erstelltCount + 1 | |
| 170 | + | End If | |
| 171 | + | alreadyProcessed(i) = True | |
| 172 | + | ||
| 173 | + | ' Serientermin-Erkennung: gleiche Veranstaltungsnummer an aufeinanderfolgenden Werktagen | |
| 174 | + | Dim serieEndRow As Long: serieEndRow = i | |
| 175 | + | Do While serieEndRow + 1 <= lastRow And Not alreadyProcessed(serieEndRow + 1) | |
| 176 | + | Dim nextDatum As Date | |
| 177 | + | If Not IsDate(ws.Cells(serieEndRow + 1, "B").Value) Then Exit Do | |
| 178 | + | nextDatum = DateValue(ws.Cells(serieEndRow + 1, "B").Value) | |
| 179 | + | If ws.Cells(serieEndRow + 1, "F").Value <> veranstaltungsnummer Then Exit Do | |
| 180 | + | If Weekday(datum, vbMonday) = 5 Then ' Freitag | |
| 181 | + | If DateDiff("d", datum, nextDatum) <> 3 Then Exit Do ' Freitag → Montag | |
| 182 | + | Else | |
| 183 | + | If DateDiff("d", datum, nextDatum) <> 1 Then Exit Do | |
| 184 | + | End If | |
| 185 | + | If ws.Cells(serieEndRow + 1, "G").Value = "" Then Exit Do | |
| 186 | + | serieEndRow = serieEndRow + 1 | |
| 187 | + | Loop | |
| 188 | + | ||
| 189 | + | If serieEndRow > i Then | |
| 190 | + | ' Serien-Eintrag | |
| 191 | + | If Not DRYRUN Then | |
| 192 | + | Dim recurrenceAppt As Object | |
| 193 | + | Set recurrenceAppt = olApp.CreateItem(1) | |
| 194 | + | With recurrenceAppt | |
| 195 | + | .Start = datum + startzeit | |
| 196 | + | .End = datum + endzeit | |
| 197 | + | .Subject = titel | |
| 198 | + | .Location = ort | |
| 199 | + | .Body = veranstaltungsnummer | |
| 200 | + | .ReminderSet = False | |
| 201 | + | .BusyStatus = 2 | |
| 202 | + | .Save | |
| 203 | + | Dim pattern | |
| 204 | + | Set pattern = .GetRecurrencePattern | |
| 205 | + | pattern.RecurrenceType = 1 ' daily | |
| 206 | + | pattern.PatternStartDate = datum | |
| 207 | + | pattern.Occurrences = serieEndRow - i + 1 | |
| 208 | + | .Save | |
| 209 | + | End With | |
| 210 | + | For j = i To serieEndRow | |
| 211 | + | alreadyProcessed(j) = True | |
| 212 | + | ws.Cells(j, "M").Value = titel & " (Serie)" | |
| 213 | + | Next j | |
| 214 | + | serienCount = serienCount + 1 | |
| 215 | + | GoTo Weiter | |
| 216 | + | Else | |
| 217 | + | For j = i To serieEndRow | |
| 218 | + | alreadyProcessed(j) = True | |
| 219 | + | ws.Cells(j, "M").Value = "(Simulation) " & titel & " (Serie)" | |
| 220 | + | ws.Cells(j, "M").Interior.Color = RGB(180, 180, 180) | |
| 221 | + | Next j | |
| 222 | + | serienCount = serienCount + 1 | |
| 223 | + | GoTo Weiter | |
| 224 | + | End If | |
| 225 | + | End If | |
| 226 | + | ||
| 227 | + | Weiter: | |
| 228 | + | Next i | |
| 229 | + | ||
| 230 | + | Dim elapsedTime As Double | |
| 231 | + | elapsedTime = Timer - startTime | |
| 232 | + | MsgBox "Fertig. " & erstelltCount & " Termine erstellt, " & serienCount & " Serientermine erstellt, " & geloeschtCount & " ersetzt, " & uebersprungenCount & " übersprungen." & vbCrLf & _ | |
| 233 | + | "Laufzeit: " & Format(elapsedTime, "0.00") & " Sekunden", vbInformation | |
| 234 | + | End Sub | |
Newer
Older