Importing Calendar in MS Outlook w/o duplicates

Share discussions around the Classic Local Unit Website (LUWS).
Locked
121887mattandjulie
New Member
Posts: 14
Joined: Thu Mar 06, 2008 7:58 pm
Location: Menifee, Ca.

Importing Calendar in MS Outlook w/o duplicates

#1

Post by 121887mattandjulie »

Calendar Downloads into MS Outlook:
If you have tried to import the calendar from your ward or Stake website into Outlook 2003 or possibly Outlook 2007 and have seen duplicate entries created each time you import. This post is for you;).
Apparently the implementation of the iCal standard by Microsoft into its Outlook products was not as robust as it could have been according to an inside authority I will not name here. When importing iCal files, Outlook does not provide any options such as replace duplicates like you will see when importing a CSV file. Only problem is the CSV file format exported from the ward/stake calendar is not compatible with MS Outlook 2003 for sure & other versions likely either. This is due to the organization and strict column naming requirements of Outlook when importing data. Currently members can only import 12 months at a time & to sync your Outlook calendar on a regular basis requires multiple downloads & imports of files. Due to the previous problem of duplicates this feature has not been used as well as it could or at all for Outlook users who have experienced this problem.
Solutions & work arounds:
Long term: Implementation of the new CalDAV standard across webservers & calendaring applications
Short term: Use my Macro until some one figures out a way to implement it on a Church Server to automatically spit out an Outlook compatible CSV file. The Macro was written in Excel 2003 & takes the exported CSV file from the calendar and automatically reformats it & spits out a new file in the correct format. All you have to do is take the reformatted file and import into Outlook as a CSV file using the option to replace duplicates.
Downside: This will not remove previous calendar entries that were downloaded and imported previously but then latter removed from the ward calendar due to cancellation etc... Those would have to be done manually.
Upside: It will let those who were previously unable to import Church calendars to do so and therefor be able to sync to their Smart phone as well if they have one. I attached the macro named Outlook converter.xls to this post. Once you have the file if you add it to your hidden XLSTART directory (Documents & Settings-Your account-Application Data-Microsoft-Excel-XLstart) you can then run it from within Excel with hot key Cntrl + (lower case letter o) or select from Tools-Macro-Macros after opening the downloaded CSV file from the calendar, to select it. Make sure you have a C:\temp directory before starting as the macro is hard coded to export to C:\temp. Suggest you save the downloaded CSV file there as well. Any questions send me an email through the forum & I will try to add additional information for benefit of all.
Attachments
Outlook converter.XLS
(21 KiB) Downloaded 278 times
User avatar
opee
Member
Posts: 338
Joined: Mon Jun 25, 2007 3:00 am
Location: Sunnyvale, CA

#2

Post by opee »

Neutron Flux wrote:Calendar Downloads into MS Outlook:
If you have tried to import the calendar from your ward or Stake website into Outlook 2003 or possibly Outlook 2007 and have seen duplicate entries created each time you import. This post is for you;).

Any questions send me an email through the forum & I will try to add additional information for benefit of all.

I like what you have done. Thanks for sharing. Is there a way to mark all of the items "Private" through the macro, or assign it a "Category" for Outlook--so that I don't have to do that manually after I import them into Outlook? (I like to make my Church calendar items "Private" and show as "Church" category so that they do not show up on my company meeting calendars...
User avatar
opee
Member
Posts: 338
Joined: Mon Jun 25, 2007 3:00 am
Location: Sunnyvale, CA

#3

Post by opee »

Neutron Flux wrote:The Macro was written in Excel 2003 & takes the exported CSV file from the calendar and automatically reformats it & spits out a new file in the correct format.

I ran the macro and it caused the start date to move to the "Categories" column on the new file, and the Start Date and Start Time on the new file were actually the end date and time on my original CSV file. The End Date and Time were blank on the new file.

Also, the 'Title" in the old file becomes the "Location" in the new file. and the description of the event in the old file becomes the "Subject" in the new file.

I think that you have a great start here--and I have been looking for a tool like this for a while, but it does not seem to be doing what you are saying that it is supposed to do.

Any suggestions why it may not be working for me?
User avatar
opee
Member
Posts: 338
Joined: Mon Jun 25, 2007 3:00 am
Location: Sunnyvale, CA

#4

Post by opee »

opee wrote:Any suggestions why it may not be working for me?

I just STEPPED THROUGH the macro in Excel and realized that the Active Cell needs to be "A1" in order for the macro to work properly. If a different cell in a different column is active, then it will cause havoc as you can see from my previous post.

I would suggest adding the following the start of your macro:

Range("A1").Select

To make sure that the first column of data is selected.

The command: ActiveCell.Columns("A:A").EntireColumn.Select selected the entire column of data where the cell was active and deleted that one, then proceeded in reference to that column.

Hope that helps. Nice macro.
121887mattandjulie
New Member
Posts: 14
Joined: Thu Mar 06, 2008 7:58 pm
Location: Menifee, Ca.

Outlook CSV converter - Improvements

#5

Post by 121887mattandjulie »

Is it possible you clicked on a cell in the downloaded CSV file prior to running the macro which in turn changed the active cell? I never had a problem with it before but I like your suggestion, feel free to implement, test & repost / upload for everyone's benefit as it is now public domain. The trick for everyone's benefit on figuring out Excel is to take a working calendar you have the way you want it and export it out as a CSV file from Outlook. Then open the file in Excel as a CSV file and examine its structure and column names etc. This will tell you what Outlook needs to properly import. Now when you download the CSV file from the Church calendar you can likewise open in Excel & examine its structure and compare to figure out which columns need renamed, added, split & renamed etc using the wizzard. This will allow you to customize it beyond the simple fix I have done. Please feel free to post any improvements along with explanation of the included improvements. I am still looking for a fix for importing the Ward directory will run through the posts but my problem is in Outlook it downloaded the entire directory but only shows as one entry.
Locked

Return to “Classic Ward & Stake Sites (LUWS)”