Management Report Data Transfer Program Instructions

Last updated 9/16/2003   03:39 PM version 1.24
By Kenrick Mock

This is a work in progress!  Bug-fixes and updates will be posted here as changes are made. Since this is the first time this program is being used by more than one person, there may likely be bugs or steps that are unclear. Thanks for your patience.

Recent changes:

• 9/16/03 - Support for Other revenue sources - Private
• 1/08/02 - Support for short Revenue sheets that would not copy
• 1/08/02 - Checks for changing fund and org immediately after "Total"
• 1/31/01 - Modified program so that it will still operate if text becomes double-spaced
• 1/27/01 - Added support for "Resale", "Mandatory Transfers", and "Interest Income".
• 1/24/01 - Added macro to clear data from the detail spreadsheet
• 1/24/01 - Updated documentation regarding empty rows, fund, new versions

The following instructions illustrate how to install and operate the data transfer program in conjunction with Excel.  These instructions have been written for Excel 2000, but should work for other versions.  For questions, contact Kenrick Mock (afkjm@uaa.alaska.edu) or Marsha Oberlender (anmlo@uaa.alaska.edu). 

Download the program from the web

1. First, download the data transfer program and save it to your local disk by right-clicking on the following link:  modTransferData.bas.  You only need to do this once, so save the file in a good place.
2. If you are upgrading from a previous version of the program and already have it installed, it is recommended that you first remove the old version before adding the new one.  From Excel, hit Alt-F11 to go to the Code View, and then find modTransferData.bas from the Project window in the upper left.  The modTransferData.bas file will be under Modules.  Right-click it and select remove.  If Excel asks you to export the file, select no. This process is illustrated in the figure below.

Download Data and Configure Excel

1.  To prepare a management report for data transfer, first start Excel and add two worksheets to the Management Report file. Name the worksheets Web Expense Summary and Web Revenue Summary .
2. Under the Finance Systems Web site (http://www.finsys.uaa.alaska.edu/businessservices/), select the Fiscal Year Expense and Revenue Summary Reports.
3. Open the cycle Expense Summary Report in Excel format.
4. Select and copy the headings (first 2 lines);  paste into the Web Expense Summary worksheet in the Management Report.
5. From the Web Summary Report, select and copy the rows that contain your org. numbers; paste into the Web Expense Summary worksheet in the Management Report.   There should be no blank rows inserted in the worksheet .  In particular, make sure that column G has no blank rows down to the end of the worksheet. The program uses this to compute the end of the worksheet.
6. Double check to see that 'Fund Number' is in column C and that 'Org Number' is in column E.  Insert another column if necessary.
7. Open the cycle Revenue Summary Report in Excel format.
8. Repeat steps 4, 5, and 6, pasting into the Web Revenue Summary worksheet.
   

 Setting up the Program

1. Start with the Web Expense Summary, Web Revenue Summary, and a blank Management Report Spreadsheet (detail worksheet) loaded into Excel.  Each worksheet must be named Web Expense Summary, Web Revenue Summary and detail.   The detail sheet is the destination spreadsheet and should contain no budget data.
2. Hit Alt-F11 to go to the Code View (also accessible from the menu via Tools, Macro, Visual Basic Editor). 
3. Click on VBAProject under "VBA Projects" in the upper left window, as shown below.
   

   Select F)ile, I)mport and choose modTransferData.bas from the location you saved it to.

4. Select T)ools, R)eferences and make sure that Microsoft Scripting Runtime is checkmarked.   If you do not have this option on the list of references, then you must download and install the Windows Scripting Host.  It is available from: http://www.microsoft.com/msdownload/vbscript/scripting.asp 
5. If your general fund is not 104110, then double-click the modTransferData.bas file under the Project window under Modules.  Change GENERALFUND = "104110" to GENERALFUND = "XXXXXX" where XXXXXX is the fund you want. See the image below for an example.
   
   
6. Hit Alt-F11 to go back to the spreadsheet view (or just close the Code View window).

Note: If you save the detail worksheet and use it again in the future, you don't need to repeat these steps.  The program will be saved when you save the spreadsheet.  If you re-load the program when it already has been loaded, the new version will have the number "1", "2", "3", etc. appended onto the end of the macro.  You may wish to delete the old macros before loading the new one.

Running the Program

1. Clear the detail worksheet of data.  There is a macro to help you do this.  Hit Alt-F8 and select ClearDetailSummary.  You will be prompted to enter the last row for which data is to be cleared.
2. Go to the Web Expense Summary worksheet and position the cursor in the row where you want processing to begin (e.g., row 3 to begin). 
3. Hit Alt-F8 to invoke the macros, and select TransferExpense 
4. If there is an error of some kind, a message box should pop up indicating what fund/org couldn't be found.  Either correct the error or to skip it, position the cursor on the next line to process, hit Alt-F8, and run TransferExpense again.  Repeat until the entire spreadsheet has been processed - there will be no dialog box if the macro successfully completes execution.  If there is a problem, a dialog box will print a row number but note that this will be the row of the next record after the problematic one.   If you are ignoring the errors for now, you may wish to make a note of them so that the numbers can be balanced on the final worksheet.
   
   Each time the macro is run, be careful the cursor is position on the row that you want processing to begin.  Otherwise you may skip some data.  It is okay to run the macro multiple times on the same data, the program will simply be re-copying information over itself.

5. Go to the Web Revenue Summary worksheet and position the cursor in the row where you want processing to begin (e.g., row 3 to begin). 
6. Hit Alt-F8 to invoke the macros, and select TransferRevenue. 
7. Repeat step 6 above for any errors, positioning the cursor on the proper row where you want processing to begin.
8. Check the final spreadsheet and check for correctness!

Final Notes

If you save your spreadsheet and open it later, you must enable macros if you want to re-run the program.  Since many viruses also travel in the form of macros, it is a good idea to have a virus scanner handy to identify them.

If you wish to give the program a test run, try it on the following sample spreadsheet:  sample.xls   The spreadsheet contains several anomalies that will be detected by the program.