©2002-2016, Ido Millet, ido@MilletSoftware.com
Replacing Content in Excel Files
Using a command line argument, you can instruct Visual CUT to replace any number of strings
in an Excel file with specified substitutions. The replacement logic can use dynamic values
from a Crystal field or formula. Also, by forcing a re-evaluation of the content in cells with
replaced content, exported content in a cell can be turned into a dynamic Excel formula.
The command line argument structure is as follows:
The parameters (after the ":") are separated by a "||" and are as follows:
1. InFile: the file path & name for the source Excel file.
2. OutFile: the file path & name for the resulting Excel file.
- If no OutFile is specified, the source file gets updated
if the target folder doesn’t exist, Visual CUT creates
- if no path is specified (just name) the path of the source file is used
3. Find & Replace Pairs: unlimited number of find and replace elements.
Each "find" element is separated from its "replace" element by a ‘
Each pair is separat
ed from the next pair by a ‘
- To specify special string characters, such as Carriage Return or Line Feed,
use Chr() expressions (e.g., Chr(10) or Chr(13).
To remove strings, specify them in the ‘find’ and leave the ‘replace’ as blank
4. Options: Leave blank. May be used for future enhancements.
For example, to replace
(useful for turning formula expressions into formulas, if in
Crystal you export the expression starting with "
", use this command line argument:
Dynamic Field Names
You can use field or formula names within the command line argument (just like you drag &
drop fields & formulas into the various options in the 3rd tab within Visual CUT. The dynamic
content of these fields/formulas would be substituted into the command line argument.
Exporting Formula Expressions to Excel, and Activating Them
Imagine you wish to export a Crystal report to Excel, and some of the cells in the export contain
formula expressions like this: "=ROUND(C3*(1+($D$2/100)),2)"
The excel export would treat such cells as TEXT rather than as a formula that refers to other cells
in the exported file. To activate the cell so it becomes an Excel formula, change the expression in
Crystal so it starts with something unique. For example: "
Then, use "XLS_Replace:c:\temp\MyFile.xlsx||||
||" to activate the formula.