Wereldkampioenschap voetbal in Excel: Outlook afspraken aanmaken
- 05/07/2014
 | Voorbeeldbestanden bij dit artikel: |
Inleiding
Nog maar kort geleden schreef ik een interessant artikel over gegevenstabellen en slicers, met als praktische illustratie het Wereldkampioenschap voetbal. Een ander stukje VBA-code dat ik met al mijn website bezoekers zou willen delen, is de volgende code. Op basis van de wedstrijden van het Wereldkampioenschap en wanneer ze gespeeld worden, kunnen automatisch Outlook afspraken aangemaakt worden!! Want geef toe, (a) je wil die wedstrijden n-absoluut niet missen, (b) je wil ze niet alle 64 zelf manueel in Outlook agenda zetten. Als ik mij vergis is het ook de eerste Excel VBA code voor Outlook op mijn website, dus dat is een primeur!
Hierboven vindt u een schermafdruk van een Excel bestand (.xlsm met macro's) dat u overigens bovenaan de pagina kan downloaden. I presenteer dezelfe look and feel dan het bestand in het eerdere artikel. Ik geef u wederom slicers om mee te spelen (filteren) en een Gegevenstabel, hoewel met iets andere kolommen. De verschuiving voor de tijdszone is achterwege gelaten.
Outlook afspraken aanmaken
Welke kolommen hebben we in het bestand? De kolom "Onderwerp" wordt bererekend met een formule, en zal gebruikt worden als het onderwerp van de Outlook afspraken. In essentie is he tonderwerp de wedstrijd (afgekorte ploegnamen), prefix "WK_" voor Wereldkampioenschap, en suffix is fase van het tornooi. Verderop in de tabel zal je merken dat dit op dit moment nog niet mogelijk is. Daarom gebruik ik de ISFOUT functie om de "fase" voor te stellen bij een fout in de berekening. Voor een aantal onder jullie is het interessant hoe ik de verkorte teamnaam (afkorting) opzoek in een andere Gegevenstabel (die staat op het tweede tabblad):
=INDEX(WC_2014_Teams[Afkorting];VERGELIJKEN([@Thuis];WC_2014_Teams[Land];0))
Dit is de INDEX/VERGELIJKEN vertaling naar een Gegevenstabel.
Naast het onderwerp voor de Outlook afspraken, hebben we de BusyStatus voor elke afspraak. Dat kan zijn:
| Outlook constante | Numerieke waarde |
| olWorkingElsewhere | 4 |
| olOutOfOffice | 3 |
| olBusy | 2 |
| olTentative | 1 |
| olFree | 0 |
-
Behalve de eerste optie laat ik jullie toe om de "BusyStatus" te bepalen in de kolom "Prioriteit". Daar hebben we een Data validatie lijst met 4 opties:
- "Die match wil ik niet missen" ==> olOutOfOffice
- "Ik ga die match bekijken" ==> olBusy
- "Mja, misschien wel, misschien niet" ==> olTentative
- "Als ik nog niet verveeld ben" ==> olFree
Jij kiest zelf de prioriteit in de kolom, en dat bepaalt de BusyStatus in Outlook voor de bewuste afspraak. Klinkt interessant, niet? :-) De code laat ook toe om een Outlook categorie in te stellen. Ik stel het in op mijn categorie "Wim" zodat alle afspraken in het geel getoond worden. Tot slot, de code markeert de afspraken als privé. Speel gerust wat met de code en voel je zo vrij de code te gebuiken / veranderen.
De duur van de afspraak is ofwel 110 minuten, ofwel 170 minuten. Ik neem 110 minuten voor gewone wedstrijden die steeds na 90 minuten stoppen, met wat extra tijd. 170 minuten is mijn berekende gok voor wedstrijden in de knockout fase waar het kan uitdraaien op strafschoppen. Nogmaals, verandere gerust indien je andere waarden hebben wil.
Een kort woordje over de kolom "ID" in de Gegevenstabel. Dat is de ID van de Outlook afspraak, als er één gecreëerd werd, tenminste. Het is de unieke, eenduidige, referentie naar het de afspraak. ID's laten toe om te bepalen of afspraken al aan de kalender werden toegevoegd, of niet. Maar belangrijker, ze laten ook toe om achteraf de afspraken opnieuw te verwijderen. De VBA procedure WKvoetbalOutlookAfspraken_Verwijderen() doet juist dit: verwijder de afspraken voor the Wereldkampioenschap voetbal in je Outlook kalender. Het hogervernoemde Excel bestand bevat deze procedure overigens.
Het finale resultaat voor de periode 23 tot en met 26 juni in mijn Outlook kalender:
VBA procedure
Hier is de code om Outlook afspraken aan te maken:
Sub WKvoetbalOutlookAfspraken_Aanmaken()' Wim Gielis ' https://www.wimgielis.com''''' ' VBA-code om Outlook afspraken aan je kalender toe te voegen ' 06/12/2014 '''''Const blnRecreate As Boolean = False Const blnOnlyFuture As Boolean = True Dim sqMatches As Variant Dim m As Long Application.ScreenUpdating = False If blnRecreate Then WKvoetbalOutlookAfspraken_Verwijderen sqMatches = ActiveSheet.ListObjects("WC_2014").DataBodyRange.Value For m = 1 To UBound(sqMatches) 'if date is later than today, create it If ((blnOnlyFuture = True) And (sqMatches(m, 2) >= Now)) Or (blnOnlyFuture = False) Then 'check de datum van de wedstrijf om te weten of we de Outlook afspraak moeten aanmaken of niet If Len(sqMatches(m, 11)) = 0 Then With CreateObject("Outlook.Application").CreateItem(1) 'olAppointmentItem = 1 .Start = sqMatches(m, 2) .Duration = IIf(InStr(UCase(sqMatches(m, 10)), "GR") = 1, 110, 170) .Subject = sqMatches(m, 7) .Location = sqMatches(m, 9) .Categories = "Wim" .Sensitivity = 2 'olPrivate = 2 Select Case sqMatches(m, 8) Case "No way I miss that match": .BusyStatus = 3 'olOutOfOffice = 3 Case "I will watch that match": .BusyStatus = 2 'olBusy = 2 Case "Well, maybe yes, maybe no": .BusyStatus = 1 'olTentative = 1 Case "If I am not bored already": .BusyStatus = 0 'olFree = 0 End Select .ReminderSet = False .Save '.Display 'update the data table with the afspraak ID ActiveSheet.ListObjects("WC_2014").DataBodyRange.Cells(m, 11) = .EntryID End With End If End If Next MsgBox "Klaar", vbInformationEnd Sub
Je kan de code nog uitbreiden met het zetten van een reminder, 15 minuten voor aanvang van de match, kwestie van niets te missen :-)
Merk de 2 booleaanse constanten (True/False of Waar/Onwaar) op die je bovenaan in de code kan instellen:
- Wil je de bestaande afspraken eerst verwijderen? (blnRecreate)
- Wil je enkel toekomstige afspraken aanmaken of ook afspraken die al voorbij zijn? (blnOnlyFuture)
De procedure "WKvoetbalOutlookAfspraken_Verwijderen" bevat 1 booleaanse constante die je ook kan instellen:
- Wil je enkel afspraken uit het verleden verwijderen of ook afspraken uit de toekomst? (blnOnlyPast)
Dat was het
Vooruit, veel plezier met de code voor het Wereldkampioenschap voetbal!
