Downloading ELM NDN disbursement files into an Excel spreadsheet gives you the ability to sort and manipulate your data to meet your needs. Here’s how to use this handy tool!

Using CL MACROS.xls

  • Download the data file (usually a .DSB file) from the Mailbox. Save this file as a “.txt” file in any drive (c:\, shared drives, personal drive on LAN, etc). Remember the name and location of the file. It may be useful to create a subdirectory labeled “data rosters” and name each file the date of the roster so that you and staff members can easily locate the files; i.e., c:\data rosters\032201.txt.
  • Open Excel
  • Security Level: Go to Tools / Macro / Select Security and Choose Medium Security. Click OK. If you can’t gain access to Select Security please contact your local IT Department.
  • Open the CL MACROS.xls file.

The following dialog box will appear. Click the Enable Macros button.

This is how the CL file will appear once it is opened. Click on the appropriate button for your data file format. Using the wrong button may result in inconsistencies between the header of the column and the actual data, but it will not affect the data.

The macro begins by prompting you to enter the location of the file you wish to import into an Excel spreadsheet. Using the drop down box in the Look in: field or typing the drive and file name in the File name:field, indicate which file you will be importing into Excel.

If you have given your file an extension other than “.txt”, you will need to change the file type using the Files of type: drop down box.

After you have selected the file to import, the data will be placed into columns and labeled according to the CommonLine field names.

At this point, you can sort the data, subtotal, total, and count the data. You will be able to manipulate the data as allowed by Excel.

When using the “X” button on the top right corner of the screen to exit the spreadsheet or program, the following dialog box will be displayed.

If you do not wish to save the file, click “No.” Reminder: If you don’t save the file, the next time you wish to access the data file, you will have to import the file using the CL Macros again.

When you are ready to save your file, you will need to select the file type Microsoft Excel Workbook (.xls) from the Save as type drop down menu.You may use the same file name that you used when downloading it from the mailbox or you can assign the file a new name.

If you do not change the file extension to “.xls”, you will receive the following dialog box.

If you have any questions or need further assistance, please contact your ELM School Priority Specialist at our ELM Service Number: 866.524.8198.

1