Topic
If there is data you cannot persist in Salesforce, whether it needs to be calculated or concatenated, you can capture it in your documents by using Excel as middleware. Using Microsoft Excel formulas, you can manipulate Report data or Object data within a cell and transfer it to any of your DocGen Package files as a range.
Ranges from a Microsoft Excel worksheet can be transferred to a Microsoft Word, Microsoft PowerPoint, or PDF document using the Microsoft Excel range as a field tag. These Microsoft Excel ranges can also be used in any Insert-Updates related to your DocGen Package.
Available only with Enterprise Edition.
Instructions
Configure Microsoft Excel documents
Document management is only available in the Lightning Experience DocGen Designer (LEDD).
The example will use Microsoft Excel as middleware to pass both single-cell and multi-cell range values to another template document. To accomplish this, first set up the DocGen Package to import data into a Microsoft Excel file.
Merging Data Options:
- Field data into Excel: Tagging Excel documents
- DocGen Relationship with Row Replication: Row Replication
- Connected Data Source: Connected Data to Templates
In the DocGen Package Document Tab, the Microsoft Excel file must be included first on the Templates list. In this example, the data is merged into the 'Report Data' sheet.
With the sample data merged into the document, Excel formulas can now be configured to manipulate the data for desired calculations or formatting on the 'Output Data' sheet.
Example: SUMIF formulas to create a table range in the Outputs sheet.
Multi-Cell Range:
Once all the formulas are included in the table range, highlight, and assign them a range name. Example: TableRange
Single-Cell Range:
Select the desired single cell, highlight, and assign it a range name. Example: SumTotal
The single-cell must be given a range name in order to transfer the value to another DocGen Package file.
Tag DocGen Package Template file:
Below is a Microsoft Word document for the DocGen Package output file. Microsoft PowerPoint or PDF can be utilized as well.
- The single-cell range name must be enclosed in angle brackets (<< >>) in order to make it a tag for Microsoft Word.
- The multi-cell range name must be enclosed with angle brackets (<< >>) and append "_Start" to the range name in order to include all data from the table range in the Microsoft Word table. The Word table must contain the correct amount of columns.
Be sure to replace the DocGen Package output file in Salesforce when you are done tagging.
Configure DocGen Package
Update both documents in Salesforce with desired tags and formulas. Sort the Template order if needed.
Connect the Excel as middleware by editing the Word Output template and selecting the Connected Data source Named Ranges from Excel Template. Manage Connected Data
Select the Excel as middleware document and enter the Named Range for Tag. Select + Add Range Name to Use as Tags for additional Ranges.
Note:
When using a single-cell named range while tagging the documents, you must remove "_Start" from the tag even though it currently displays as <<SumTotal_Start>> in the file pane. For example, <<SumTotal>>.
Additionally, single-cell ranges are not required to be placed in the Connected Data pane to pass data. Multi-cell ranges are required.
Save the document configuration and test the DocGen Package. We recommend generating a download delivery option as Output Originals to verify the formulas and logic of the excel document.
Once the DocGen Package is generating as expected, update the Start Page and End Page of the Excel Document to a value of zero. This excludes the document from the output while utilizing the calculations.
Related Links
- Field data into Excel: Tagging Excel documents
- DocGen Relationship with Row Replication: Row Replication
- Connected Data Source: Connected Data to Templates
- Phone numbers not formatting as expected
- Update Child Records with Data
