©2002-2016, Ido Millet, ido@MilletSoftware.com
Inserting File Exports into Excel Templates
Using XLS_Range_Insert_File command line argument, you can instruct Visual CUT to insert
Excel (Data Only) exports into pre-formatted excel templates.
sample image1 & image2 .
This approach has several advantages compared to XLS_Range_Insert (see next section):
1. XLS_Range_Insert inserts data from formula values (limited to 65,534 characters).
XLS_Range_Insert_File inserts data from files that can contain much more data
2. If the target cell is not empty, the content is appended to the first empty area below.
3. The method automatically detects the template row based on the target cell and the
number of columns in the exported data. The format of all columns in the template row
as well as the row height are applied to all inserted data.
4. You can elect to also clone content from all other columns in the template row. This
allows you to clone formulas that can refer to content in the inserted rows!
Here is an example of how the command line argument is structured:
The elements (after the "XLS_Range_Insert_File:") are separated by a ">>" and are as follows:
1. Template excel file.
2. Resulting excel file. In append scenarios, can be same as template file
3. Data source & insert instructions consisting of 4 "||" delimited elements:
a) Excel (data Only) export file to be inserted
b) Target sheet in template
c) Target cell in template
d) Options (may leave blank):
[Clone_Template_Row] is useful for cloning formulas outside template columns.
[Remove_Content_Top_Row] skips the top row of the inserted data.
a) If the template range is a Table with sort and filter conditions, these choices are reapplied
after the insert, so the populated table is sorted and filtered just as in the template.
sample image1 & image2 .
b) If your template Table contains formula columns, sparklines, or any other content you
wish to clone rather than overwrite, include a corresponding blank column in the excel
export. You can use a formula that returns "" in Crystal to create such a blank column.
c) You can specify multiple data source & insert instructions separated by a "^^" delimiter.
d) Content below target cell gets pushed down as data is inserted.
e) Row height of target cell is cloned to all inserted rows
f) As always, the files path & name can contain dynamic references. For example:
g) You can fill templates in hidden sheets
h) At the end of the process, Visual CUT restores the focus in the workbook to the original
sheet and original selections in all sheets.
i) The process can automatically handle cases where a Table contains a Summary Row.
The following page shows images demonstrating this functionality.