Page 1 of 1

Importing ward membership directory into MS Outlook

Posted: Sun Apr 20, 2008 9:25 pm
by 121887mattandjulie
The csv format exported from LUWS member directory page, like its calendar cousin, is incompatible with MS Outlook. Like the calendar this is because the column names must be exact in order to import in and the names in the downloaded format do not match those required. Additionally information is crammed into one column that needs to be split out over several to import properly etc.....I am no expert, I am not a software programmer or macro maestro just a computer user trying to make stuff work. My macro is not perfect and if you look at the code you can see I did some two stepping but the job gets done.. mostly. I publish this macro with the following advise and limitations. The best way to see if it will work for you is to try it then examine and make minor adjustments to the resultant file before importing.
Recommendations:
1) Do not release this macro to entire ward but if you find it useful I recommend the web admin use it to create files that can be saved to the news & information section periodically but no less than monthly so members can import into Outlook on their computers & ergo sync their smart phones & PDA's that have not other free option.
Limitations:
a) In order to combine the names of all family members into a single column renamed Notes & to include other information such as email addresses and additional phone numbers, a concatenate formula had to be used to join multiple columns into one. I extended this formula down about 15-20 rows beyond my ward directory size to facilitate growth. It should handle up to around 255 family records but beyond that the macro will need to be edited to extend it down a few more rows. Any rows formulated that are not used result in empty Contact Folder entries upon importation. These are easily deleted by hitting delete or you can edit the resultant file and remove the bottom rows of the resultant file before saving this may help.
b) If the ward is not consistent in the way it modifies its MLS entries it could skew the results of the macro since it is limited as to how it can parse information and split columns. Example if both husband & wife have cell phones and the ward list in MLS shows first intial then phone number then first initial and phone number of second spouse without any spaces it cannot be parsed. Excel limits its dleimiters to space, comma, tab,semicolon and one other character. Care must be used in selection to insure uniform & consistent results as much as possible. having abnomolies in MLS will throw thisoff and require some post macro editing to correct until MLS is adjusted.
c) Entries with comments like DNC may require consultation with PEC as members who do not wish to be contacted most likely do not want their information published as well. In these cases you can simply delete the rows containing such entries upon direction from PEC/ Bishopric.
d) The macro does not include the save function so you must manually hit the save button after running the macro to save the results & over write the original file or no changes will be saved & running the macro will be for not.
e) Do not delete or modify the columns on the end as they are used in teh concatenate formula & deltign them will delte the notes column data. The extra columns are not imported but their data is required to facilitate the conversion. If you don't know what it does, don't mess with it!;)
f) City names are expected to be one word if broken into two words you will need to edit manually the result to combine into one column and clear extra data as required.
Importing:
Select Import from another program of file then - comma separted values (windows) when importing into Outlook.
PS To post to LUWS I just renamed the csv ext. to xls & told members to rename after they download prior to importing not all members have excel and may not be able to convert if truly saved as a XLS file. It works that is what counts.

If you find this macro useful please review and give me a star or two etc for usefulness. If there are any serious flaws let me know & post. Remember I am limited to what information I can easily extract & reorganize from that presented from the downloaded csv file. If you can do better please share.:confused: I believe this file also includes my original macro for converting the calendar as well. The Hot key for directory conversion should be CNTRL+W. You will have to set your Excel security option to medium to allow the option to run macros save then reopen the file to prompt to run the macro.

Posted: Wed Apr 23, 2008 9:10 am
by daddy-o-p40
This group has a link to a program which will do this and more. Think it's called Unit List on the Run.

Thank you!

Posted: Sun Apr 27, 2008 6:24 pm
by 121887mattandjulie
First I heard of it! Thanks for sharing wished I known about it before I re-invented the wheel?:( Tried it and it works great so have posted in my News & Information section for entire ward membership & will no longer need to post converted files for them.:D