The scripting language for L4X directs how L4X filters text into a copy of the worksheet
containing the script. The L4X scripting language extends Lua 4.0 with reserved tables that
define how text-strings may be selected, converted and conditioned before output
to a formatted Excel worksheet.
An L4X script is
saved as a cell "comment"
in a worksheet within a workbook.
Only one script is allowed per worksheet but a workbook may contain as many
scripts as there are worksheets.You may format a "scripted" workbook as you
require and add formulae, charts, pictures, notes and word-art. The L4X filter
imports text into the worksheet that contains the script, in a copy of the workbook
that contains this worksheet. All formats and formulae in the "scripted" workbook
will be preserved. Formulae with references to particular locations are adjusted
to refer instead to the locations containing the imported text.
top
Introducing L4X scripts
An L4X script specifies neither the name of the text-file nor the name of the workbook.
These input and output files are opened automatically by the L4X filter when the script is run.
The simplest possible L4X script directs the filter to read all (non-blank) lines
from the input text-file and write them to successive cells of column A in the
output worksheet:
-- An L4X script must begin with a comment (--) line
-- No other language statements are required!
Of course, L4X can perform more complex imports. The following script reads
the first
10 text characters, from all lines of text in the input file, and
writes them to successive cells of column A, beginning at row
1 of the output worksheet:
-- begin each script with a comment line
Column.A = {pos=1, len=10}
A script may specify that the text-strings are read only from selected
lines within selected pages. The following script selects lines of text
beginning at line
10 from every page of the input file, and writes text-strings
read from the selected lines to successive cells of columns
A and
B,
beginning at row
3 of the output worksheet:
-- another script
Column.Source = {fromline=10, toline=-1, frompage=1, topage=-1}
Column.A = {pos=1, len=10}
Column.B = {pos=21,len=20}
Sheet.Out.row = 3 -- the "first" row
The last statement in the script above assigns a value to field:
row of the
Sheet.Out
table which specifies the
first row of the worksheet where text
read by
Column.A and
Column.B
will be written.
This document
©Jane Hearn 2008.
All Microsoft trademarks acknowledged.