X-VBA

XTemplate

XTemplate is a Microsoft Word, PowerPoint, and Outlook program and templating language used to easily pull information from a Microsoft Excel workbook into a Word Document, PowerPoint Presentation, or Outlook Email or Appointment. XTemplate is written in pure VBA, making it easy to install and use, has no dependencies, and is free and open source software. The full source code can be found at my github page or on the dowload page. XTemplate is also MIT Licensed, meaning it can be used for commercial and personal use with very little restrictions free of charge.

Usage

XTemplate is used by placing templates within a Word Document, PowerPoint Presentation, or Outlook Email or Appointment, with these templating pointing towards a cell within an Excel workbook. Then, once all templates are placed, simply run the XTemplate Macro, and if the syntax is correct and the Workbook and Sheets exist, then XTemplate will fetch the data directly from Excel. This makes creating and updating templated and frequently created documents much easier, as it saved time from having to manually pull the data from Excel by hand. For example, below is a Word Document and an Excel Workbook that we will pull data from:

The data from the Excel Workbook is pulled within the Word Document.

Syntax

The template within the Word Document example is:

{{ C:\Files\[Book1.xlsx]Sheet1!A1 }}

This template can be broken up into 4 parts:

File Path C:\Files\
Workbook Name [Book1.xlsx]
Sheet Name Sheet1!
Range Address A1

Essentially, this is the same syntax that Excel uses to reference cells from other Workbooks when using a formula in a different workbook:

Using this same syntax, we can create a template in our Document, Presentation, Mail, or Appointment, and after running the XTemplate Macro, the data will be pulled from Excel. For example, the template {{ C:\Files\[Book1.xlsx]Sheet1!A1 }} will fetch the value within cell "A1" in sheet "Sheet1", in workbook "Book1.xlsx" that is found within the folder "C:\Files\". It's that simple to use and can save a lot of time and reduce error when creating templated documents.

Absolute and Relative File Paths

The XTemplate syntax can be shortened in some cases by taking advantage of Relative File Paths. For example, the following is a valid template for XTemplate in Word and PowerPoint:

{{ [Book1.xlsx]Sheet1!A1 }}

This path is a Relative Path, and in this case XTemplate will look for "Book1.xlsx" within the same folder of the Document or Presentation we ran XTemplate in. If XTemplate was run within a PowerPoint Presentation in the folder "C:\MyPresentations\", then the template internally will be converted to:

{{ C:\MyPresentations\[Book1.xlsx]Sheet1!A1 }}

This makes it easy to further create templated files. For example, suppose you have an Excel Workbook that is run every month and is placed within a folder called "Jan", "Feb", "Mar", etc., and the data from this Spreadsheet is used within a Presentation for that month within the same folder, then you can simply use Relative Paths and when you need to create a new presentation the next month, you won't need to update the file paths within any of the templates. This makes XTemplate a very powerful tool that can save a lot of time and error.

Note on File Paths in Outlook

Since Outlook Mail and Appointments don't reside in a folder, you will always need to use Absolute Paths within Outlook.

Common Errors and Best Practices

Close the Workbook before running XTemplate

It is best practice to close the Workbooks you want to pull data from before running XTemplate. There are two reasons for this:

  1. Closing the Workbook ensures that it is saved and that the most current version of the Workbook is used.

  2. While Excel will often allow the user to open the same Workbook twice (and will typically open the second one in a read-only mode), it may give error messages when trying to open the same Workbook twice, which may interrupt XTemplate. This is most notable when trying to open the same Workbook twice on a networked drive or on OneNote. In these cases, best practice is again to save and close the Workbook you want to pull data from.

Copying Source Code from Github .bas files

If you copy the source code from the .bas files on Github and paste it into a new Module, make sure to remove the Attribute VB_NAME line from the source code. The source code on the download page of this site automatically removes that line, so you can copy the source code here as well without issues.

Number Formatting in Excel

XTemplate will pull raw numbers from Excel, and for numbers with decimal places, the number should first be converted to text (for example by using the =TEXT() function) to ensure that any rounding of decimal places is what the user intends. Without the conversion to text, the numbers may have a large number of decimal places. For more information on how computers handle decimal places, see this article.


License

The MIT License (MIT)

Copyright © 2020 Anthony Mancini

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the “Software”), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.