2.16.1.1 Details of TreeNodes in Wextract


settings

The settings tree specifies all settings for the wextract X-Function.

Syntax: settings.Treenode:=<value>

Example:

  • settings.Cols:=0 settings.RecalMode:=0
Treenode Label Type Default Description
Cols Columns string Specify columns to be extracted. This must be specified if settings.stMethod.Method is not 0. Note that column index starts from 0 and if multiple columns are desired, separate them with "|". Examples:
  • settings.cols:=0 //Extract the first column
  • settings.cols:="0|1" //Extract the first and the second columns
RecalMode Recalculate int 0 Specify the recalculate mode.
  • 0: None
  • 1: Auto
  • 2: Manual

stAlias

The stAlias branch of the settings tree allows you to define aliases for columns. Then you can use these aliases to build the condition for the If test.

Syntax: settings.stAlias.Treenode:= <value>

Examples:

  • settings.stAlias.Cols:=1 settings.stAlias.AliasNames:=A
  • settings.stAlias.Cols:="1|2" settings.stAlias.AliasNames:="V|A"
Treenode Label Type Default Description
Cols Columns string Specify the indices of columns for which aliases are defined. Note that column index starts from 1. If you want to define aliases for multiple columns, separate their index numbers with "|". Examples:
  • settings.stAlias.cols:="1" //The first column
  • settings.stAlias.cols:="1|2" //The first and second columns
AliasNames Alias Names string Specify aliases for the chosen columns in the settings.stAlias.Cols string. If multiple aliases are defined, separate them with "|". Examples:
  • settings.stAlias.AliasNames:="A" //The alias for the selected column is "A".
  • settings.stAlias.AliasNames:="A|B" //The aliases in the selected columns are "A" and "B", respectively.

stCondition

The stCondition branch of the settings tree specifies the condition for testing whether a row should be extracted and the scripts that will be executed before the select data loop and before the condition is tested.

Syntax: settings.stCondition.Treenode:= <value>

Examples:

settings.stCondition.Condition:="A<5"
settings.stCondition.BeforeLoop:="j=1;"
settings.stCondition.BeforeIfCondition:="j=j+1;"
Treenode Label Type Default Description
Condition Condition string Specify the condition to test whether a row should be extracted. If a data row meets the condition, if will be extracted.
BeforeLoop Script Before Select Data Loop string Specify the script that will be executed before the selected data rows are tested to see if they meet the condition. See details here.
BeforeIfCondition Script Before If Condition string Specify the script that will be executed in the loop, before a row if tested to see if it meets the condition. See details here.

stMethod

The stMethod branch of the settings tree specifies the output method.

Syntax: settings.stMethod.Treenode:= <value>

Examples:

settings.stMethod.Method:=2
settings.stMethod.ColFilter:=1
settings.stMethod.WksSpecified:=[Book1]Sheet2!
settings.stMethod.ColFrom:=1
settings.stMethod.ColTo:=2
settings.stMethod.Color:=1
Treenode Label Type Default Description
Method Output Method int 2 Specify the output method. Options include
  • -2:Highlight the cells that meet the condition.
  • -1:Mask the cells that meet the condition.
  • 0:Use a column to indicate whether the condition is satisfied for each row(1=true, 0=false).
  • 1:Extract to a new worksheet.
  • 2:Extract to a new workbook.
  • 3:Extract to a specified worksheet.
  • 4:Fill the cells that meet the condition with a specified color.
ColFilter Column Filter int -1 This is valid only when settings.stMethod.Method:=0. You can use this variable to specify the column for outputting the filtering result (0 for false and 1 for true). Column index starts from 0. If "-1" is used, a new column will be added to the source worksheet and used for outputting the filtering result.
WksSpecified Worksheet range This is valid only when settings.stMethod.Method:=1. Use it to specify the worksheet for outputting the extracted rows. Note that if the oy variable of the X-Function is specified, oy will be used as the output range no matter what WksSpecified is.
ColFrom Column From int -1 This is valid only when settings.stMethod.Method:=1 or 2 or 3. It specifies the beginning column to output the extracted data. Column index starts from 0. Note that if the oy variable of the X-Function is specified, y will be used as the output range no matter what ColFrom is. Examples,
  • settings.stMethod.ColFrom:=0 //The first column.
  • settings.stMethod.ColFrom:-1 //The first empty column in the output worksheet.
ColTo Column To int -1 This is a redundant parameter. Please do not use it.
Color Color int 1 This is valid only when settings.stMethod.Method:=4. It specify the index of the color (in the color list) used for filling the cells that meet the condition. Color index starts from 0. Examples:
  • settings.stMethod.color:=0 //black
  • settings.stMethod.color:=1 //red

stRowRange

The stRowRange branch of the settings tree specifies the row range from which data is extracted.

Syntax: settings.stRowRange.Treenode:= <value>

Example:

settings.stRowRange.RowFrom:=0
settings.stRowRange.RowTo:=10
Treenode Label Type Default Description
RowFrom Row From int 0 Specify the beginning row to extract data. Row index starts from 0. Examples:
  • settings.stRowRange.RowFrom:=0 //The first row.
  • settings.stRowRange.RowFrom:=2: //The third row.
RowTo Row To int -1 Specify the end row to extract data. Row index starts from 0. For example,
  • settings.stRowRange.RowTo:=3 //The fourth row.
  • settings.stRowRange.RowTo:=-1 //The last row.
English | Deutsch | 日本語