The workflow is described in the PowerPoint file below. Upload of shore changes to DESClogik is not necessary anymore.

DESClogik asset cleanup by Excel macros

What the macro does:

1. Opens each spreadsheet in a folder of your choice.
2. Standardizes font, font size, font color; fits column widths to text; removes split screens or frozen rows; removes all colored cells; sets worksheet zoom level to 100%, and removes all bracketed classifications.
3. Flags duplicate samples and their offsets in an extra column to the left of the main data. If no duplicates are detected, the extra column is deleted.
4. If there is nothing below the header row (worksheet not used), inserts the phrase “No data.” in cell A2.
5. Saves and closes the spreadsheet. Continue until all spreadsheets have been processed and then issue a completion message to the user when finished.

How to use the macro:

1. Open any spreadsheets that were flagged as changed during the post-cruise meeting. Apply the color codes as needed. IMPORTANT: If there is a color code key in the first row, delete that row.  The macro is designed to work on spreadsheets with the header in the first row.
2. Create a copy of your DESC spreadsheets; this is where you will point the macro.
3. Run the macro.
4. Open all the processed spreadsheets and check for duplicate samples. Speak with the EPM on how they want the duplicates resolved.
5. Delete the “dupes” column after you resolve the issues per instructions; the color coding will disappear when you do.
6. Upload to the database using the DESC asset uploading template.
7. Send an email to Rakesh to flag your uploaded files for moratorium access.
8. Email the appropriate Pubs Specialist, EPM, Lorri Peters, and David Houpt when you are finished.

Done!

The macro: DESC_reformer.txt

For questions see Margaret or Sandra.

Archived Versions