Creating a Custom Microsoft Excel Template File

You must be familiar with using Visual Basic for Applications (VBA) in order to create or modify a macro within Microsoft Excel.   The menus to open and therefore the steps to follow within Excel will vary depending on the version you are using.  The following procedure is provided as a guideline rather than a set of steps to be followed.

 

To create a custom Microsoft Excel template file:

•    Navigate to the Template directory within the VTS installation directory (e.g. C:\VTS\Template\).

•    Open the template file you wish to use (Example1.xlt or Example2.xlt). (Please refer to the preceding section, Using a Microsoft Excel Template File to Generate Reports, for information on each of the template files.)

Be careful that you do not create a new file based on this macro instead of opening the macro for editing. Right-click on the file name and be sure to select Open rather than the default option, New.

•    If prompted to enable macros, do so.

•    Press the Alt-F11 key combination to open the VBA development environment.

•    Locate Module1 in the Project area.

•    Click to open the code window to edit the macro named Complete().

 

 

•    Modify the macro as you require. 

One way to do this that requires only a cursory knowledge of VBA programming is to start with a report that is saved to an unformatted Excel file.  Turn on the macro recorder and adjust the report as you like, formatting headers, adding sub-totals, etc. When the report looks the way you want, stop the macro recorder and examine the code that was created to learn how each step of the formatting process is coded into VBA.

•    Expand the File menu and select Save.

•    Close the VBA development environment window.

•    In Excel, select Save As from the file menu.

•    Navigate to your application directory (e.g. "C:\VTS\MyApplication\).

•    Enter a name for the template file in the File name field.

•    Close the custom template file.