Remove pivot table row and column labels

 

Download as .sbs Shows as plain text

 

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
'Begin Description
'This script deletes all row and column labels in the selected Pivot Table.  
'Requirement: The Pivot Table you want to change must be selected.
'End Description
'PURPOSE
	'This script deletes all row and column labels in the selected Pivot Table
	
'ASSUMPTIONS
	'A is selected in the Navigator (Output Document).
	'Also, the Navigator (Output Document) that contains the Pivot Table is the Designated Output Window
	
'EFFECTS
	'Row and Column labels will be changed to null strings
	
'HINTS
	'If you are new to programming, select Scripting Tips from the 
	'Help menu for a basic introduction.

	'For information on SPSS automation objects, properties and methods,
	'press F2 to display the Object Browser.

	'For context-sensitive help on Sax Basic terms as well as SPSS objects,
	'properties, and methods, press F1. 


Option Explicit		'All variables must be declarated before being used
'***********************************************************************
Sub Main
'Declare variables here
	Dim objPivotTable As PivotTable
	Dim objItem As ISpssItem

	Dim bolFoundOutputDoc As Boolean
	Dim bolPivotSelected As Boolean

	'The following procedure is a global procedure that is located in global.sbs
    Call GetFirstSelectedPivot(objPivotTable, objItem, bolFoundOutputDoc, bolPivotSelected)

	If (bolFoundOutputDoc = False) Or (bolPivotSelected = False) Then
		'either there wasn't an output doc or a pivot table wasn't selected
		Exit Sub
	End If

'This is the REAL call blank out the rows and columns
    Call BlankLabels(objPivotTable)
    
    objItem.Deactivate
		
End Sub
'**********************************************************************
Sub BlankLabels(objPivotTable As PivotTable)
'Purpose: Deletes all row and column labels in a Pivot Table
'Assumptions: The Pivot Table is already activated
'Effects: Row and column labels will be empty (null)
'Inputs: objPivotTable -- the selected Pivot Table
'Return Values: Pivot Table with Row and Column labels deleted

 'Declare SPSS-specific objects
    Dim objColLabels As ISpssLabels
    Dim objRowLabels As ISpssLabels
    Dim objDataCells As ISpssDataCells

'Declare various integer indices
    Dim lngRowNum As Long
    Dim lngColNum As Long
    Dim lngNumCols As Long
    Dim lngNumRows As Long
              
'Instantiate SPSS objects
    Set objDataCells = objPivotTable.DataCellArray()
    Set objColLabels = objPivotTable.ColumnLabelArray()
    Set objRowLabels = objPivotTable.RowLabelArray()
    
    
	lngNumCols = objColLabels.NumColumns
	lngNumRows = objColLabels.NumRows
	
	
	'Extracting column labels
	'The label at (1,N) does not exist, so do not access it
	       
	For lngRowNum = 0 To lngNumRows - 1
	    For lngColNum = 0 To lngNumCols - 1
	        If Not IsNull(objColLabels.ValueAt(lngRowNum,lngColNum)) Then
	            objColLabels.ValueAt(lngRowNum,lngColNum) = ""
	        End If
	    Next lngColNum
	Next lngRowNum
	
	lngNumCols = objRowLabels.NumColumns
	lngNumRows = objRowLabels.NumRows
	 
	'Extracting row labels       
	'The label at (N,1) does not exist, so do not access it
	
	For lngRowNum = 0 To lngNumRows - 1
	    For lngColNum = 0 To lngNumCols - 1
	        If Not IsNull(objRowLabels.ValueAt(lngRowNum,lngColNum)) Then
	            objRowLabels.ValueAt(lngRowNum,lngColNum) = ""
	        End If
	    Next lngColNum
	Next lngRowNum
    
End Sub
'*******************************************************************
Related pages

...

Navigate from here