Generate custom calendar in Word from Outlook 2003 data
Posted on 2004-10-11
My boss has a strong preference for having a hard copy of his six-month calendar that he carries around and on which he and his wife make updates and notations of personal appointments and events.
His assistants maintain his official calendar on Outlook 2003. We tried printing out the six-month calendar from within Outlook (using the normal print function) but he is very picky about the design of his six-month calendar and says that it can't have those boxes Outlook insists on printing around the name of each day. He also dislikes having extraneous details included in the six-month calendar (we use the details section to remind ourselves of things like the contact person for the event or the need to get the speech writer to draft speeches) and goes ballistic when he sees "12:30 p.m. - 1:30 p.m. Lunch" on his calendar as the only entry for a day. Final fussy detail: he wants to see an entry for every single day, even if that day has no appointments, in case he wants to add a personal appointment on the weekend or on an otherwise dull weekday.
His first assistant (who resigned to get away from him) had one of the junior staff laboriously type every entry from Outlook into Word, following the Boss's preferred format and applying the rules on what to include and what to omit by hand. Not surprisingly, this has not proven to be an ideal solution because it's labor-intensive AND the junior staff didn't always remember to update the Word version when the Outlook version changed, so Boss was sometimes working from very old information.
The second assistant (who also resigned for much the same reason) was the one who tried to sell him on the Outlook-generated calendar but he wouldn't buy it (plus we had the problem with no entries for "empty" dates and too much information included in details).
I recently got roped into trying to figure out a way to generate the hard copy calendar from the Outlook data, meeting all his requirements but avoiding the hand-typing method. It CAN be done but it uses almost every program in MSOffice and I'm concerned it's too complex a process to hand over to the junior staff.
My solution thus far has been:
Use Import/Export Wizard to export Outlook data to an Excel file.
Inside Excel, convert the date and time information from Outlook's text format to a date format. I've been using DATEVALUE to copy the Outlook date column to a new column. I then copy "values" from the new column into a third column and delete the original Outlook date and the formula-filled column. Ditto the two times (StartTime and EndTime).
I then create "empty dates" by autofilling dates-only at the end of the table. These end up being the entries for the "empty dates" the Boss wants to have included.
I sort the columns a number of times and a number of ways to get rid of the pesky "Lunches" and to clear out the "12:00 AM" starting times Outlook attaches to "all day events."
With one last sort, I go through and delete the "empty dates" for those days which DO have at least one appointment.
Next, I use "get external data" in Access to pull the Excel spreadsheet data into an Access table. The Access table is the recordsource for an Access report that meets the Boss's demands for grouping by day and grouping by month.
From within the "preview report" pane, I use Share Office Tools to create a Word Rich Text Format file.
I save the .rtf file as a .doc file and use Word for final editing and formatting.
Does anyone have any suggestions of a more efficient (and more idiot-proof) way to get to the final Word file?
For the record, the fields I end up dealing with are:
Subject (text; the "main title" of the event)
Description (text; the info included in the details section of the Outlook appointment)
To help you frame your answers, I have a lot of experience programming VBA in Access but have never tried to use VBA in any other MS Office application. I do know what a macro is and can usually be trusted to copy code correctly, but I might need to be coached through how to add code to non-Access Office programs (never tried it and nervous about giving it a go).
I have loads o' Experts Exchange points available and am willing to be generous with awarding them if we can get this down to a junior-staff exercise that's close to turn-key in its simplicity.
Feel free to give input on just a segment of the problem. I noticed, for example, an EE answer to the "Outlook exports dates and times to Excel as text" problem that uses a macro to clean up the data (instead of having to create, copy, and delete columns). A rewrite of that solution tailored to my problem would be greatly appreciated. I'll award 500 additional points via other questions for any segment that works for me. (I'm sure we can work around the point-awarding problem with some patience and creativity.)
Thanks in advance for your help. I just as soon not be the third assistant to solve this problem by running away from it.