Date: Sat, 5 Apr 2003 12:11:29 -0500
Reply-To: Raynald Levesque <rlevesque@videotron.ca>
Sender: "SPSSX(r) Discussion" <SPSSX-L@LISTSERV.UGA.EDU>
From: Raynald Levesque <rlevesque@videotron.ca>
Subject: Re: NEARLY get rid of '%'signs via script-language
In-Reply-To: <00c201c2fac8$9c70da40$fe78a8c0@boettchermafo.de>
Content-type: text/plain; charset=iso-8859-1
Hi
I am using V11.5.1 for Windows. With that version, the script included in
your email below works correctly even when I delete some % cells before
running it.
Which version do you use?
Raynald Levesque rlevesque@videotron.ca
Visit my SPSS Pages http://pages.infinit.net/rlevesqu/index.htm
-----Original Message-----
From: Werner Mueller [mailto:w.mueller@boettcher-mafo.de]
Sent: April 4, 2003 11:38 AM
To: SPSSX-L@LISTSERV.UGA.EDU
Cc: Raynald Levesque
Subject: NEARLY get rid of '%'signs via script-language
Dear List members, dear Mr. Levesque,
about 3 weks ago I received a preliminary version of a script for a
solution for my problem to get rid of the % signs behind the cross-tables
values for all crosstab-Pivot-Tables in a given Output window.
First off all: Thank you very much!
I tried to understand this script, found some other scripts at SPSS and also
at Mr. Levesque's homepage and after all I tried to adopt the script and it
really workes (nearly all the times). BUT, I have not learned Visual or Sax
Basic! I do not have a book describing it (do you know one?)
I just adopted/combined the first script from Mr. Levesque with some
sccripts from SPSS to make it work for all Tables in a output-window.
Now I have a good assumption in what cases /under what circumstances it does
NOT work, it simply closes SPSS without warning.
If the cell in the crosstab-pivot-table does not contain any value, this
(SPSS closes) happens!
I thought, that the code (see below) allready accounts for this case
...
If Not IsNull (.ValueAt (lngRow, lngCol)) ... (Full script see below)
...
But if the script tries to handle an empty/null - cell of the pivot table
(you can see this via the debug.print-lines), SPSS closes .
Can someone help me?
Kind regards
Werner Müller
Werner Mueller
V.Böttcher Marktforschung
Hugo-Viehoff-Str. 84
D-40468 Düsseldorf
GERMANY (Europe)
Tel: ++49 (0) 211-41 86 50
Fax: ++49 (0) 211-41 86 519
eMail: w.mueller@boettcher-mafo.de
----------------------------------------------------------------------------
----------
*example-data from spss.
GET FILE='C:\Programme\SPSS\1991 US Sozialerhebung.sav'.
* one empty cell .
CROSSTABS
/TABLES=region BY prob1
/FORMAT= AVALUE TABLES
/CELLS= COUNT COLUMN .
__________________________________________________________________
* Start of the script.
Sub Main
'Purpose: apply some cell-changes to all %-cells
'of ALL Pivot Tables in a given OUTPUT-WIndow
'Assumptions: there is an open Output Doc (Navigator)
'Effects: deletes %sign and makes %values bold
'Inputs: none
'Return Values: none
Dim objDocuments As ISpssDocuments ' SPSS documents.
Dim objOutputDoc As ISpssOutputDoc ' Output document
Dim objItems As ISpssItems ' Output Navigator items
Dim objPivotTable As PivotTable ' The Pivot Table
Dim i As Integer
'Get list of documents in SPSS.
Set objDocuments = objSpssApp.Documents
' Get designated document only if there is at least one output document.
' Omitting this test results in a error message.
If objDocuments.OutputDocCount > 0 Then
' Get the currently designated output document.
Set objOutputDoc = objSpssApp.GetDesignatedOutputDoc
Else
' If no navigator window exists, quit the script.
' comment the following line out and the script will go away silently.
MsgBox "Please open an output window before running this script.",
vbExclamation, "Script Error"
Exit Sub
End If
' Get the outline tree from the Navigator.
Set objItems = objOutputDoc.Items
' Get each item in the Navigator.
For i = 0 To objItems.Count - 1
Set objItem = objItems.GetItem(i) 'Get each item in turn.
If objItem.SPSSType = SPSSPivot Then 'Check to see if it's a
PivotTable
Set objPivotTable = objItem.ActivateTable() 'Activate the pivot table.
objPivotTable.UpdateScreen = False 'Defer drawing until
later.
' set all cell formats to x decimal digits without %sign and make the
digits bold
Call RemovePctSign(objPivotTable, 0, 2)
' do all the drawing at once
objPivotTable.UpdateScreen = True
' Clean-up time: Always remember to Deactivate when finished.
' note that it's the Item, not the Pivot Table, which is deactivated,
' just as it was the Item that was Activated.
objItem.Deactivate
End If
Next
End Sub
' ------------------------------------------------------------------------
Sub RemovePctSign(objPivot As PivotTable, intDigits As Integer, IntStyle 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
dummyrow = lngRow + 1
dummycol = lngCol + 1
strFormat=.NumericFormatAt(lngRow,lngCol)
'Debug.Print "#Rows:" & .NumRows & " #Cols: " &
.NumColumns & " "
If Not IsNull (.ValueAt (lngRow, lngCol)) And
InStr(strFormat,"%")>0 Then
'No difference in behaviour with IsEmpty instaed of IsNull: SPSS ends
' If Not IsEmpty (.ValueAt (lngRow, lngCol)) And
InStr(strFormat,"%")>0 Then
'Debug.Print .ValueAt (lngRow, lngCol) & " " &
.NumericFormatAt(lngRow,lngCol)
.NumericFormatAt(lngRow,lngCol)= "#.#"
Debug.Print "# Rows:" & .NumRows & " #Cols: " &
.NumColumns & _
" in Row:" & dummyrow & " in Col: " &
dummyCol & " Wert: " & .ValueAt(lngRow, lngCol)
.HDecDigitsAt (lngRow, lngCol) = intDigits
.TextStyleAt (lngRow, lngCol) = IntStyle
End If
Next
Next
End With
objPivot.Autofit
End Sub
