Populate Excel Template for Specific Fields

How To
1

Introduction

You can populate data into an existing template using the Linx Excel plugin. Data population will, however, begin underneath the template. While this is perfect for templates that act as headers, you may wonder how to populate specific fields. This can be done by creating a template with two sheets: the main sheet that acts as the neatly formatted template and then a data sheet where the data will be put. Then, in your template sheet, use referential fields to access the specific fields.

Solution Overview

Rather than attempting to insert data directly into the template, follow these steps:

  1. Create an Excel template that includes fields referencing a separate data sheet.
  2. Use Linx to populate the datasheet instead of the template.
  3. Ensure Excel updates the referential fields upon opening the file.
  4. (Optional) If values are not updating correctly, use a VBA script to force recalculation.

Step-by-Step Implementation

Step 1: Create an Excel Template with Referential Fields

  1. Open Excel and create a new workbook.
  2. Create two sheets:
  • Template Sheet (where data should appear in a structured format)
  • Data Sheet (where Linx will populate raw data)
  1. In the Template Sheet, reference cells from the Data Sheet using formulas like:
=DataSheet!A2

This ensures that when the Data Sheet is populated, the Template Sheet will automatically update.
4. Save this workbook as an Excel Template (.xltx).

Linx can also write to a hidden sheet, so you can hide the data sheet if you do not want it to be visible to users.

Excel Template With Referral Fields
Excel Template With Referral Fields648×486 24.2 KB

Step 2: Configure Linx 6 to Populate the Data Sheet

  1. In Linx 6, set up a process that:
  • Creates a new Excel file by copying the Template
  • Populates the Data Sheet (not the Template Sheet) with relevant values.
  1. Run the process to generate a new Excel file with populated data.

Linx Process to Populate Excel Data
Linx Process to Populate Excel Data1134×589 30.4 KB

Step 3: Ensure Data Refreshes Upon Opening

Sometimes, Excel may not automatically update formulas referencing the Data Sheet. If this happens, try:

Manual Refresh:

  • Open the Excel file.
  • Press Ctrl + Alt + F9 to force recalculation.

Automatic Refresh with VBA (Optional):

If formulas still don’t update when opening the file, use a VBA script:

  1. Open the Excel VBA Editor by pressing ALT + F11 .
  2. In the Project Explorer, double-click ThisWorkbook.
  3. Copy and paste the following VBA code:
Private Sub Workbook_Open()
    Application.CalculateFull ' Forces recalculation of all formulas
End Sub
  1. Save the file as a Macro-Enabled Workbook (.xlsm).
  2. Close and reopen the file to see if updates are applied correctly.

Here is an attached example solution:
ExcelPopulateTemplate.zip (13.0 KB)