08-06-2007, 09:36 AM

Is there a software program available where i can sync ical with microsoft excel. i downloaded a schedule in microsoft excel format and would like to export it to ical, any suggestions?
thank you

09-28-2007, 10:04 PM

Thanks to a hint I found elsewhere, I managed to do what you want by creating a Word mail merge list from my Excel file event entries in iCal format. Here are the basic steps:

1. Drag any iCal event from your calendar onto your desktop and open it in Word. This file shows you the iCal standard lines required to describe an event.
2. Copy the lines between the BEGIN:VTIMEZONE line and the next to last line END:VEVENT into a new document. Use Tools|Data Merge Manager to Create a Form Letter from this new document.
3. In your Excel worksheet of events, create columns for start dates in yyyymmdd format, start times in hhmmss format (24-hour clock) and similarly for end dates and end times. Use appropriate unique column header names.
4. Copy your Excel worksheet data into a new Word document which will be your Data Source for your mail merge.
4. In the Word Form Letter document, look for the DTSTART line and replace the date (before the "T" character) with the Word mergefield name you've used for the start yyyymmdd. Replace the time (after the "T" character) with your mergefield name for the start hhmmss. Do the same for the DTEND line for end date and time.
5. Replace the event description in the SUMMARY: line with your mergefield for event description.
6. If you use calendar notes for the events, replace the text in the DESCRIPTION: line with your mergefield column names for note lines(multiple lines are separated by "\n").
7. Do a Merge to New Document (test this with just a few merge records first at this point). Globally replace the section breaks with nulls to remove them. Select all and copy the lines back into the original iCal event file you created in step 1., replacing the template lines you highlighted in step 2 and save.
8. Click on the iCal calendar file you've just updated. It will open iCal and import the events into the calendar you specify.

If you're familiar with mail merge in Word, this all isn't difficult.

Here's what the key Word Form Letter lines with my mergefield names look like:

DTSTART;TZID=US/Eastern:«yymmdd»T«starthhmmss»
SUMMARY:«description»
DTEND;TZID=US/Eastern:«yymmdd»T«endhhmmss»
DESCRIPTION:«noteline1»\n«noteline2»

If you need other iCal lines, make a dummy event, look for the appropriate lines in the copied event, and replace the data with a mergefield title for your Excel data.

Good luck!

01-27-2009, 12:13 PM

to sync your shift roster from excel to ical i will go through all the steps because usually people on shift will not normally use word form letters etc.
1. prepare your data in Excel create column of dates and custom format colum yyyymmdd no forwad slash. only two other columns needed description and unique id

2.create a single event in ical and drag to your desk top and open with word.
3. delete last line END:VCALENDAR
4.find unique ID "UID:C8E07A3D-2D99-4BC3-AEA5-DD87162B8C300" and copy to excel column put any number at end of ID and add 1 to next column C8E07A3D-2D99-4BC3-AEA5-DD87162B8C301 create a uid for every date by dragging down from two created,
you data is now ready save excel sheet.
5 In word go to tools and open Data merge manager
a: Create "Form Letter"
b: Get Data Open Data source which is your excel file
C:The Merge fields appear
Drag date to this field and replase date in front of T with merge field DTEND;TZID=Europe/Dublin:«sdate»T010000
Descrition to this field
SUMMARY:«desc»
UID:«uid»
Now go to merge and click on merge to new document
new Doc is created
In new Doc go to edit replace to remove page breaks and do the following
in replace type ^b and in replace with box type the word break and hit the space bar once, this is important. Hit replace all . got to end of doc and replace the last line which should be the word break with END:VCALENDAR
Nearly there.
Edit Select all and copy to clip board. Go back to original .ics file and highlight all text and edit and paste over it.
close file and answer yes to any pop up, your saved file should be in word format with .ics extension. drag file into ical and it should import all data.
Best of luck hope this save you the aggro I had with this