'Begin Description 'Purpose: To combine the Means and SD numbers into the same column as Means ± SD 'Assumptions: The currently designated Output Window contains at least one such Table. 'The Means And SD column are assumed To be the last 2 columns of the table. 'This script is called from syntax by using a line such as: 'SCRIPT "path\CombineMeanAndSD.SBS". 'Raynald Levesque rlevesque@videotron.ca 2000/10/09 'End description Option Explicit Sub main() ' Declare object variables Dim objOutputDoc As ISpssOutputDoc Dim objOutputItems As ISpssItems Dim objOutputItem As ISpssItem Dim objPivotTable As PivotTable 'Continue the program only if an output document exists. If objSpssApp.Documents.OutputDocCount > 0 Then 'Get the currently designated output document items collection. Set objOutputItems = objSpssApp.GetDesignatedOutputDoc.Items Else MsgBox "There are no Output window!" Exit Sub End If Dim intItemCount As Integer 'number of output items Dim intItemType As Integer 'type of item (see SpssType property) Dim strLabel As String 'Item label Dim intIndex As Integer ' Iterate through output items ' If type = PivotTable, call CombineMeanAndSD intItemCount = objOutputItems.Count() For intIndex = 0 To intItemCount - 1 Set objOutputItem = objOutputItems.GetItem(intIndex) intItemType = objOutputItem.SPSSType() strLabel = objOutputItem.Label If intItemType = SPSSPivot Then Set objPivotTable = objOutputItem.Activate() Call CombineMeanAndSD(objPivotTable) objOutputItem.Deactivate End If Next End Sub Sub CombineMeanAndSD(objPivotTable As Object) 'Purpose: 'Assumptions: The Pivot Table that is to be modified is activated, and strNewFormat is a valid format string 'Effects: If Means and Std Deviations are the last 2 columns of the table, then combine them ' hide the former Std Deviation column, else do nothing 'Inputs: PivotTable object that is already activated 'Return Values: Modified Pivot Table Dim lngRow As Long, lngCol As Long Dim objDataCells As ISpssDataCells Dim ObjColumnLabels As ISpssLabels Dim lngNumRows As Long Dim lngNumColumns As Long Dim strTemp As String Set objDataCells = objPivotTable.DataCellArray Set ObjColumnLabels=objPivotTable.ColumnLabelArray() On Error GoTo errHandler ' Get the column labels object and the number of columns Set objColumnLabels = objPivotTable.ColumnLabelArray lngNumColumns = objColumnLabels.NumColumns ' ColumnLabelArray is a 2-dimensional array. Test whether the last 2 labels ' are Mean and Std Deviation If (objColumnLabels.ValueAt(1,lngNumColumns - 2)&(objColumnLabels.ValueAt(1,lngNumColumns - 1))="MeanStd Deviation") Then With objDataCells For lngRow = 0 To .NumRows - 1 strTemp=Int(.ValueAt (lngRow, .NumColumns - 2)+.5) & " ± " & Int(.ValueAt (lngRow, .NumColumns - 1)+.5) .ValueAt (lngRow, .NumColumns - 2) = strTemp Next End With objColumnLabels.ValueAt(1,lngNumColumns - 2)=" Mean ± SD " 'Hide SD label and data column objColumnLabels.HideLabelsWithDataAt(1, lngNumColumns - 1) End If objPivotTable.Autofit Exit Sub errHandler: Debug.Print "err= ";Err.Number; " Description= ";Err.Description Resume Next End Sub