How do I save my workbook as a template?

Background

Excel templates have a special file type that designates them as a template.  The advantage of using a template is that the default action associated with a template is to create a new workbook based on a template but not open or modify the template itself.  The design of the template is therefore preserved.

Saving a workbook as a template can be as simple as selecting Excel Template in the Save as type drop-down in the Save As window.  This article addresses some additional steps related to Office Connector and how you can control the experience of the user who creates new workbook from your template.

Overview


Step 1 - Remove Data from Workbook

In most cases, a user will not expect that a workbook created from your template to already contain data values.  You will therefore want to consider removing any actual data.

Option 1 - User Parameters Resulting in No Data
The conditions used in your workbook's functions and queries may depend on the values in certain cells (parameters).  If this is the case, placing certain values in these cells may result in your workbook displaying no information.  For example, the cell for the Job Number or Property ID might be left blank for this purpose.  Date parameters could be set to a value such as 1/1/1901 as an example.  After assigning the parameter values, click Refresh Timberline Data on the Office Connector toolbar.

Option 2 - Use a "Blank" database
A blank database would be a Sage 300 Construction and Real Estate database that contains each of the possible data files but does not contain any records.  Such a database is available upon request from Event 1 support.

  1. Click Open Timberline Data Folder on the Office Connector toolbar
  2. Select the Blank data folder


Step 2 - Disable Automatic Update of Links

The Office Connector add-in is treated as a linked file by Excel because of Office Connector worksheet functions.  When opening a workbook that contains links to other workbooks, Excel may display prompts about updating the location of linked files. Office Connector handles updating of its own link automatically and so this prompt is not necessary and often undesirable. The objective of this step is to prevent the prompts from occurring and allow Office Connector to update its link automatically when and if necessary. Keep in mind this step is not necessary if all users of your template will have Office Connector installed to the same location on their computer. Also, you may want to consider another option (in the Startup Prompt window) if your template contains other links to other workbooks.

Excel 2010 to current

  1. Click the Data tab on the ribbon
  2. Click Edit Links
  3. Make sure OfficeConnector.xla is selected under Source
  4. Click Startup Prompt
  5. Select Don’t display the alert and don’t update automatic links
  6. Click OK
  7. Click Close


Step 3 - Edit Workbook Properties

Certain custom workbook properties control the behavior of a new workbook that is being created from a template.  Some of these properties are automatically set by Office Connector. The objective of this step is to change these parameters so that Office Connector has the desired behavior when creating a new workbook from your template.  To access the custom workbook properties, follow these steps:

Excel 2010 and higher

  1. Click File
  2. Click Properties
  3. Click Advanced Properties
  4. Click the Custom tab

Address each of the following workbook properties

TimberlineDataFolder - This property identifies the path of the Sage 300 Construction and Real Estate data folder associated with the workbook. Since a template should not identify any specific data folder, this property must be removed. When creating a workbook from the template, the absence of this property will cause Office Connector to prompt for the Timberline Data folder.

  1. In the Properties list, select TimberlineDataFolder
  2. Click Delete

OCVersion - This property identifies the last version of Office Connector used with the workbook. Leaving this property in the workbook can result in the following message being displayed if the user who is creating a workbook from the template is using an older version of Office Connector:

"The workbook appears to have been opened in a newer version of Event 1 Office Connector. You could possibly experience compatibility issues if this workbook uses features that were developed after your version. Compatibility issues can be resolved by upgrading to the current version of Event 1 Office Connector".

In most cases, workbooks are fully backward compatible. If you know that your workbook does not utilize any features that are unsupported in the versions of Office Connector being used by the target users, you can remove this property. Removal of this property will therefore prevent the above prompt from appearing unnecessarily.

  1. In the Properties list, select OCVersion
  2. Click Delete

FirstRefresh - This property determines whether or not Office Connector will refresh data immediately after allowing the user to select a Timberline Data Folder. If your workbook contains parameter cells where the user would first need to enter values before refreshing data then setting this property may be advantageous. This prevents the unnecessary delay of having to wait for queries to refresh (and return no results) before the user can enter their parameters. The exception would be if one or more of the parameters depend on a list that is queried from the Timberline data (such as a Job or Property list used in a drop-down list). In this case, you would not want to include this property because it would prevent the drop-down list from populating. Follow these steps to include this property for workbooks that should not immediately refresh data upon creation and selection of a data folder:

  1. In the Name box, type: FirstRefresh
  2. In the Type box, select: Text
  3. In the Value box, type: Manual
  4. Click Add

Step 4 - Set the Active Cell

You will want the workbook to open to the worksheet where the user should start and have the first cell where the user might need to enter a value already selected. This will help to remove any doubt for the user about where they should start.

  1. Click the worksheet that should be displayed when a new workbook is created from your template
  2. Click in the cell that should be selected


Step 5 - Save As Template

The last step is to save your workbook as a template.

Excel 2010 and higher

  1. Click File and then Save As
  2. Click This PC and then Browse
  3. In the Save as type box, click one of the following options
    • Excel Template (*.xltx) - Use this option if users of your template use Excel 2010 or higher and your template does not contain any Excel macros.
    • Excel Macro-Enabled Template (*.xltm) - Use this option if users of your template use Excel 2010 or higher and your template does contain Excel macros.
    • Excel 97-2003 Template (*.xlt) - Use this option if users might use either Excel 2003 (or prior) or Excel 2007.
    Note that the folder identified in the Save in box automatically changes to Excel's default templates folder. Be sure review this or change to the desired folder where you want to store your template.
  4. Click Save

IMPORTANT - When you click Save, the following prompt will be displayed:

"This workbook contains external data. Do you want Microsoft Excel to clear the data before saving the template, and then automatically refresh the data whenever the template is opened?"
Yes  No  Cancel

Always click No in response to this prompt. This allows Office Connector to reconnect the workbook to your Sage 300 Construction and Real Estate database before any queries are refreshed.

Tags:

Cart

"In today's economic times, it is imperative that we are confident in the information we provide to our company and our customers. To achieve this, we rely on Office Connector. Office Connector enables Excel to instantly gather and write information that would otherwise take hours or even days to assemble."

B. Morse, The Gorman Group