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	TROUBLE IN PARADISE While messing about testing the new fast-as-a-scalded-dog program, Kitty accidentally saves the CalendarTemplate file before clearing its contents. When she reopens CalendarTemplate, it won’t let her import data from her CurrentCalendarData file but does offer to “Refresh Data” for her. Hmm...does this mean Excel “remembers” whence it last imported the data? Intrigued, Kitty posts another question on EE: Solution Title: Import Data greyed out but Refresh Data appeared asked by prettykittyq on 10/16/2004 09:47AM EDT This solution was worth 500 Points and received a grade of A Kitty's Great Calendar Project Continues... I created an Excel workbook called CalendarTemplate which contains all the macros one needs to automatically run The Boss's Six Month Calendar. Users are supposed to import the most recent data from an Excel file that contains data exported from Outlook. After running the various macros to clean up the data, they are expected to SAVE AS another filename (to keep them from SAVEing the template and messing it up). The CalendarTemplate has an On Open command that clears the cells of their contents automatically. I've been importing external data and SAVE ASing all week with no problems. BUT I accidentally SAVEd CalendarTemplate with data still in it (even the mastermind forgets once in a while...and I temporarily disabled the code that prevents users from SAVEing the file). Just now, I reopened CalendarTemplate. It cleared its contents on open (as directed). But, when I tried to do Data, Import External Data, the Import Data option was greyed out and a new Refresh Data option appeared. (There's more than one question coming up...I'll split points appropriately if necessary...) Did Excel "know" it had Imported Data last time CalendarTemplate was saved? Did Excel "know" where it had Imported the Data from? (When I clicked Refresh Data, it did import the right data.) Can I make Excel do that again? How? I can bully my users into always exporting the Outlook data to a specified file name (like CurrentCalendarData) and I can tell them to save to the folder that contains CalendarTemplate. Can you/I/we/theWorld actually "tell" a workbook to go get its own data from an external (albeit Excel) source without getting users involved? WOW. Comments, feedback, brilliant coding? Amazed Kitty At 10:04 a.m., byundt suggests to Kitty that she record a macro in Excel to produce the VBA code needed to import the data. Kitty tries it out and produces the ImportCalendarData macro. Kitty decides to leave the new macro outside of SuperMacro on the grounds that it will be easier to detect problems with the data import if it is run separately. Kitty does have concerns about “hard coding” the location of the CurrentCalendarData file into the macro. Experience has shown Kitty that it is often difficult to “tell” the computer on which of three drives a file was located. A few messages back and forth involving Experts byundt and ShaneDevenshire explain to Kitty how to get around the hard-coding of the location problem: recreate the ImportCalendarData macro from Kitty’s computer at the Office. byundt’s instructions: When you are recording the macro, please use the File...Open menu item and choose My Network Places in the "Lookin" field. You can then explore to find the file you need (even if it is on your computer). The macro will then record a path like //KittyTravel/prettykittyq/C:\BossStuff\CalendarData.xls This path can then be used by any other computer running the macro without worrying about whether it is the Z drive or J drive. This is the same point that Shane was making, and it's a good one. Kitty thanks her Experts and sets this info aside for testing when she gets back to the Office. The last tweak to the Great Calendar Project involves putting in a few custom buttons so that users will not have to think very hard to get the Outlook data imported and the Excel data cleaned and ready to import from Access. Creating the custom buttons is easy enough: Tools, Customize, Macros, Custom Button accomplished that. Kitty assigns a picture of a key to the “ImportCalendarData” macro and a happy face to the “SuperMacro” button. Amazingly, instructions to users on how to import and clean the data in Excel are now down to four lines: 1. Open the Excel file “CalendarTemplate” 2. Click the button with the key. 3. Click the button with the happy face. 4. Close Excel. You can’t get much more goof-proof than that. Current time to produce the document: 7 minutes (down from 12 minutes) The macro buttons look and run great but...Houston, we have a problem. The first time Kitty runs the macros from their buttons in CalendarTemplate, all is well and it looks great. The second time she opens CalendarTemplate and tries to run ‘em again, Excel tells her it ExcelCalendar (the file that will eventually contain the cleaned data) is already open. Kitty asks Excel, “Why do you care if ExcelCalendar is open? You are going to create a new version anyway.” Excel refuses to run Kitty’s nice new macros and stubbornly continues to tell her ExcelCalendar is already open. Frustrated, Kitty deletes the old version of ExcelCalendar and tries again. This time, Excel informs Kitty, “ExcelCalendar not found.” Kitty tells Excel, “I know it’s not found, you stupid program. I just deleted it. What’s this big obsession with ExcelCalendar anyway?” Excel again refuses to run Kitty’s nice new macros. Kitty knows the problem isn’t with the macros since she hasn’t edited them recently and they were all working a few minutes ago. She opens Visual Basic Editor to check to see if they’re still there: yep, they are. Confused, Kitty decides to check the buttons to see if they got messed up somehow. To Kitty’s surprise, “someone” has recoded her macro buttons so that they are now assigned as “ExcelCalndar.xls!SuperMacro” and “ExcelCalendar.xls!ImportCalendarData” instead of “CalendarTemplate.xls!macroname” Totally bewildered, Kitty reassigns the macros to CalendarTemplate. This time the macros run. Kitty closes the files, then reopens Excel to try again. Houston...we’re on a collision course. Excel once again tells Kitty that ExcelCalendar is open already. This time, Kitty checks the macro button assignments – damned if they haven’t been reassigned from CalendarTemplate to ExcelCalendar again. Completely frustrated, Kitty runs to Experts Exchange and calls for help: Solution Title: Why does Excel keep reassigning my macros to a different workbook? asked by prettykittyq on 10/16/2004 02:41PM EDT This solution was worth 300 Points and received a grade of A I have a workbook called CalendarTemplate which contains a number of nice EE-created macros. One macro imports data into the open CalendarTemplate workbook and a second macro saves the now-data-filled CalendarTemplate under the name ExcelCalendar. Good so far...that's what it's supposed to do. But, if I open CalendarTemplate a second time, it tells me it can't access ExcelCalendar (which now exists from the prior run) because it's already open. I tried deleting the previously saved verson of ExcelCalendar, figuring it can't complain about a nonexistent file being open. Now it tells me it can't find ExcelCalendar. That's not surprising since I killed ExcelCalendar. After tinkering around a lot with it, I finally realized Excel has been busily reassigning the location of half of my macros in CalendarTemplate such that it goes looking for them in ExcelCalendar instead of just getting them from CalendarTemplate where they were to begin with. In other words, when I create a button to run macro KillB in CalendarTemplate, I think its location is CalendarTemplate\KillB. Excel independently decides macro KillB is REALLY located in ExcelCalendar\KillB. Then it either sends me a message that it can't access ExcelCalendar because it's already open (and locked) or it can't find ExcelCalendar because I deleted it. How do I make it stop doing that? Frustrated Kitty An hour later, the following exchange of comments appears: Comment from ShaneDevenshire Date: 10/16/2004 03:37PM EDT Comment Hi, The problem is your toolbar buttons which are linked to the old file. When you change the name of a file using Save As the buttons tracks the new file name. I suggest that you put the macro into the Personal Macro Workbook, not into the template file, that way the button will always be able to find the file since you don't change its name or location. Shane Devenshire Comment from prettykittyq Date: 10/16/2004 03:40PM EDT Your Comment Hey, Shane! What you've said sounds about reasonable, but I'm not the sharpest knife in the drawer so I need a bit of coaching. Where is the Personal Macro Workbook? Will I be able to transfer that info to another computer? (I'm working at home but the Great Calendar Project will eventually reside on the agency's computer system.) Can/should I move all of my macros over there? Do I need to reassign the buttons when I'm done? Thanks for the help thus far. Kitty Accepted Answer from ShaneDevenshire Date: 10/16/2004 04:55PM EDT Grade: A Accepted Answer Hi Kitty, "What you've said sounds about reasonable, but I'm not the sharpest knife in the drawer..." Well I haven't heard that one before, kind of cute. Easiest way to do this is to choose Tools, Macro, Record New Macro, and pick Personal Macro Workbook from the Store macro in box. Then turn off the recorder. In the Visual Basic Editor you will find a Personal.xls project file and you can put all necessary code in there. The personal macro workbook is handled by Excel. Everyday when you open Excel, it looks to see if this file exists and them opens it. By default the workbbook is Hidden on the spreadsheet side, although you can unhide it. When you close Excel you will be asked to save any changes to the Personal Macro Workbook, but you will not be asked where to put it, Excel puts it in the XLSTART folder. This file is a good place to put all general purpose macros, ones you want to use from more than one workbook. But I wouldn't put file specific macros there in general. A more advanced way to handle these types of general purpose macros is to create and attach Excel add-ins, XLA files. But I would start with the Personal Macro Workbook for now. Shane Devenshire Comment from prettykittyq Date: 10/16/2004 05:24PM EDT Your Comment Shane! I did find the Personal Macro Workbook as you suggested and have copied all my little macros into it. I don't know that that's going to solve my problem though. I have up to six users operating on up to seven different workstations who are going to be trying to use my workbook to create Kitty's Great Calendar Project and they're all going to need access to those macros so having them in MY personal macro workbook is not going to do us much good. I did try creating an add-in XLA file but I don't understand how to attach it to the workbook. It shows up in the add-in manager but the macros inside the XLA file don't show up in Tools, Macros, Macro so I can't use them. Well, I'll pursue the XLA matter in another question. Since you did solve the problem I posed, points are on their way and I thank you kindly. Kitty Comment from ShaneDevenshire Date: 10/16/2004 05:28PM EDT Comment Hi Kitty, I'll get back to you on these things when I return home from my bike ride,. Shane Comment from prettykittyq Date: 10/16/2004 05:40PM EDT Your Comment BTW, the Personal Macro Workbook solution does look fabulous. I got my husband to give it a test run. Clicking on two buttons completed the Excel end of this project -- elapsed time about 10 seconds. To put this in perspective, without macros and working manually, it took me about 45 minutes to import, clean, and save the data. So I do appreciate your help. Hope the bike ride was fun. Kitty Kitty implements Shane’s suggestions and it resolves the problem. She’s still concerned that storing the macros in the Personal Workbook might not be a long-term solution since it will be requires copying the macros to the Personal Workbook of every person at work who is going to run the program. Fortunately, byundt came back with a long-term acceptable fix. Comment from byundt Date: 10/16/2004 06:08PM EDT Comment Hi Kitty, One drawback to add-ins and personal.xls files is the fact that every user has to have their own copy of them. So it can be somewhat cumbersome when they need updating. With your existing approach, all the required code is self-contained in the CalTemplate file. So updating is simple--change one file on the network, and everybody gets to take advantage of it. Back to your original question: it is also possible to copy the active worksheet and make it into a new workbook. If you do that, the old workbook doesn't change its name--and your macros don't get detached from their buttons. The sub below shows two methods to accomplish that goal. The first method (one statement that is commented out), saves a copy of the entire workbook with the name CalendarData.xls -- but the original file never gets renamed, and ExcelCalendar.xls is never actually open. Using this approach, the macros in the original workbook get get copied into ExcelCalendar.xls The second method (the code not commented out) saves a copy of just the active sheet with the name CalendarData.xls. Once again, the original file never gets renamed. Since the macros are in a module sheet, they do not get copied into ExcelCalendar.xls. This may be looked at as either an advantage or disadvantage--depending on your situation. byundt's solution becomes a new macro SaveMyFile Kitty opts for saving the worksheet (without the workbook modules) since the worksheet never really does get opened up again. She comments out her original macro SaveMyFile and plugs this code in its place. A few test runs confirm that All The Code works the way it’s supposed to and Kitty’s Great Calendar Project is at an end. Click here to roll the credits...	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