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	WRAP-UP, CREDITS AND THANKS: The Great Calendar Project started out as a much-despised assignment and turned into a project that became an obsession at times but was usually fun and ultimately very satisfying. The old Calendars 3 and 4 were prone to error, hard to keep up to date, and unlikely to match up with either the electronic Outlook calendar OR the handwritten, pencilled-in calendar. The new calendar matches exactly the state of the Outlook calendar at the moment the data is exported. No one ever estimated the time it took to maintain the old Calendars 1-4, but it probably took upwards of five hours a week just to compare notes between the calendars. The new calendar takes under five minutes to produce with most of the time invested in walking through the Import/Export Wizard in Outlook. The only comparison that needs to be done is checking to make sure The Boss's handwritten comments on his last hard-copy have been entered into the Outlook calendar. Estimate time to accomplish the comparing of notes: 20 minutes. The staff tended to put off the old calendar projects as long as possible since they are so tedious. The process of producing the new calendar is kind of fun and therefore less likely to be neglected. Along with making all the improvements over the Old System, Kitty had to learn about what it is the Wizard really does when it exports data from Outlook, she had to learn how to use VBA code for Excel, she had to sit down and really think about what kind of tasks can be automated, and she had to reacquaint herself with the notion that there's a lot of knowledge out there in the world just waiting to be tapped. Kitty would like to thank once again all of her great Experts at www.experts-exchange.com: bruintje, who wrote the code for the ReplaceApos macro brettdj, who wrote the code for the GetDate macro, the KillB macro, and ForgetThePast macro, wrote a procedure to force users to SAVE AS instead of SAVE, and explained how to edit the properties of custom buttons roos01, who wrote the code for the DeleteFakeTimes macro and the EmptyA macro MalicUK, who wrote the code for the CompareDates macro jevreist, who wrote the code for the SortDateTime macro matthewspatrick, who wrote a code snippet to clear the contents of the file when it opens DRJ, who wrote the first SaveMyFile macro and demonstrated how to write the SuperMacro ShaneDevenshire, who provided helpful comments on using the Personal Workbook and recommended the workaround of re-recording ImportCalendarData at the site at which it will be used, and byundtd, who encouraged Kitty to use the Record A New Macro function of Excel as a way to generate her own code, helped translate Shane’s comments on re-recording macros, and provided the code for the second SaveMyFile macro. The right sidebar of this site offers links to: each of the macros used in the Great Calendar screenshots of various stages in producing the Great Calendar a downloadable Excel file with all of the code attached to the CalendarTemplate a downloadable Access database set up to produce the report that becomes the Great Calendar No one really owns the Great Calendar Project so feel free to download its components and adapt it to your own use. Kitty	Some VBA Code: All Macros CompareDates DeleteFakeTimes Empty A ForgetThePast GetDate ImportCalendarData KillB ReplaceApos SaveMyFile (v. 1) SaveMyFile (v. 2) SortDateTime SuperMacro Other Stuff Calendar Template (Excel file) dbCalendar.mdb (Access database) Report Screenshot
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