This is some code to create an iCalendar (.ics) file from data in cells
Note: These bits of code are mostly for my own reference, but if anyone else finds them useful all the better.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 |
Sub CreateCalendar() Dim CalendarData As Range Dim N_rows As Integer ' Add a reference to "Microsoft Scripting Runtime" Dim CurrentFileSystemObject As New FileSystemObject Dim CurrentTextFile As TextStream Dim PathString As String Dim Filename As String Dim FullPath As String Dim DataString As String Dim BeginString As String Dim EndString As String Dim SummaryString As String Dim LocationString As String Dim DateStartString As String Dim DateEndString As String Dim CurrentDate As Date ' It is a assumed the data is arranged in 3 adjacent columns ' and uses a named rage "CalendarData" ' Column 1: The date of the event ' Column 2: The event title string ' Column 3: The event location string Set CalendarData = Range("CalendarData") N_rows = CalendarData.Rows.Count Filename = "calendar.ics" PathString = "C:\" FullPath = PathString + Filename BeginString = "BEGIN:VEVENT" EndString = "END:VEVENT" ' Create the file Set CurrentTextFile = CurrentFileSystemObject.CreateTextFile(FullPath) ' Write header information DataString = "BEGIN:VCALENDAR" CurrentTextFile.WriteLine (DataString) DataString = "VERSION:2.0" CurrentTextFile.WriteLine (DataString) ' Write the individual events For i = 1 To N_rows CurrentTextFile.WriteLine (BeginString) CurrentDate = CalendarData(i, 1) SummaryString = "SUMMARY:" + CalendarData(i, 2) LocationString = "LOCATION:" + CalendarData(i, 3) DateStartString = "DTSTART;VALUE=DATE:" + Format(CurrentDate, "yyyymmdd") DateEndString = "DTEND;VALUE=DATE:" + Format(CurrentDate, "yyyymmdd") CurrentTextFile.WriteLine (SummaryString) CurrentTextFile.WriteLine (LocationString) CurrentTextFile.WriteLine (DateStartString) CurrentTextFile.WriteLine (DateEndString) CurrentTextFile.WriteLine (EndString) Next i ' Write the closing information and close the file DataString = "END:VCALENDAR" CurrentTextFile.WriteLine (DataString) CurrentTextFile.Close End Sub |
Cheers! Just what I needed – playing with a shared Excel calendar and wanted a way for people to be able to add it to their Outlook.
Exactly what I need to do!
Thanks!!!
For those new to VBA (like me), if you have trouble running the code at :
” Dim CurrentFileSystemObject As New FileSystemObject”
you need to go, in your VBA editor, in the “Tool” menu, then “Reference”, and then check the box of “Microsoft Scripting Runtime”.
Thanks heaps. I modified it to run in Outlook 2013. For some reason, I had to alter:
Set CurrentTextFile = CurrentFileSystemObject.CreateTextFile(FullPath)
to:
Set fso = CreateObject(“Scripting.FileSystemObject”)
Set CurrentTextFile = fso.CreateTextFile(FullPath, True)
to get it to work.
Elo,
cool!
I like it!
do you know how to parameterize a recall?
Thank’s for your help.
nb :If you have other parameters for Icalendar, i’m really interested (example : start time, end time).
Yep, 6 years later and still useful 🙂
Thanks
Frank