Drawloop - Excel as middleware - Connected Data and Named Ranges from Excel Templates

  • 15 February 2022
  • 2 replies
  • 999 views

Badge +8

Topic

How to use Microsoft Excel to manipulate data in a DocGen Package. Also known as Excel as middleware

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:

 

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.

19830iF34BDD0E9A756EA4.png

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.

19831i0138CB2A341207DC.png

Multi-Cell Range:
Once all the formulas are included in the table range, highlight, and assign them a range name. Example: TableRange

19832iEB92178DA4E5AFEA.png

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.

19833i10D62E779A05835C.png

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.

19834iAEFE826A73A8A39B.png

Configure DocGen Package

Update both documents in Salesforce with desired tags and formulas. Sort the Template order if needed.

19840i27E84CB76EAC2786.png

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

19841iAF4B8F5C3FF6E50C.png

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.

19842iDB16C154FA6EF3F9.png

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.

19838i464AB533F0849CE9.png

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.

19843iD57794ED8236834B.png

Related Links


2 replies

Surely there is some updated marketing material for this since 2012?

Here is a YouTube video that is at least showing Lightning https://www.youtube.com/watch?v=UIyZUTy4fG8


 


But this feature is so powerful it needs a much better demo than one so simple and with spelling mistakes. 

Reply