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 VI: FINE-TUNING THE PROCEDURE AND SPEEDING IT UP Kitty’s brother, who is a senior analyst at NCSA, likes to describe his most successful programs as being “fast as a scalded dog.” Kitty is not that concerned about getting the run time down. She produced the first Outlook-based calendar on October 5 in 135 minutes. Ten days later, the procedure is down to 12 minutes. But, the test run indicates there’s still room for improvement. Adding the custom buttons helped, but there’s still room for error if the user clicks the buttons in the wrong order. The test run also indicates that a user could “lose” the cleaned-up Excel file if he/she is allowed or required to name it him/herself. Back to Experts Exchange. At 7:41, Kitty posted: Solution Title: Need VBA code to Save As a specific file name asked by prettykittyq on 10/16/2004 07:41AM EDT This solution was worth 400 Points and received a grade of A Kitty's Great Calendar Project is going well. Now I need some code that will save the open workbook under a specific file name without getting the user involved. By that I mean I'd rather not have to ask the user to supply the file name. In an ideal world, they won't even know the file was saved until a message box pops up to tell them it was. I found some code here in EE that does the SAVE AS but I have a question about specifying the path. If I don't specify a path in the code ( for example, C:\\KittyStuff\StupidCalendar) but I had the user open a workbook that is/was located in C:\\KittyStuff\StupidCalendar, will the new SAVE AS code automatically save to the StupidCalendar folder? I'm not sure I can specify the path correctly in advance. Users are using a shared drive and the drive name (like J:) might not be the same for every user's computer. Errr...since I asked two questions, I'll split the points and give 200 for the SAVE AS code and 200 for the answer to the "Which drive?" part of this. Thanks as always. Kitty The following dialogue appeared: Comment from patrickab Date: 10/16/2004 07:50AM EDT Comment Kitty, Are we ever going to see this amazing calendar? Patrick Accepted Answer from DRJ Date: 10/16/2004 07:55AM EDT Grade: A Accepted Answer You can use ThisWorkbook.Path to get the path of the workbook ActiveWorkbook.SaveAs ThisWorkbook.Path & "\FileName.xls" Comment from DRJ Date: 10/16/2004 07:55AM EDT Comment Also ThisWorkbook.Path will take into account the drive. Comment from prettykittyq Date: 10/16/2004 07:57AM EDT Your Comment Patrick: I'll share it when it's all finished, totally goof proof, and a thing of beauty. FYI, it had its first test drive yesterday. The Boss's executive assistant -- who is a Lieutenant Commander in the U. S. Navy and knows how to follow orders -- got it done right the first time in under 17 minutes. Lt Cmdr's assistant tried it and messed up twice because he doesn't know how to follow orders, but even he got it done in less than 30 minutes. Lt Cmdr's assistant is the reason I'm back at the drawing board trying to keep him from saving the file into the ozone where we can't find it again. The Boss got his first real-life version of the KittyCalendar in his homework package last night. Waiting to hear feedback from him. The best feedback will be for him NOT to notice we aren't using the old manually generated Word version. Stay tuned for further developments... Kitty Comment from prettykittyq Date: 10/16/2004 08:07AM EDT Your Comment Worked the first time! Thanks, DRJ! Kitty Kitty adds the following code to her project: ActiveWorkbook.SaveAs ThisWorkbook.Path & "\FileName.xls" Substituting “CalendarData” for “FileName” in the code, this becomes the macro SaveMyFile v 1. Kitty realizes that the way to keep the user from performing the steps in the wrong order is to not expect the user to perform the steps at all. How do achieve that? Produce one macro that runs the other macros. At 7:50 a.m., Kitty posts: Solution Title: Macro to run other macros asked by prettykittyq on 10/16/2004 07:50AM EDT This solution was worth 200 Points and received a grade of A I have a workbook with a bunch of macros coded separately in VBA. Let's call them Macro1, Macro2, Macro3. I would like to have a macro (call it SuperMacro) that automatically runs those Macros then pops-up a message box telling the user "Your data has now been cleaned." (Yeah, I know this is easy and you all know how to do this. Share your knowledge and earn the points.) Thanks in advance. Ignorant Kitty at 7:53 a.m., DRJ posts the answer: Sub SuperMacro() Call Macro1 Call Macro2 Call Macro3 MsgBox "Your data has now been cleaned." End Sub With appropriate substitutions of the real macro names, this becomes the macro SuperMacro One last improvement based on the test run: The Kid left in dates from the past when he exported from Outlook and forgot to delete them from the Excel file and from the final Word calendar. Kitty cleaned those up in her runs by sorting by date and time and just deleting the bygone appointments. It hits Kitty that it wouldn’t be that hard to ask Excel to toss out appointments equal to or earlier than Today. At 8:46 a.m., Kitty posts: Solution Title: VBA Code to delete rows with past appointments asked by prettykittyq on 10/16/2004 08:46AM EDT This solution was worth 300 Points and received a grade of A Kitty's Great Calendar Project is nearing its final form. Now I need to automatically delete rows that contain appointments from the past. The data was exported from Outlook into an Excel spreadsheet. Data looks like: Column A - StartDate (date/time) Column B - Subject (text) Column C - StartTime (date/time) Column D - Location (text) Column E - EndTime (date/time) Column F - Description (text) The spreadsheet does have a header row. I need to have the code look at Column A in Row 1, decide if it's less than or equal to Today, and delete the whole row it if it is, then go on to the next row, etc. Deleting today's appointments is a good thing (The Boss is always looking toward the future). Thanks and blessings on y'all in advance. Kitty By 9:10 a.m, brettdj had provided an answer which becomes the macro ForgetThePast. Kitty updates the SuperMacro to include ForgetThePast (preceded by another call of the SortDateTime macro). Kitty's new SuperMacro behaves so nicely, she has a grand time importing the data, then cleaning it up; then importing the data, then cleaning it up; then importing the data... So far, the Great Calendar Project has gone smoothly, the time to produce the report has dropped by an order of magnitude, and Kitty is thinking she and her EE pals are pretty hot stuff. Little does she know that there's trouble lurking in her paradise... Click here to go on to Trouble In Paradise...	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