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
'Title:
'Remove % signs in Pivot Tables

'Description:
'Q. 
'I'm using SPSS 7.5 or higher. 
'I would like to remove % in a Pivot Table.

'A. 
'Below is a script which will make the changes for the currently selected Pivot Table.
'Sub Main may be modified or discarded; however, the RemovePctSign subroutine
'will probably not need modification should you wish to use it in your own
'scripts, or Call it from an Autoscript routine.
'This script is a modification of AnswerNet Solution ID:  100007757
'Posted to SPSSX-L list by Raynald Levesque on 2003/03/17

'Begin Description
'RemovePctSign removes % signs from Pivot Table.
'End Description

Sub Main()
Dim objPivot As PivotTable 
Dim objItem As ISpssItem
Dim bolFoundOutput As Boolean
Dim bolFoundPivot As Boolean

'Pivot table must be activated
Call GetFirstSelectedPivot(objPivot, objItem, bolFoundOutput, bolFoundPivot)
'postpone drawing until we're finished 
objPivot.UpdateScreen = False 

'set all cell formats to 2 decimal digits
Call RemovePctSign(objPivot, 2)

objPivot.UpdateScreen = True
objItem.Deactivate
'sometimes, the PivotTable Height and Width are not updated correctly 
're-activating the table will work around this 
objItem.ActivateTable 
objItem.Deactivate 
End Sub 


Sub RemovePctSign(objPivot As PivotTable, intDigits As Integer)
'Changes the number of Decimal Digits for *all* cells containing "%"
Dim lngRow As Long, lngCol As Long
Dim objDataCells As ISpssDataCells 

Set objDataCells = objPivot.DataCellArray 
	With objDataCells
		For lngRow = 0 To .NumRows - 1
			For lngCol = 0 To .NumColumns - 1
			strFormat=.NumericFormatAt(lngRow,lngCol)
			If Not IsNull (.ValueAt (lngRow, lngCol)) And InStr(strFormat,"%")>0 Then
			'Debug.Print .ValueAt (lngRow, lngCol) & " " & .NumericFormatAt(lngRow,lngCol)
			.NumericFormatAt(lngRow,lngCol)= "#.#"
			End If
			Next
		Next
	End With
objPivot.Autofit
End Sub