Preparing Excel and Word Templates for Data Merge

Dale Mandeville Updated by Dale Mandeville

Wheelhouse is capable of merging data into template documents created with Microsoft Excel and Microsoft Word. Once the merge is complete, the file may be exported to a PDF or left as the original file type. File merge capability is useful for many things - from creating a custom format for a sales order to creating an nicely formatted report, a customer letter, or a work order template. Using scripting, the resultant report may be stored in the system, emailed as a PDF, or sent to another system.

Creating a Template

  1. Setup the File... if output is paper, set page width
  2. setup columns - cannot use merged cells if text needs to be wrapped or AutoFit Row Height
  3. Create layout
  4. Sometimes it is helpful to manually enter test data to make sure wrapping and auto-fit are working correctly in the template document.
  5. Verify that document prints correctly.
  6. Switch test data to template field data. If you're already using a similar template, see that template for the available fields. Template fields are customized per company. Contact your developer for a list of available fields. Template fields follow the format ${field_name}.
  7. Save the document to the Template Documents list.
  8. Add keywords based on the terms that your developer uses for identifying the correct templates. Again, looking at the exiting template documents will reveal the keywords that are in use.
  9. If you don't already have the merge function available, ask your developer to create an action to connect the template to the data.

Troubleshooting

  • In Excel, if Autofit height not working properly:
    • Verify that Autofit is switched on for the cell.
    • Verify that text wrapping is enabled for the cell.
    • Verify that the autofit cells are either (1) single row or (2) not merged cells
    • Try exporting to Excel instead of PDF to verify that the data is getting properly inserted.
  • If there are missing fields or fields where data is not filling correctly:
    • Verify that the field_name is available in the action be used. (Check with your developer.)
    • Verify that you aren't missing any qualifiers such as the ${ }
  • If cells in Microsoft Word tables are getting split across pages, you can change these settings:
    • Table Properties: Uncheck "Allow row to break across pages"
    • Paragraph Properties: Check "Keep lines together"
  • If certain paragraphs need to be kept together, try the Paragraph: Keep with Next setting.

Fields that are valid for use in template documents:

Valid Template Fields for Quotes and Sales

How did we do?

Exporting to Excel

Contact