MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Major Housekeeping problem - Here's a real challange


Posted by Mark M. on June 13, 2001 10:39 AM

I am trying to automate some weekly housekeeping chores in file manipulation. I have created exact workbooks for 10 different employees, each being named for the employee. The task at hand is to copy these workbooks from different locations to other locations, rename the workbooks, extract information from the original workbook to a weekly report workbook, clean up the original workbook and save and close all workbooks. It should go in the following flow.
1. Copy Network Drive\Employee1\Employee1.xls to Local Drive\My Documents\Files.
2. Repeat Step 1 nine more times for nine other employees. Each file having a different name.
3. Copy Network Drive\Employee1\Employee1.xls to Network Drive\Employee1\Archive folder.
4. Rename Network Drive\Employee1\Archive\Employee1.xls to Network Drive\Employee1\Archive\(today's date).xls.
5. Repeat Steps 3 and 4 nine more times.
6. Open Network Drive\Employee1\Employee1.xls.
7. Delete information in selected cells. C7:L80 and P7:P80.
8. Enter the current fixed date in cell E2. Can not use TODAY() or NOW() since they will change in the future.
9. Save Network Drive\Employee1\Employee1.xls.
10. Close Network Drive\Employee1\Employee1.xls.
11. Repeat Steps 6 through 10 nine more times.
12. Open Report workbook which will extract information from files copied in Step 1.

Any help in this problem would be greatly appreciated. I have recently started getting involved with programming, but am learning a lot very quickly with the challenges so far. Thank You.


Posted by Richard S on June 13, 2001 11:38 PM

Steps 6 through 12 you should be able to record a macro which does each step. Just go to Tools, Macro, Record, and do all those steps, then stop recording. The rest of the stuff could be done using a DOS batch file with a prompt for a variable being the days date, which could fire up from Explorer.