Available only with Enterprise Edition
How to use Microsoft Excel to manipulate report data before adding it to your DocGen Package.
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 Microsoft 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 an 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.
Configure Microsoft Excel documents
In our example we will use Microsoft Excel as middleware to pass both a single cell value and a value from a range. To accomplish this, we must first set up our DocGen Package to import a Salesforce Report into an Microsoft Excel file.
Tip: In Lightning, you have to click the play button each time you access the Select Files user interface.
In the DocGen Package Files section of the DocGen Package Detail Page, we must be sure to have the Microsoft Excel file be included first in the middle grid. We must also import a Salesforce Report to an Microsoft Excel sheet by including it in the far right grid and assigning it to a sheet in the Microsoft Excel file. In our example, we will include the Salesforce Report in the Report sheet. Finally, we must ensure that the DocGen Package output file comes after the Microsoft Excel file.
Above is the Salesforce Report that we have decided to include in the Report sheet. We are going to use Microsoft Excel formulas to create ranges from this report.
Note: You do not need to include a Salesforce Report when using Microsoft Excel as middleware. We are just doing it for this specific example. You can use object/record data instead of report data to create ranges.
Above we have begun inserting SUMIF formulas to create a table range in the Outputs sheet.
Once all the formulas are included in the table range we will assign it a range name.
For our example, we will also create a single-cell range using a SUM formula to total up cells B3 to B6.
We must give the single-cell a range name in order to transfer the value to another DocGen Package file.
Once we are done assigning ranges to our Microsoft Excel template file, we must upload or replace the template inSalesforce, then move on to tagging our output file.
Tag DocGen Package file
In our example we will use a Microsoft Word document for our DocGen Package output file, but you can also utilize Microsoft PowerPoint or PDF when using Microsoft Excel as middleware.
You must surround the single-cell range name in angle brackets (<< >>) in order to make it a tag for Microsoft Word. You must also surround the table range name 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.
Be sure to replace the DocGen Package output file in Salesforce when you are done tagging.
Configure DocGen Package
The final step to using Microsoft Excel as middleware setting up the DocGen Package properly.
If you plan on using a table range in your DocGen Package output file, then you must include your Microsoft Excel file in your DocGen Package output file. You can do this by highlighting the output file and dragging and dropping the Microsoft Excel file into the far right grid and inserting the table range name in the Sheet/Range column. Also be sure to have a Start Page and End Page value of zero, so the Microsoft Excel file is excluded from the output.
When you are ready to test your ranges, navigate to a record on your DocGen Package Object, click Generate Documents, satisfy all steps on the generate documents page, and then select Run. Above is the output received from our example. As you can see, the single-cell and table range values were included in the final output.