Macro (SVB) Programs Example - Copying Cells from a Spreadsheet into a Report

This example illustrates how to use the Copy and Paste methods to move cells from a results spreadsheet to a report (object).

' This program will generate a Descriptive Statistics
' results Spreadsheet via the Basic Statistics module,
' create a new Report window, and paste some cells from
' the results Spreadsheet to the report.

Sub
Main
' Create the Basic Statistics analysis object; you
' may have to edit the path name for the input data
' file (Spreadsheet), to match the installation of
' STATISTICA on your computer.

Dim s As New Spreadsheet

Set s = Spreadsheets.Open _
(Path & "\Examples\DataSets\Exp.sta")

Set newanalysis = Analysis (scBasicStatistics,s)

' Here we are "running" the Basic Statistics;
' the next four lines were recorded as a macro,

' and then modified for this example.

newanalysis.Dialog.Statistics = scBasDescriptives
newanalysis.
Run

newanalysis.
Dialog.Variables = "5 6 7 8"

' Create the Summary results Spreadsheet (remember that
' all results Spreadsheets are returned as collections).

Set ResSpreadsheetCollection=newanalysis.Dialog.Summary

' "Extract" the single individual object of type Spreadsheet
' from the collection.

Set ResSpreadsheet=ResSpreadsheetCollection.Item(1)

' Make the results Spreadsheet visible.

ResSpreadsheet.Visible=True

' Select all numbers in the first column of the
' Spreadsheet; note that the CellsRange property returns

' an object of type Range object; here is the complete

' syntax (see also the Object Browser):

' Property CellsRange(

' FirstRow As Long, FirstColumn As Long,
' LastRow As Long, LastColumn As Long)
' As Range

Set
Cells=ResSpreadsheet.CellsRange( _
1, 1, ResSpreadsheet.
NumberOfCases, 1)

' Next select the cells in this range (for copying).

Cells.Select

' The CopyWithHeaders method will copy the highlighted
' range of cells along with the row and column headers.

ResSpreadsheet.CopyWithHeaders

' Create a new report window.

Set ResRep=New Report

' Make the new report window visible.

ResRep.Visible=True

' Paste in the previously copied numbers.

ResRep.Paste

s.Close

End Sub

The Copy and Paste (and related) methods are also very useful to move information from spreadsheet to spreadsheet or to another application such as Microsoft Excel (see also Calling STATISTICA from Visual Basic in Other Applications for more details).