How to Store a @StatSoft #Statistica Spreadsheet to #Excel in Workspace Mode

In order to save a intermediate spreadsheet in Statistica, you need to create some Statistica Visual Basic (SVB) code. This article explains how to do it.

This article is based on the following workspace:

Essentially, the workspace is executing an IDP query against a database, performing some operation (in this case it processes invariant variables) and saves the result from this operation to a Microsoft Excel Sheet.

There is no Custom SVB Code (hey Statistica: why actually?) node in Statistica so we use a Multiple Copies of Datasource node to add our code to. This node has the advantage that it is close to what we want to achieve (it just gets a number of input spreadsheets, copies them, and outputs them).

The Original Code

The original code is performing the following actions:

  1. Checking the InputDescriptor DataIn() (section 3)
  2. Checking input data descriptor, variable selections, etc. (section 4)
  3. Analysis parameter specification (section 5)
  4. Analysis and parameter estimation (section 6)
  5. Error messages (section 7)
  6. Error checking function (section 8)

Modification

We don’t need the basic functionality of the original node, therefore, we can remove the following sections:

  1. Analysis parameter specification (section 5)
  2. Analysis and parameter estimation (section 6)

In addition, we modify section 4 (Checking input data descriptor, variable selections, etc.) to contain the following code:

'==================================================================
' 4. Checking input data descriptor, variable selections, etc.
'==================================================================
	Dim MinerError As DataMinerInputErrors
	For I = Lower To Upper
		MinerError = CheckInputErrorNumber( DataIn(I) )
		If MinerError <> StartOfCommonDataMinerInputErrors _
		Then		GoTo ErrorHandler
	Next I
 
	For I = Lower To Upper
		DataIn(I).DataSource.ExportXLS(
          		"C:Process" & I & ".xlsx",1,0,1,0,False,False,True)
	Next I
 
ExitPort:
	Exit Sub

The first for-loop is from the original code, the second saves all input spreadsheets as Microsoft Excel XML Spreadsheets using the ExportXLS function. Note that the file name contains the number of the spreadsheet. You can modify this code to use a fixed file name by changing it to:

		DataIn(I).DataSource.ExportXLS(
          		"C:Process.xslx",1,0,1,0,False,False,True)

This way, the last spreadsheet will overwrite all previous spreadsheets. You better have only one input spreadsheet in this case!

The complete code should look like this:

'#Uses "*CommonDataMinerInputErrorMessages.svx"
Option Base 1
 
Private Sub SubsetNode( _
    DataIn() As InputDescriptor, _
    DataOut() As InputDescriptor)
 
'==================================================================
' 3. Checking the InputDescriptor DataIn().
'==================================================================
	Lower = LBound( DataIn )
	Upper = UBound( DataIn )
	ReDim DataOut(lower To upper)
 
'==================================================================
' 4. Checking input data descriptor, variable selections, etc.
'==================================================================
	Dim MinerError As DataMinerInputErrors
	For I = Lower To Upper
		MinerError = CheckInputErrorNumber( DataIn(I) )
		If MinerError <> StartOfCommonDataMinerInputErrors _
		Then		GoTo ErrorHandler
	Next I
 
	For I = Lower To Upper
		DataIn(I).DataSource.ExportXLS("C:Process" & I & ".xlsx",1,0,1,0,False,False,True)
	Next I
 
ExitPort:
	Exit Sub
 
'==================================================================
' 7. Error messages
'==================================================================
ErrorHandler:
	DisplayCommonDataMinerError( MinerError )
	GoTo ExitPort
 
End Sub
 
'==================================================================
' 8. Error checking function
'==================================================================
Private Function CheckInputErrorNumber( ByVal ID As InputDescriptor ) As DataMinerInputErrors
 
	CheckInputErrorNumber = StartOfCommonDataMinerInputErrors
 
End Function

I left all code which is useful to our node in the script.

Please leave comments if you have ideas for improvements.

One thought on “How to Store a @StatSoft #Statistica Spreadsheet to #Excel in Workspace Mode

Leave a Reply

Your email address will not be published. Required fields are marked *