Background
Once you have created a workbook containing Office Connector queries and functions, you may wish to deploy it to other people or companies. This article covers the recommended steps and things to consider that will create a smooth experience for the recipient who will create workbooks based on your template.
Steps
Step 1 - Remove live data
Step 2 - Change how links will be updated
Step 3 - Set workbook preferences
Step 4 - Save as template
Step 1 - Remove live data
As a template that people will use to create new workbooks, you typically won’t want the workbook to start out with data already in it. There are two methods you can use to remove the existing data:
Method 1 - If your workbook includes parameters that control which records are shown (such as a date range, a job number, property ID, etc), you can enter values for the parameters that result in no information being returned. For example, enter a date range of 1/1/1901 to 1/1/1901 or enter a blank job number (or both).
Method 2 – You can obtain a copy of the “Blank” Sage 300 Construction and Real Estate database from Event 1 support. This is a set of Sage 300 Construction and Real Estate data files that contain no data. You can then select this data folder and refresh the data in the workbook.
Step 2 - Change how links will be updated
This step is important if you might be providing the workbook to users who would have the Office Connector add-in installed to a location different than yours. For example, if your Office Connector software is installed under “C:Program FilesEvent 1Office Connector” but other could have it installed elsewhere (such as “M:ProgramsEvent 1Office Connector”), you will need to complete this step. Failure to account for this, will result in the other user receiving a prompt about updating links to other workbooks when they create a workbook from your template.
Excel 2002-2003
1. Click the Edit menu and click Links
2. Click Startup Prompt to display the startup prompt options
3. Select Don’t display the alert and don’t update automatic links
4. Click OK
5. Click Close
Excel 2007
1. Click the Data tab on the ribbon
2. In the Connections group, click Edit Links
3. Select Don’t display the alert and don’t update automatic links
4. Click OK
5. Click Close
Step 3 - Set Workbook Preferences
1. Display the Workbook Properties window
Excel 2002-2003
Click the File menu and then click Properties
Excel 2007
a) Click the Office button and click Prepare and then Properties
b) Click the drop-down button next to Document Properties (on the orange information bar) and select Advanced Properties
2. Click the Custom tab
3. Remove each of the following properties by clicking on it in the list at the bottom and then click Remove
TimberlineDataFolder – Alternatively, you can change the value of this property to Prompt to force Office Connector to prompt for the Sage 300 Construction and Real Estate data folder as soon as the template is opened.
OCVersion – Delete this property if you think you may be running a newer version of Office Connector than users who you will provide the template to and you know that you are not making use of new features they would not have.
Step 4 - Save as a template
The final step involves saving your workbook as an Excel template. An Excel template differs from an Excel workbook in how it responds when you double-click on it via Windows Explorer. Double-clicking on an Excel workbook results in opening the workbook. Double-clicking on an Excel workbook leaves the template untouched and creates a new workbook based on the template.
Excel 2002-2003
1. Click the File menu and click Save As
2. In the Save In box, select the folder where you want to store your template
3. In the Filename box, type the desired filename for your template
4. In the Save as type box, select Excel Template
5. Click Save
6. Click No when prompted with the following message:
This workbook contains external data. Do you want Microsoft Office Excel to clear the data before saving the template, and then automatically refresh the data whenever the template is opened? <Yes> <No> <Cancel>
Excel 2007
1. Click the Office button and click Save As
2. In the Look In box, select the folder where you want to store your template
3. In the Filename box, type the desired filename for your template
4. In the Save as type box, select the appropriate file type
Option 1 - For compatibility with Excel versions 2002 to 2007, select Excel Template (97-2003)
Option 2 - For deployment to users of Excel 2007, select Excel Template (*.xlsx) or Excel Macro-Enabled Template (*.xlsm) if your template includes macros.
5. Click Save
6. Click No when prompted with the following message:
This workbook contains external data. Do you want Microsoft Office Excel to clear the data before saving the template, and then automatically refresh the data whenever the template is opened? <Yes> <No> <Cancel>
Other Considerations
Version Control – Include the revision number or date/time of the latest revision to the template somewhere inside the template. This will allow users to confirm that they are using the most up-to-date version.
Storage – Make sure to place your templates in a location that will not only be easily accessible by those who need it, but also a location where it will be included as part of your regularly scheduled backups.
Template Information – Using the Event 1 templates as an example, consider adding an “Information” worksheet to your template that includes details about its purpose, how to use it, and who to go to for additional information.