World Cup soccer in Excel: creating Outlook appointments
- Jul. 05, 2014
 | Example files with this article: |
Introduction
Only a couple of hours ago, I posted an interesting article on data tables and slicers, with the World Cup soccer as my practical illustration. Another piece of VBA-code that I want to share with all my website visitors, is the following code. Based on the Word Cup matches and when they are played, Outlook appointments can be created without manually work! Because admit it, you (a) don't want to miss those matches, (b) don't want to enter them yourself in your Outlook agenda. If I'm not mistaken, it's the first Excel VBA code to Outlook code on my website, so that's nice too!
Above you can see a screenshot of the Excel file (a macro-enabled .xlsm file) that you download on top of the page in the Downloads section. I present the same look and feel than the file in the earlier article. Again, I give you slicers to play with and a Data table, albeit with some different columns. The timezone offset feature is identical. Please use it to match the times in the field "Your time" to your real local time.
Creating Outlook appointments
We do have a number of new columns, however. The "Subject" column is calculated by a formula, and will be used as the subject of the Outlook appointments. Basically, the subject is the match (abbreviated team names), prefix "WC_" for World Cup, and suffix is the group stage letter. Later on in the table, you will notice that this is not possible right now. Therefore, I use the IFERROR function to present the "Stage" in case of an error. Interesting to some of you, will be the way to look up the team abbreviation in a different data table (stored on the second sheet of the file):
=INDEX(WC_2014_Teams[Short name], MATCH( [@[Home_Team]], WC_2014_Teams[Long name], 0))
This is the INDEX/MATCH translation in a data table context.
Next to Outlook appointment subjects, we have the BusyStatus that an appointment can have. It can be:
| Outlook constant | Numeric value |
| olWorkingElsewhere | 4 |
| olOutOfOffice | 3 |
| olBusy | 2 |
| olTentative | 1 |
| olFree | 0 |
-
Except for the first option, I allow you to set the BusyStatus in the column "Priority". There is a Data validation list with 4 options:
- "No way I miss that match" ==> olOutOfOffice
- "I will watch that match" ==> olBusy
- "Well, maybe yes, maybe no" ==> olTentative
- "If I am not bored already" ==> olFree
You set the priority in the column, and that determines your Outlook BusyStatus for the given appointment. Sounds interesting, isn't it? :-) Also, the code allows to assign an Outlook category. I set it to my category "Wim" so that all appointments are shown in yellow. Lastly, the code marks the appointments as private. Please play around with the code and feel free to use it / tweak it.
The duration of the appointment is either 110 minutes, either 170 minutes. I use 110 minutes for regular matches that always end after 90 minutes of play with some overtime. 170 minutes is my guess for knockout phase matches where it can end up as a penalty shootout. Again, change it if you want to have different values.
A word about the ID column in the Table. This is the Entry ID of the Outlook appointment, if one has been created. It's a unique reference towards the appointment. It allows us to know that that match was already added to our calendar. But more importantly, it allows us to clean up the games again. The VBA procedure DeleteWorldCupAppointments() does just that: delete the appointments for the World Cup soccer in your Outlook calendar. The said Excel file contains this procedure.
The final result for June 23 to June 26 in my Outlook calendar (and my local times):
VBA procedure
Here is the code to create the Outlook appointments:
Sub CreateWorldCupAppointments()' Wim Gielis ' https://www.wimgielis.com''''' ' VBA-code to add Outlook appointments to your calendar ' 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 DeleteWorldCupAppointments sqMatches = ActiveSheet.ListObjects("WC_2014").DataBodyRange.Value For m = 1 To UBound(sqMatches) 'check the date of the game to know if we need to create the Outlook appointment If ((blnOnlyFuture = True) And (sqMatches(m, 2) >= Now)) Or (blnOnlyFuture = False) Then 'appointment item should not have been created earlier 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 appointment ID ActiveSheet.ListObjects("WC_2014").DataBodyRange.Cells(m, 11) = .EntryID End With End If End If Next MsgBox "Ready", vbInformationEnd Sub
You can extend the code to set a reminder, say 15 minutes before the start of the match, so that you don't have to miss any action :-)
Notice the 2 boolean flags (True or False) that you can set at the beginning of the code:
- Do you want to delete the existing appointments first? (blnRecreate)
- Do you want to only create appointments for the future, or also for the past? (blnOnlyFuture)
The procedure "DeleteWorldCupAppointments" contains 1 boolean flag that you can set as well (again, True of False):
- Do you want to only delete appointments for the past, or also for the future? (blnOnlyPast)
That's all folks
There you go, a nice piece of coding for the upcoming World Cup soccer!
