The Great Calendar Project
Journey to a Solution
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 III: CALL IN THE EXPERTS TO AUTOMATE DATA CLEAN-UP
Throughout the vacation, the back of Kitty’s mind continues to fuss about the fact that the data clean-up requires a lot of attention to detail and the ability to follow complex instructions--things at which her colleagues don't always excel.
On October 11, at 5:00 a.m. Kitty’s mind wakes her up and says, “Can’t you automate this?”
Kitty is used to using VBA (Visual Basic for Applications) to program Access but she’s never tried using VBA in Excel. She hauls out the 1200-plus page “Using Microsoft Office 2003” book supplied by IT and starts reading.
At 4:30 p.m. Kitty’s mind says, “You silly goose! Why not ask the Experts at Experts Exchange?”
Experts Exchange, www.experts-exchange-com, is a website used by thousands of computer gurus. Kitty used it extensively in 2002 when developing from scratch her database-drive website, www.dc-movies.com, but hasn’t really gone back since. She logs on and finds she’s still a paying member.
Going into the Microsoft Outlook area, Kitty posts the following:
Solution Title: Generate custom calendar in Word from Outlook 2003 data
asked by prettykittyq on 10/11/2004 04:43PM EDT
This solution was worth 500 Points and received a grade of A
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:
StartDate (date/time)
StartTime (date/time)
Subject (text; the "main title" of the event)
Location (text)
EndTime (date/time)
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.
Kitty
Kitty waits patiently for a miraculous response but it’s not forthcoming.
Undaunted, Kitty plugs away at breaking her process into smaller bits in between doing her “real” work at the Office.
Searching through Experts Exchange, she finds the following dialogue:
Solution Title: How do I export correct date format from Outlook Calendar to Excel?
asked by *name withheld* on 02/15/2003 09:14AM EST
This solution was worth 75 Points and received a grade of A
I frequently have to export my calendar from Outlook to Excel to add mileage and other information that Outlook does not store.
When I export using Outlook's export feature and saving as a Microsoft Excel file and then open it in Excel the date format always has a ' in front of the date, for example, '7/20/2002. Is there a quick way to eliminate that ' in Excel all at once in front of the date. I usually have quite a large file and was wondering if using a Find and Replace function would work in this case?
Please advise. Thank you for your help.
*name deleted*
The answer, provided by bruintje, became the macro ReplaceApos:
Kitty is delighted to see someone else had the same problem.
She spends another hour searching for other existing solutions that might help her out but finds nothing. Emboldened by her success thus far, she decides to just ask for help on Excel programming from Excel Experts.
On October 13 at 7:40 a.m., she goes to the Microsoft Excel area of Experts Exchange and posts:
Solution Title: VB Code to automatically fill in dates
asked by prettykittyq on 10/13/2004 07:40AM EDT
This solution was worth 300 Points and received a grade of A
We need to create a six-month calendar for The Boss, which we update about once a week.
We begin by exporting the data from Outlook Calendar to an Excel spreadsheet and mess with it from there.
The problem is that Outlook only exports dates for which there is at least one appointment entry and The Boss wants to see every date on his calendar whether it has an appointment or not.
We've been doing this in the Excel worksheet by autofilling dates by dragging the fill handle until we've dragged six months' worth of dates.
Is it possible to use VB code to create a routine that will prompt for today's date (or the date on which the calendar is supposed to begin) and then add rows with the needed dates from beginning date to beginning-date-plus-six-months?
Thanks in advance for your help.
Kitty
At 7:53 a.m., brettdj posts the answer, which became the macro GetDate:
Kitty cuts-and-pastes, thinking this can be run easily by the Assistant and the Kid.
Next stop: get rid of those stupid Lunch appointments. At 7:49 a.m., Kitty posts another question:
Solution Title: Automatically delete rows based on a cell's value
asked by prettykittyq on 10/13/2004 07:49AM EDT
This solution was worth 450 Points and received a grade of A
The Boss does not like having the appointment "Lunch" show up on his six month hard copy calendar.
"Lunch" shows up because of a recurring appointment inserted by The Boss's scheduler (and I can't talk her out of doing that).
I produce the hard copy calendar by exporting data from Outlook Calendar to an Excel spreadsheet.
The rows I want to delete are those that have the word Lunch (by itself) in the column B (which has the column header Subject).
Can I use VB code to set up a routine that looks at the value of the B cell, determines if that value is equal to "Lunch", and then deletes the row if that's true?
Note that I don't want to delete non-routine lunches which might show up as Subject = "Lunch with President Bush".
Thanks in advance.
Kitty
At 7:57 a.m., brettdj provides Kitty with another answer, which becomes the macro KillB:
On a roll now, at 8:44 a.m., Kitty asks if someone can get rid of her “fake midnights.”
Solution Title: VB Code to clear contents of cells where value is midnight
asked by prettykittyq on 10/13/2004 08:44AM EDT
This solution was worth 350 Points and received a grade of A
In the continuing story of The Boss's calendar, I now need to clear the contents of all the cells that contain "fake" midnights as StartTime and EndTime of an Outlook appointment.
The story so far:
I've been exporting the data from Outlook Calendar to an Excel spreadsheet. I discovered that, when the scheduler adds an All Day Event in Outlook, it's internally stored as an appointment for which the start time and end time are 12:00:00 AM.
Surprise! The Boss doesn't like seeing those fake times.
StartTime is in Column C, EndTime is in Column E.
Can I use VB code to find the fake midnights and either clear the contents of those cells or store an empty string in them?
Thanks as always for any help.
Kitty
At 9:07 a.m., roos01 posts a solution that becomes the macro DeleteFakeTimes:
Inspired by the excellent assistance she’s getting from EE, Kitty mentally restates all her data-cleaning procedures as steps a computer could take. At 10:00 a.m., she asks if someone can clean up the unneeded empty dates.
Solution Title: Deleting unneeded rows based on value in another row
asked by prettykittyq on 10/13/2004 10:00AM EDT
This solution was worth 400 Points and received a grade of A
This is the last part in the Continuing Story of The Boss's Calendar.
I routinely create a hard copy six month calendar for The Boss. To do that, I export data from Outlook Calendar into an Excel spreadsheet.
Because The Boss wants to see every date for the next six months, whether or not it has an appointment associated with it, we use a macro to add "empty dates" -- one date for each day between Today and Today Plus Six Months.
This is great for the months in the future in which there aren't a whole lot of appointments yet. Unfortunately, we end up having to manually delete any empty dates for which there IS an appointment.
Your mission, should you choose to accept it, is to write me VB code for Excel that will:
1) Examine a date to see if it is empty. (It should be considered empty if Column A -- StartDate -- is not blank and Column B -- Subject -- is blank.)
2) Check to see if there is another row with that same date that isn't empty (that is, does this date have an appointment already?)
3) If there is no Not Empty date, leave the empty date in.
4) If there IS a Not Empty date, delete the matching empty date row.
I can tell the staff to sort by Date Then by Subject before running the macro if that helps. (Logic: all the dates will be next to each other and easier to compare.)
Thanks in advance for your help.
Kitty
After a few faulty starts, MalicUK posts the answer at 10:28 a.m., the macro CompareDates
MalicUK also throws in for free code to sort the data automatically, which becomes the macro SortDateTime:
Since it’s 11 a.m. on a weekday and Kitty is overdue at the Office, she saves everything and heads back off to work.
That evening, she does a dry-run to see how well the macros work.
Actual time to produce the clean document: 65 minutes – down from 135.
Kitty is ecstatic.
End of phase III.
Click here to go on to Phase IV...
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