EssVCascade

Description

Cascades on the selection, and generates multiple worksheets.

Syntax

EssVCascade(sheetName, range, selection, path, prefix, suffix, level, openFile, copyFormats, overwrite, listFile)
ByVal sheetName As Variant
ByVal range As Variant
ByVal selection As Variant
ByVal path As Variant
ByVal prefix As Variant
ByVal suffix As Variant
ByVal level As Variant
ByVal openFile As Variant
ByVal copyFormats as Variant
ByVal overwrite As Variant
ByVal listFile As Variant

Parameters

sheetName

Text name of worksheet to operate on. sheetName is of the form "[Book.xls]Sheet". If sheetName is Null or Empty, the active worksheet is used.

range

Range object which refers to the data to be used as the source of the cascade. If range is Null or Empty, the whole worksheet is used.

selection

Range object which refers to the members to be cascaded. If selection is Null or Empty, the active cell is used.

path

Text name of the destination directory for worksheets created. If path is Null or Empty, the current directory is used.

prefix

Text name of the first part of the file names of the worksheets generated. If prefix is Null or Empty, no prefix is added to the file name.

suffix

Text name of the last part of the file names of the worksheets generated. If suffix is Null or Empty, no suffix is added to the file name.

Level

Note:

You can use the Level Constants instead of 1-7 to set the cascade level (see VBA Level Constants).

Number indicating the granularity of the cascade, as shown in Table 7.

Table 7. Level Numbers for Cascade

Level	Action
1	Next level
2	All levels
3	Bottom level
4	Sibling level
5	Same level
6	Same generation
7	Calc level

If level is Null or Empty, 1 is used.

openFile

Boolean value indicating whether the newly created worksheets should remain open. If openFile is Null or Empty, False is used.

copyFormats

Boolean value indicating whether the formatting from the parent worksheet should be copied to the newly created worksheets. If copyFormats is Null or Empty, True is used.

overwrite

Boolean value indicating whether the newly created worksheets can overwrite previously saved worksheets. If overwrite is Null or Empty, True is used. False means the user can rename the worksheets before saving.

listfile

Boolean value indicating whether a report file is created. If listfile is Null or Empty, False is used. True means a file is created in the path with the name prefix0suffix.lst.

Return Value

Returns 0 if successful. A negative number indicates a local failure (see VBA Return Values). A return value greater than zero indicates a failure on the server.

Example

Declare Function EssVCascade Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal range As Variant, ByVal selection As Variant, ByVal path As Variant, ByVal prefix As Variant, ByVal suffix As Variant, ByVal level As Variant, ByVal openFile As Variant, ByVal copyFormats as Variant, ByVal overwrite As Variant, ByVal listFile As Variant) As Long

Sub Cascade()
X=EssVCascade("[Book2.xls]Sheet1", RANGE("A1:G52"), RANGE("B3"), "C:\ESSBASE\CLIENT\SAMPLE", "HQ", "97", 3, True, True, True, True)
If X = 0 Then
MsgBox("Cascade successful.")
Else
MsgBox("Cascade failed.")
End If
End Sub

Notes:

The files created are named prefixXsuffix.xls, where prefix and suffix are supplied parameters, and X indicates the number of the worksheet generated. The first worksheet generated is prefix1suffix.xls, the second prefix2suffix.xls, and so on.
The number of worksheets generated equals the number of member combinations that exist in the level you specified in level. Keep the prefix and suffix short enough to leave room in the file name for the maximum number of combinations.