Introduction
Sometimes you create a template with {skip} tags and/or {hop} tags throughout. You then have an Excel Job that sends the data to the spreadsheet skipping and hopping accordingly. But what if you don't send as much data on one of your runs? You may end up with some extra {skip} and {hop} tags since they aren't removed until the Buddy encounters them during export.
To get rid of these, we created a simple macro called "Cleanup". All you have to do is tell the Buddy Job to run this macro after the tag replacements and it will clear out any remaining tags. Below are the steps to accomplish this.
NOTE: This has become an option in the Excel Job Wizard in later versions of Buddy.
Steps
- Download the "Cleanupmacro.txt" file attached to this article.
- Open the Excel spreadsheet you are using as the template for your report.
- In Excel 2003, choose Tools > Macro > Macros...
In Excel 2007, choose down arrow in the Macro button from the View tab of the ribbon.
- Type "junk" in the text box at the top of the Macro window and click Create.
- This will open the Visual Basic Editor and show the Junk Subroutine. Highlight all text in the window including Sub junk() and End Sub and delete it.
- Highlight all text in the CleanupMacro.txt file from this article and paste it in the window.
- Save your template spreadsheet and close it.
- In the SPC Office Buddy, edit your Excel Job by highlighting the job in the list and clicking the pencil icon
- Click Next >> 3 times until you reach the Excel Options screen.
- Check the box that says Show Advanced.
- Click Next >> to go to the Advanced Options screen.
- Type "Cleanup" in the text field called Run Macro After.
- Click Finish to save your Excel Job
When you run the job, it should automatically run the Cleanup macro when the tag replacements are finished. This macro will clear the remaining {skip} and {hop} tags leaving your finished report nice and clean.