Editing an Enviro Data Template File

Enviro Data provides many custom templates for use in formatting reports. These are stored in the C:\Enviro\EDate8\Templates folders and can include company logos, custom fonts, margins, etc. These template files are in Excel Macro format. Instruction for modifying one of the template files using Excel is below. These templates contain Visual Basic for Applications code that is easily broken if you do not know what you are doing. However, if you are not comfortable making changes, Geotech will make changes for you under your paid support plan. 

For using Crosstab Template see How can I create a custom header and footer on my tables in my Crosstab Exports? under Using Enviro Data --> Crosstab Export in the FAQs..

Before starting to edit a template, MAKE A BACKUP COPY! This can be done by opening it in Excel from and use File --> Save As appending the original name with BACKUP to the end of the filename. You could include the date if desired.

Configuring Excel Developer Mode

To change the font and font size in the data rows of the export open the XLSM template file in Excel. These changes will be done using Excel’s Visual Basic Editor. On the Excel menu you will need to open the Developer tab in Excel. If the Developer tab is not displayed by default, you can add it to the ribbon using these simple instructions. Go to File --> Options --> Customize Ribbon then on the right, select Developer and click OK.

Figure 1. Adding the Developer Menu to Excel to allow for editing Crosstab templates.

Saving and Compile After Changes

Whenever you make changes, especially to the VBA code, you must ALWAYS Save first and then Compile the template file. It also does not hurt to click Save at any time but before you close out, it needs both. For crosstab reports you also need to add the file name and description to that database.

Figure 2. Debug Complie Menu

TO save your changes click on File --> Save. Next, Click on Debug and then Compile VBA Project. 

.Making Changes

When you open a template, you will see two workbooks, one for TitleRows (header) and one for Footnotes (footer).

Figure 3. Multiple workbooks in Excel template file. 

These workbooks are where you can make modifications to the text and/or add or remove rows. When you are finished making modifications make sure you Save As in the C:\Enviro\EData8\Templates folder\Xtab_Template_yourname.xlsm format.

NOTE: Both workbooks contain a cell in the bottom row of Column A with “/End” as shown below. These must be present at this location for the template to control printing and formatting.

Figure 4. "/End" in last row of each workbook

Even if you already entered a name for the crosstab in the Report Title text box at the bottom of the Crosstab Wizard form, the name of the template file takes priority.

Changing Fonts and Text

Click on the Developer tab in Excel and then click on Visual Basic on the far left of the ribbon.

Figure 5. Enter Visual Basic Editor in Developer Mode of Excel

Next, scroll down in the VBA code to the font section. Following the example below, change the font name and size as needed. Be sure to keep the font name in quotation marks.

Figure 6 Format of Font Code in VBA

Edit Margins in Templates

It is possible to modify the margins in any of the crosstab templates if needed. To do this first set the Margins (under Page Layout) desired in the TitleRows worksheet. Next, copy the following VBA code into Page Setup section at the bottom of the VBA module.

Margins
wks.PageSetup.TopMargin = Application.Worksheets("TitleRows").PageSetup.TopMargin
wks.PageSetup.BottomMargin = Application.Worksheets("TitleRows").PageSetup.BottomMargin
wks.PageSetup.LeftMargin = Application.Worksheets("TitleRows").PageSetup.LeftMargin
wks.PageSetup.RightMargin = Application.Worksheets("TitleRows").PageSetup.RightMargin

Make sure you Save the template and Compile it after making the above changes.

Editing Text in Header and Footer

We do not recommend changing headers and footers unless the client request this as this is easily done in the final product by your client To change fonts, font sizes and text on header and footer rows, highlight the rows on the TitleRows or FootNotes worksheet that you want to change and use the font and size dropdowns to customize the fonts.

Figure 7. Changing text in Headers and Footers in Excel Template file

The FootNotes worksheet can have up to 100 rows of notes. The last row that contains text on the TitleRows and FootNotes must be “/End”. This marker is used by the export to control printing and formatting.

Make sure to Save changes when finished and, if this is a new template see How can I create a custom header and footer on my tables in my Crosstab Exports? under Using Enviro Data --> Crosstab Export in the FAQs.