Spreadsheet Members

The Spreadsheet object provides access to the Statistica Spreadsheet document, allowing read/writing of Spreadsheet data.

Functions

Name Description
Activate Brings the spreadsheet to the front.
AddCases Adds the specified number of cases to the spreadsheet.
AddEvent Adds event code from the specified file to the document.
AddVariables Adds a specified number of variables to the spreadsheet.
AnalyzeSelectionConditions Analyzes selection conditions for validity. Return value: Boolean.
ApplyLayout Applies a spreadsheet layout to the spreadsheet.
AutoFitCase Sets the case width to fit the case names.
AutoFitVariables Sets the variable widths and heights to fit the variable names.
CaseNamesManager Case Names Manager.
ClearCaseStates Clear the case states of the indicated range of cases. Defaults to first case/last case of spreadsheet.
CloneVirtual Return value: Spreadsheet.
Close Closes the spreadsheet.
ColorByCategory Color and/or mark cases by category.
ConcatenateVariables Concatenate multiple variables' values into one variable, either a new variable or an existing one. TextOperationFlags can be combination of enum TextOperation values, either one set of flags or an array, 1 per variable.
Copy Copies the selected area in the spreadsheet.
CopyCases Copies the specified range of cases in the spreadsheet.
CopyVariables Copies the specified range of variables in the spreadsheet.
CopyWithHeaders Copies both the selected cells and case/variable headers in the spreadsheet.
Cut Cuts the selected area in the spreadsheet.
DeleteAllCaseNames Delete all case names.
DeleteCases Deletes the specified range of cases from the spreadsheet.
DeleteCasesWithMissingData Create a new spreadsheet where any cases from any variables in the variable list that have missing data are deleted. Return value: Spreadsheet.
DeleteVariables Deletes the specified range of variables from the spreadsheet.
ExportJMP Export a spreadsheet to a JMP file. Return value: Boolean.
ExportLog Export spreadsheet log to a new spreadsheet. Return value: Spreadsheet.
ExportMTW Export a spreadsheet to a MTW file. Return value: Boolean.
ExportSAS Export a spreadsheet to a SAS file. Return value: Boolean.
ExportSPSS Export a spreadsheet to an SPSS file. Return value: Boolean.
ExportText Export a spreadsheet to a text file. Return value: Boolean.
ExportTextEnh Enhanced export of a spreadsheet to a text file with many options. Return value: Boolean.
ExportTextEnhEx Enhanced export of a spreadsheet to a text file with many options. Return value: Boolean.
ExportTextEx Export a spreadsheet to a text file with option of using English Numbers (=True) or System Numbers (=False) in the EnglishNumbers parameter. Return value: Boolean.
ExportXLS Export a spreadsheet to a XLS file. Return value: Boolean.
ExportXLS2 Export a spreadsheet to a XLS file with formatting option. Return value: Boolean.
ExportXLSEx Export a spreadsheet to a XLS file with formatting option. Return value: Boolean.
ExportXML Export a spreadsheet to the new XML file format. Return value: Boolean.
FilterCases Filter cases.
FilterCasesEx Filter cases.
FilterSparseData Filter sparse data. Return value: Spreadsheet.
FilterSparseDataEx Filter sparse data. Return value: Spreadsheet.
GetData Get spreadsheet data as 2-D array of variant. Text type variables returned as strings, numeric as numbers (or text labels if desired.). Return value: Variant.
GetData2 Get spreadsheet data as 2-D array of variant. Text type variables returned as strings, numeric as numbers (or text labels if desired.). Return value: Variant.
GetLogEntries Get a number of log entries as XML string. Return value: String.
GetMargins Get the margins for printing in twips.
GetRecordset Gets the ADO Recordset data source, if applicable. Return value: Object.
GetUniqueValues Returns sorted array of all distinct, non-MD values as integers for variable VarNo.
GetUniqueValues2 Returns sorted array of all distinct, non-MD values as integers for variable VarNo.
GetVData Get selected spreadsheet variable information as a single-dim array of variant. Return value: Variant.
GoTo Sets the focus to the specified cell in the spreadsheet.
IsValidFormula Return value: Boolean.
Lock Lock the Spreadsheet to protect its contents. LockFlags are combinations of values from the enum SpreadsheetLockType.
MaxHeaders Sets the depth of headers for variables and case names.
MergeCases Merges the spreadsheet cases with the cases of the specified file.
MergeCasesEx Extended case data merge. Return value: Spreadsheet.
MergeCasesEx2 Second extended case data merge. Return value: Spreadsheet.
MergeTextLabels Text labels merge. Return value: Spreadsheet.
MergeTextValues Merges the spreadsheet text labels with the text labels of the specified file.
MergeVariables Merges the spreadsheet variables with the variables of the specified file.
MergeVariablesCartesian Cartesian variables merge. Defaults to all output variables, no selection conditions, no formatting, and spreadsheet creation. Return value: Spreadsheet.
MergeVariablesConcatenate Concatenate variables merge. Defaults to fill unmatched with MD, all output variables, no selection conditions, no formatting, and spreadsheet creation. Return value: Spreadsheet.
MergeVariablesEx Merges the spreadsheet variables with the variables of the specified file. Return value: Spreadsheet.
MergeVariablesMatching Matching variables merge. Defaults to relational matching, fill unmatched with MD, auto determine text/numeric comparison, match by casename, all output variables, no selection conditions, keep multiples, no formatting, and spreadsheet creation. Return value: Spreadsheet.
MergeVariablesMatchingEx Extended matching variables merge. Defaults to relational matching, fill unmatched with MD, auto determine text/numeric comparison, match by casename, all output variables, no selection conditions, keep multiples, no formatting, and spreadsheet creation. Return value: Spreadsheet.
MergeVariablesMatchingEx2 Extended matching variables merge with SQL matching option. Defaults to relational matching, fill unmatched with MD, auto determine text/numeric comparison, match by casename, all output variables, no selection conditions, keep multiples, no formatting, and spreadsheet creation. Return value: Spreadsheet.
MergeVariablesPO Merges the spreadsheet variables with the variables of the specified file. Return value: Spreadsheet.
MergeVariablesVerifySort Merges the spreadsheet variables with the variables of the specified file verifying the inputs for being sorted. Return value: Spreadsheet.
MoveCases Moves the specified range of cases in the spreadsheet.
MoveVariables Moves the specified range of variables in the spreadsheet.
MultipleSubsetByGroup Create multiple subset spreadsheets of this spreadsheet based on unique combinations of the grouping variables. Return value: Variant.
NumericDateOperation Date operation from numeric variables to date variable.
OptimalTextVariableWidths Returns longest text value length(s) in data so text variable length can be set to minimum without truncation. Return value: Variant.
ParseVariableList Parse a variable list string and return an array of integer variable number arrays. Return value: Variant.
Paste Pastes the contents of the Clipboard into the selected area of the spreadsheet.
PrintOut Prints the document.
ProcessInvariantVariables Process invariant variables. Return value: Spreadsheet.
ProcessInvariantVariablesEx Process invariant variables. Return value: Spreadsheet.
RankVariables Ranks the specified range of variables in the spreadsheet.
Recalculate Recalculate the specified variable formula (optionally within a specified case range).
Recode Recodes the specified variable.
RecodeGroups Recodes a group of variables.
RecodeOutliers Recode outliers. Return value: Spreadsheet.
RemoveAllLabels Removes all text labels for the specified variable.
RemoveTextLabel Removes a text label for the specified variable.
ReplaceMD Replaces the missing data in the specified range of variables in the spreadsheet.
ReplaceMDusingKNearestNeighbor Replace MD using k nearest neighbor method. Return value: Spreadsheet.
ReplaceMDusingKNearestNeighborEx Replace MD using k nearest neighbor method. Return value: Spreadsheet.
RunEvent Run the event.
Save Saves the spreadsheet.
SaveAs Saves the spreadsheet as the specified filename. Return value: Boolean.
SaveAsPDF Save a spreadsheet as a PDF file. Return value: Boolean.
SaveAsPDFCustom Saves the spreadsheet as a PDF file, with custom length and width (in 10ths of millimeters), to the specified file path. Return value: Boolean.
SaveAsPDFEx Saves the spreadsheet as a PDF file, with orientation, to the specified file path. Return value: Boolean.
SaveAsVersion6 Return value: Boolean.
SaveAsVersion61 Return value: Boolean.
SaveAsVersion7_11 Return value: Boolean.
SaveCopyAs Creates a file-based copy of the spreadsheet; if Name is not specified, creates a temporary file. Return value: String.
SaveToEnterprise -
SelectAll Selects all of the cells in the spreadsheet.
SelectCaseNamesOnly Selects only the case names in the spreadsheet.
SelectCodesDialog Display UI for selecting codes. Return value: Long.
SelectVariableNamesOnly Selects only the variable names in the spreadsheet.
SelectedCase Returns case using selection conditions.
SelectedCaseWithWeights Returns case using selection conditions and weighting variable.
SetData Set spreadsheet data as 2-D array of variant.
SetMargins Set the margins for printing in twips.
SetRecordset Sets the ADO Recordset data source, if applicable.
SetSize Sets the size of the spreadsheet.
SetTextLabel Sets a new text label for the specified variable.
SetTextValuesManager Set the TextValuesManager to override this spreadsheet's default method of generating associated numbers for text values. Callers should restore it to previous values when finished (ident is new value on entry, and old value on exit).
SetTextVariableWidths Sets text variable widths for multiple variables at once.
SetVariableTypes Set the types and/or lengths of multiple variables at once.
ShiftVariables Shifts the specified range of variables in the spreadsheet.
ShowVariableSpecsDialog Return value: Boolean.
SortData Sorts the specified variables in the spreadsheet by ascending, numeric order.
SortDataEx Extended Sort Data. Defaults to all variables, ascending order, sort by numeric value, and inline update. Return value: Spreadsheet.
StandardizeVariables Standardizes the specified range of variables in the spreadsheet.
Statistics Computes descriptive statistics for the specified variable(s).
StripFormatting Strips formatting from returned strings (case names, variable names, etc.). Return value: String.
SubsetSplitSpreadsheet Creates two new spreadsheets by splitting the current spreadsheet using a probability value.
SubsetSplitSpreadsheetEnh Creates two new spreadsheets by splitting the current spreadsheet using a probability value.
SubsetSplitSpreadsheetEx Creates two new spreadsheets by splitting the current spreadsheet using a probability value.
TextDateOperation Date operation from text variable to date variable.
Transform Transform variable(s) according to the supplied formula(s).
TransformWithExtraOptions Transform variable(s) according to the supplied formula(s).
TransformWithExtraOptions2 Transform variable(s) according to the supplied formula(s).
TransposeBlock Transposes the selected block of the spreadsheet.
TransposeData Transposes the spreadsheet.
TransposeFile Transposes the spreadsheet.
UnMarkAll Removes all marked cells.
Unlock Unlock a locked spreadsheet.
VariableNumber Lookup 1-based variable index from variable name. Return value: Long.
VariableSetTextType Change a variable to Text type and sets the length in one step.
VariableTextLabels Returns three arrays of numeric values, corresponding text labels, and text label descriptions for the specified variable.
VerifyData Verifies data by marking cases based on a range of cases and selection conditions.

Properties

Name Description
ActiveCell Returns/sets the active cell in the spreadsheet as a range. Return value: Range. This property is read only.
AllowTextLabels Returns/sets whether a variable can have text labels. Return/assignment value: Boolean.
Analyses Returns a collection of all analyses which use this spreadsheet as the data source. Return value: StaDocuments. This property is read only.
Application Returns application associated with the spreadsheet. Return value: Application. This property is read only.
AutoRunEvent Returns/sets whether the event code of a document will automatically run when the document is opened. Return/assignment value: Boolean.
CData Returns/sets a case vector as an array of doubles. Return/assignment value: Double().
Case Returns/sets spreadsheet case. Return value: Range. This property is read only.
CaseColor Get/Set the case color as an RGB value, which will be used in certain plots of this case. Return/assignment value: Long.
CaseExcluded Get/Set the case as excluded, meaning it will not be included in analyses/graphs. Return/assignment value: Boolean.
CaseHeader Returns/sets the content of the specified range of case headers. Return value: Range. This property is read only.
CaseHeaderCell Returns/sets the content of the specified case header. Return value: Range. This property is read only.
CaseHeaderEx Returns/sets the content of the specified range of case headers, with columns. Return value: Range. This property is read only.
CaseHeaders Returns/sets the content of the specified case header. Return value: Areas. This property is read only.
CaseHeight Returns/sets the height of the specified row/case. Return/assignment value: Double.
CaseHidden Get/Set the case as hidden, meaning it will be hidden on certain plots. Return/assignment value: Boolean.
CaseLength Get the length in bytes of a single case. Return value: Long. This property is read only.
CaseMarked Get/Set the case as marked. Return/assignment value: Boolean.
CaseMarkerType Get/Set the case marker type. This marker will be used in certain plots of this case. Return/assignment value: PlotMarkerType.
CaseName Returns/sets the specified case name. Return/assignment value: String.
CaseNameIndex Case name exists => returns a corresponding case index, otherwise 0. Return value: Long. This property is read only.
CaseNameLength Returns/sets the specified case name length. Return/assignment value: Integer.
CaseNameWidth Returns/sets the specified case name width. Return/assignment value: Double.
CaseQualified Returns whether the specified cell is case qualified through case selection conditions. Return value: Boolean. This property is read only.
CaseState Get/Set the CaseState of the indicated case. If VarNumber is provided and is a CaseStateVar, get the CaseState from there instead. Return/assignment value: StaCaseState.
CaseStateVar Get/Set the indicated variable as a variable that displays case states. The variable must be of type Integer. Return/assignment value: Boolean.
CaseValues Returns the specified case values as a double array. Return/assignment value: Double().
CaseValuesForVars Returns an array of values corresponding to the specified case for the specified variables. Return value: Double(). This property is read only.
CaseWeight Returns the case weight of the spreadsheet. Return value: CaseWeight. This property is read only.
Cases Returns/sets spreadsheet cases as a collection. Return value: Areas. This property is read only.
Cells Returns/sets individual cells as a range. Return value: Range. This property is read only.
CellsRange Returns/sets a cell range as a range. Return value: Range. This property is read only.
ContentData Get/Set the content data in a specified format. Return value: Variant. This property is read only.
Data Returns/sets the spreadsheet data as a two dimensional array of doubles. Return/assignment value: Double().
DeferNotificationState Get/Set deferred handling of change notifications. Return/assignment value: Boolean.
DirectMode Get/Set the Direct Mode of operation, where no temp file is used for modified data and modifications to spreadsheet data are written back to original datafile. Return/assignment value: Boolean.
DisplayAttribute Returns/sets the display status of a specified attribute (e.g., displaying marked cells). Return/assignment value: Boolean.
EntireRange Returns/sets a range of all rows/columns. Return value: Range. This property is read only.
EventCode Returns/sets the event code of a document. Return/assignment value: String.
EventRunning Return true if the event is running, otherwise return false. Return value: Boolean. This property is read only.
FullName Returns the full name (including file path) of the spreadsheet. Return value: String. This property is read only.
Gridline Returns/sets the gridline attributes for the spreadsheet. Return value: GridBorder. This property is read only.
Header Returns/sets the header content of the spreadsheet. Return value: Range. This property is read only.
InfoBox Returns/sets the infobox content of the spreadsheet. Return value: Range. This property is read only.
InputSpreadsheet Returns/sets whether the spreadsheet is an input spreadsheet. Return/assignment value: Boolean.
IsDatabase True if this is a streaming db source. Return value: Boolean. This property is read only.
IsDate Return value: Boolean. This property is read only.
IsTime Return value: Boolean. This property is read only.
LabelCase Get/Set the case label flag, meaning this case will be labeled on certain plots. Return/assignment value: Boolean.
LabelVariable Get/Set the label variable. This variable's values are used as labels in certain plots. Zero means no label variable. Return/assignment value: Long.
LockFlags Get current lock settings. These are combinations of values from enum SpreadsheetLockType. Return value: SpreadsheetLockType. This property is read only.
LogEntryCount Get the number of entries in the log. Return value: Long. This property is read only.
LoggingEnabled Get/Set logging on/off for this spreadsheet. Return/assignment value: Boolean.
LoggingPromptForReason Get/Set whether we prompt for reason comments for logging of changes. Return/assignment value: Boolean.
LoggingReason Get/Set a reason comment to use for following changes. Return/assignment value: String.
MappedSubset Create a mapped subset. Return value: Object. This property is read only.
MarkedCells Returns the range of cells that are marked in the spreadsheet. Return value: Range. This property is read only.
MaximumColumnWidth Sets maximum column width. Return/assignment value: Double.
MissingData Returns whether the specified cell is missing data. Return value: Boolean. This property is read only.
MonitorRunStatus Get the monitor run status. Return value: Object. This property is read only.
Name Returns/sets the name of the spreadsheet as a string. Return/assignment value: String.
NumberOfCases Returns the number of cases in the spreadsheet. Return value: Integer. This property is read only.
NumberOfTextLabels Returns the number of text labels assigned to the spreadsheet. Return value: Integer. This property is read only.
NumberOfVariables Returns the number of variables in the spreadsheet. Return value: Integer. This property is read only.
Parent Returns parent associated with the spreadsheet. Return value: Object. This property is read only.
Path Returns the file path of the spreadsheet. Return value: String. This property is read only.
PrintFooter Returns/sets the document footer used when printing. Return/assignment value: String.
PrintHeader Returns/sets the document header used when printing. Return/assignment value: String.
Queries Returns the queries collection associated with the spreadsheet. Return value: Queries. This property is read only.
Range Returns/sets a range of cells(e.g., "C1R1:C3R2,C5R5:C6R6"). Return value: Range. This property is read only.
ReadOnly Is spreadsheet opened as read-only. Return value: Boolean. This property is read only.
Redraw Returns/sets whether the spreadsheet can be redrawn. Return/assignment value: Boolean.
Saved Returns/sets the saved state of the spreadsheet. Return/assignment value: Boolean.
Selection Returns/sets current selection as range. Return value: Range. This property is read only.
SelectionCondition Returns the selection conditions of the spreadsheet. Return value: SelectionCondition. This property is read only.
Subset Creates a new spreadsheet containing the specified subset of the current spreadsheet using Selection Conditions. Return value: Spreadsheet. This property is read only.
SubsetEx Creates a new spreadsheet containing the specified subset of the current spreadsheet using Selection Conditions. Return value: Spreadsheet. This property is read only.
SubsetEx2 Creates a new spreadsheet containing the specified subset of the current spreadsheet using Selection Conditions. Return value: Spreadsheet. This property is read only.
SubsetRandomSampling Creates a new spreadsheet containing the specified subset of the current spreadsheet using Random Sampling. Return value: Spreadsheet. This property is read only.
SubsetRandomSamplingEx Creates a new spreadsheet containing the specified subset of the current spreadsheet using selection conditions and Random Sampling. Return value: Spreadsheet. This property is read only.
SubsetRandomSamplingEx2 Creates a new spreadsheet containing the specified subset of the current spreadsheet using selection conditions and Random Sampling. Return value: Spreadsheet. This property is read only.
SubsetStratifiedRandomSampling Creates a new spreadsheet containing the specified subset of the current spreadsheet using selection conditions and Stratified Random Sampling. Return value: Spreadsheet. This property is read only.
SubsetStratifiedRandomSamplingEx Creates a new spreadsheet containing the specified subset of the current spreadsheet using selection conditions and Stratified Random Sampling. Return value: Spreadsheet. This property is read only.
SubsetSystematicRandomSampling Creates a new spreadsheet containing the specified subset of the current spreadsheet using Systematic Random Sampling. Return value: Spreadsheet. This property is read only.
SubsetSystematicRandomSamplingEx Creates a new spreadsheet containing the specified subset of the current spreadsheet using selection conditions and Systematic Random Sampling. Return value: Spreadsheet. This property is read only.
SubsetSystematicRandomSamplingEx2 Creates a new spreadsheet containing the specified subset of the current spreadsheet using selection conditions and Systematic Random Sampling. Return value: Spreadsheet. This property is read only.
Superset Creates a new spreadsheet containing the specified subset/superset of the current spreadsheet using Selection Conditions. Return value: Spreadsheet. This property is read only.
Text Returns the text value of the specified cell. Return value: String. This property is read only.
TextEx Returns the text value of the specified cell formatted with the format string. Return value: String. This property is read only.
TextLabel Returns the text label for the specified value in the specified variable. Return value: String. This property is read only.
TextLabelDescription Returns the text label description for the specified value in the specified variable. Return value: String. This property is read only.
TextLabelValue Returns the value for the specified text label in the specified variable. Return value: Variant. This property is read only.
TextValuesManager Get/Set the TextValuesManager to override this spreadsheet's default method of generating associated numbers for text values. Return value: TextValuesManager. This property is read only.
TextValuesManagerIdent Get the identifier this spreadsheet uses to communicate withe the TextValuesManager. Return value: Integer. This property is read only.
VData Returns/sets a variable vector as an array of doubles. Return/assignment value: Double().
VDataWithoutMD Returns/sets a variable vector as an array of doubles without missing data. Return value: Double(). This property is read only.
Value Returns the value within the specified cell of the spreadsheet. Return/assignment value: Double.
VarBundle Returns the variable Bundle. Return value: VarBundles. This property is read only.
Variable Returns/sets spreadsheet variable. Return value: Range. This property is read only.
VariableExcluded Get/set the variable excluded. Excluded variables do not show up in variable selection dialogs for graphs and analyses. Return/assignment value: Boolean.
VariableFormatString Returns/sets the display format of the specified variable index. Return/assignment value: String.
VariableHeader Returns/sets the content of the specified range of variable headers. Return value: Range. This property is read only.
VariableHeaderCell Returns/sets the content of the specified variable header. Return value: Range. This property is read only.
VariableHeaderEx Returns/sets the content of the specified range, with rows, of variable headers. Return value: Range. This property is read only.
VariableHeaders Returns/sets the content of the specified variable header. Return value: Areas. This property is read only.
VariableLongName Returns/sets the long name of the specified variable as a string. Return/assignment value: String.
VariableMeasurementType Get/Set the variable's measurement type. Pass AutoDetect=True if you want the type to be automatically determined (when type is scMeasureTypeAuto). Return/assignment value: MeasurementType.
VariableMissingData Returns/sets the missing data value for the specified variable. Return/assignment value: Double.
VariableMissingDataText Returns/sets the missing data value of the specified variable index in text format. Return value: String. This property is read only.
VariableName Returns/sets the name of the specified variable as a string. Return/assignment value: String.
VariableNames Get an array of all variable names. Return value: Variant. This property is read only.
VariableProperties Get the collection of properties for the variable. Return value: NamedProperties. This property is read only.
VariableType Returns/sets the variable type of the specified variable as an integer. Return/assignment value: VariableType.
VariableTypeLength Returns/sets the maximum length of the specified variable (if variable type is set to text) as an integer. Return/assignment value: Integer.
VariableWidth Returns/sets the width of the specified variable. Return/assignment value: Double.
Variables Returns/sets spreadsheet variables as a collection. Return value: Areas. This property is read only.
Visible Returns/sets the visibility of the spreadsheet. Return/assignment value: Boolean.
Window Returns the window of the spreadsheet. Return value: Window. This property is read only.
WorkbookItem Returns the WorkbookItem interface if the object is in a Workbook. Return value: WorkbookItem. This property is read only.

Events

Name Description
Activate Called when the spreadsheet is brought to the front.
BeforeAddToWorkbook Called before spreadsheet is added to a workbook.
BeforeClose Called before the spreadsheet is closed.
BeforeDataChange Called before data has been altered.
BeforeDoubleClick Called when any area of the spreadsheet has been double clicked.
BeforePrint Called before the spreadsheet is printed.
BeforeRightClick Called when any area of the spreadsheet has been right clicked.
BeforeSave Called before the spreadsheet is saved.
BeforeStructureChange Called when the size of the spreadsheet has changed.
DataChanged Called when the data within a cell has been altered.
Deactivate Called when the spreadsheet loses the focus.
OnClose Called when the spreadsheet is closed.
OnQueryRunComplete Called when running the query is finished.
OnQueryRunError Called when running the query has an error.
Open Called when the spreadsheet is opened.
SelectionChange Called when the selection within the spreadsheet has moved.
StructureChanged Called when the size of the spreadsheet has changed.