Projects_ver2.xls

Projects_ver2.xls is a copy of the new Projects.xls workbook. Grantees who already have data in the CPMP 1.3 Projects.xls workbook have two options: 1) copy and paste data as necessary into the new workbook, or 2) update their existing workbooks to include the new functionality. Instructions for the latter are provided below.

Grantees that choose to update their existing workbooks to include the new functionality will be required to run a macro. This macro must be run against each projects worksheet (ex. Project (1), Project (2), etc) within the Projects.xls workbook. After all projects worksheets have been updated, grantees should review their workbook to verify changes before saving.

NOTE: The macro should NOT be run against the Section108Projects worksheet.

Screen shots and instructions provided below are from MS Excel 2000. Screens and prompts may differ slightly for other versions.

To Incorporate the New Projects.xls Functionality into an Existing Projects.xls Workbook:

1. Download CPMP 2.0.
   
   
2. Follow the instructions for downloading CPMP 2.0, including extracting the files.
   
   

   Important! It is highly recommended that you extract the CPMP Version 2.0 files to your LOCAL HARD DRIVE to run the macros. If the files need to be housed on a network, they can be copied to their permanent destination after the data migration.

   
   
3. Once CPMP 2.0 is installed on your computer, copy your current Projects.xls workbook to the folder named
   Ver2.0_Data Migration, which is located in the same folder to which you extracted CPMP 2.0.
   
   

   Note: It is recommended that you close all other applications, other than Microsoft Excel. Therefore, printing this page to complete execution of the macro is suggested. Also, if you have Excel's Auto Save feature installed (under the Tools menu), it should be turned off temporarily.

   
   

   Important! It is important that you COPY your existing Projects.xls workbook to this folder. This will allow you to review the changes before saving and revert back to the original should you encounter difficulty.

   
   
4. You must now open two Excel workbooks from the Ver2.0_Data Migration folder:
   1. First, open Projects_ver2.xls.
   2. Next, open Projects.xls.
   
   
   

Note: You may receive 2 prompts for each workbook. Click Enable Macros for each workbook and click No to update links to other workbooks. (If you are not prompted to enable macros, ensure your security settings are set at Medium.)



5. In Projects.xls, select the first project worksheet (Project (1), Project (2), etc) into which you want to add the new functionality. Verify that only one worksheet is selected. The worksheet tab will be white.
   
   
6. On the Menu bar, select Tools. Then select Macro, and Macros from the submenu.
   
   
   
   
   
7. In the Macro dialog box, select the Projects_ver2.xls!mcrImplementChanges macro, then select the Run button.
   
   
   
   
   
   
   
8. The macro should complete within a couple of seconds. You should see the following message, "Data migration complete. Run macro again for remaining Projects worksheets."
   
   
   
   
   
9. Click OK to the message and verify the following changes were made to the projects worksheet you selected:
   
   The National Objective Code field and the Project Primary Purpose check boxes should have been removed and replaced with the Objective Category radio buttons and the Outcome Category check boxes.

   Fields that were removed:	Fields that were added:

   
   
10. Select the next project worksheet and follow steps 6 through 8 to repeat this process.
    
    
11. Continue this process until all project worksheets in Projects.xls have been updated.
    
    

    Note: It is recommended that you take your time with this process. If you try to go too fast, it is likely that you will encounter errors. If you have a large number of project worksheets, it is also recommended that you save Projects.xls periodically (e.g., after migrating data for 10 projects).

    
    
12. Choose File | Save.
    
    
13. Close Projects.xls and save the changes. Also close Projects_ver2.xls and choose No when prompted to save changes.
    
    
14. You may now copy Projects.xls up one folder to the CPMP_ver2 folder. Choose Yes to overwrite the Projects.xls in the folder.
    
    

Important! It is important to save often when running this macro against a large number of projects worksheets. If you encounter an error while running this macro, stop and contact the TAU for assistance! The macro may seem to have worked, but it hasn't completed correctly. Please do not continue to run the macro regardless of an error.