The Great Calendar Project
Journey to a Solution 		 
The Story of
A Journey:

The Journey Begins

Phase I: Outlook to Excel

Phase II: Cleaning Up the Excel Data

Phase III: Call In the Experts

Phase IV: Making It Goof-Proof

Phase V: A Test Run and More Goof-Proofing

Phase VI: Fine-Tuning

Trouble In Paradise

Credits and Thanks

Home



PHASE II: CLEAN UP THE EXCEL DATA AND EXPORT IT TO ACCESS

Now that Kitty has managed to get the calendar data out of Outlook, she needs to figure out how to get that data into Word.

She tries using MailMerge in Word to produce a catalog with the Excel file as her source data but abandons that very quickly.

Problems:
  1. Outlook has exported the date and time data as text not numbers, so Excel acts a bit funny when sorting.
  2. There are a number of recurring events that appear in Outlook that The Boss does not like seeing on his hard-copy calendar.
  3. Kitty can’t make Excel and Word understand the concept of grouping by day and grouping by month, so events in the merged document show up as:

    Monday, October 4, 2004 -- 8:30 a.m. to 10:30 a.m --Staff Meeting
    Monday, October 4, 2004 -- 11:00 a.m. to 12:00 p.m. -- Meeting at the White House
    Monday, October 4, 2004 -- 2:30 p.m. to 3:30 p.m. -- Meeting with economists

    The Boss (not surprisingly) wants to see:

    Monday, October 4, 2004
    8:30 a.m. to 10:30 a.m. -- Staff Meeting
    11:00 a.m. to 12:00 p.m. -- Meeting at the White House
    2:30 p.m. to 3:30 p.m. -- Meeting with economists

  4. Outlook treats AllDayEvents as events that begin at 12:00:00 AM and end at 12:00:00 AM. Without even asking, Kitty knows The Boss is going to hate seeing those fake midnights.
  5. The Boss wants to see an entry for every day. Outlook only exports days for which there is at least one appointment.
  6. The Boss wants all the appointments on a given day to appear on the same page. Kitty can do this by hand using “Format, Paragraph, Lines and Breaks, Keep With Next” but it is a labor-intensive process.
  7. The Boss wants each month to start on a new page and to begin with a header that shows the month’s name in large, bold, italic type. Kitty can insert page breaks and month names by hand but it’s also labor-intensive.

    Grouping Months and Days

    Kitty, who is a big fan of Microsoft Access, decides to take advantage of Access’s grouping functions to produce a report which she can then Publish As A Word Document.

    Dates As Numbers

    Kitty encounters a snag when she discovers Access doesn’t recognize the dates and times as date/time formatted data. It thinks they are text strings. After a few abortive attempts to get Access to clean up its own act, Kitty goes back to the Excel file. By poking around and invoking Excel Help a few million times, Kitty discovers the DATEVALUE and TIMEVALUE functions in Excel. This gets her around the problem of dates-as-text, although it’s a complex set of instructions and Kitty isn’t sure The Kid will be able to replicate them. Nevertheless, Kitty is delighted with her progress.

    An Entry For Every Day

    Kitty takes advantage of Excel’s autofill function to add “empty dates” to the calendar. Empty dates are days that don’t have an appointment associated with them. The snag here is that an empty date creates a big gap on each day after the report is generated. Kitty resolves this by sorting and manually deleting the unnecessary empty dates. It works but it’s labor-intensive.

    Delete Unwanted Recurring Events

    The recurring events in question are “Lunch.” The Assistant has inserted in Outlook a recurring event showing 12:30 p.m. to 1:30 p.m. as Lunch, to remind herself not to overbook The Boss’s calendar. The Boss does not like seeing “Lunch” show up on his calendar.

    Kitty fixes this problem by sorting by Subject and manually deleting all the Lunches. It’s a fast operation, since the unwanted Lunches all appear next to each other and can be deleted in one operation, but it does require labor.

    Delete “Fake Midnights”

    Kitty gets around the fake midnights inserted by Outlook in AllDay Events by sorting on StartTime, then clearing the contents of StartTime and EndTime. Fortunately, the fake midnights in EndTime match up with the fake midnights in StartTime so she only has to sort once. Again, it’s a fast operation but it does require labor.

    Victory! One real-time week and twenty-seven billable hours into the Great Calendar Project, Kitty produces an acceptable Word document for The Boss.

    Actual time to produce the clean document: 135 minutes.

    Kitty’s boss is duly impressed and nominates her for a $500 bonus. Kitty accepts it and goes back to spending her time outside the Office in more enjoyable pursuits. Among other things, Kitty and her husband take a week-long vacation.

    End of Phase II.

    Click here to go on to Phase III...

Some
VBA Code:

All Macros

CompareDates

DeleteFakeTimes

Empty A

ForgetThePast

GetDate

ImportCalendarData

KillB

ReplaceApos

SaveMyFile (v. 1)

SaveMyFile (v. 2)

SortDateTime

SuperMacro

A KittyTours website maintained by Jean K. Rosales, Ph.D.


Comments, corrections, and suggestions should be sent to
webmaster@kittytours.org

© 2004 kittytours.org, All Rights Reserved