SPSS Pivot tables
in Microsoft Word

John Hendrickx

Due to a bug, SPSS pivot tables from version 8.0 to 10.05 that are pasted in Microsoft Word 95 or Word 97 will print each row double (provided the document is saved first). The Word macro available below fixes this problem. It transforms the table to tab delimted text, then changes it back to a table again. This procedure discards all text alignment in the SPSS table, so the macro uses left alignment for the first column, right alignment for all others. The macro also applies the "Simple1" autoformat. This can be altered to suit your taste.

This bug no longer occurs using either Word 2000 or SPSS 10.07. It also does not occur using WordPerfect.

Other solutions:

  • An SPSS script "Export_to_Word_RTF.sbs" is available at http://www.spss.com/tech/scptxchg/. This script will export all pivot tables in an output viewer document to Word. The Word macro below is an alternative for use on a table by table basis.
  • Set the vertical alignment of cells to "bottom". Tables will be printed correctly, but the problem will resurface if vertical alignment is set to "top" again. There's no standard menu item for setting vertical alignment of cells and it can't be done through "cell properties". You have to make the tables toolbar visible (View>Toolbars), then click on the rightmost button:
  • Right-click the table in SPSS and select "copy objects". This will paste the table in Word as a figure, with all formatting intact. However, only limited editting is possible using a figure editor.
  • Paste in Excel, then copy and paste in Word. The "FixSPSS" macro is still useful for autoformatting the table in Word.
  • Paste special as unformatted text in Word, but that method is rather overkill.

FixSPSS Word macro

Attribute VB_Name = "SPSSTab"
Sub fixSPSS()
    'SPSS pivot tables pasted from SPSS 8.0 to 10.05 into Microsoft Word
    'print each row double. This macro fixes this by converting the table
    'to text, then back to a table again. The "Simple1" autoformat is
    'applied as well (change this to suit your tastes) and all columns
    'except the first are right aligned.
    'To use, place the cursor in the table, press alt-F8 and
    'select "fixSPSS".
    'An SPSS script "Export_to_Word_RTF.sbs" is also available at the SPSS
    'site to export *all* SPSS pivot tables to Word.
    
    If Not Selection.Information(wdWithInTable) Then
        MsgBox prompt:="The cursor must be in a table" _
        & vbCr & "before running the fixSPSS macro", _
        Title:="fixSPSS error"
        Exit Sub
    End If
    
    Selection.Tables(1).Select
    With Selection
        .Font.Reset
        .Rows.ConvertToText
        
        'standard formatting with simple1
        .ConvertToTable AutoFit:=True, Format:=wdTableFormatSimple1, _
        ApplyBorders:=True, ApplyShading:=True, ApplyFont:=True, _
        ApplyColor:=True, ApplyHeadingRows:=True, ApplyLastRow:=False, _
        ApplyFirstColumn:=True, ApplyLastColumn:=False
        
        'uncomment this for no formatting, no borders
        '.ConvertToTable AutoFit:=True, Format:=wdTableFormatSimple1, _
        ApplyBorders:=False, ApplyShading:=False, ApplyFont:=False, _
        ApplyColor:=False, ApplyHeadingRows:=False, ApplyLastRow:=False, _
        ApplyFirstColumn:=False, ApplyLastColumn:=False
       
       'right align all columns ...
        .ParagraphFormat.Alignment = wdAlignParagraphRight
    End With
    'left align the first column
    Selection.Columns(1).Select
    Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft
End Sub

Installation

Use the following steps to install the fixSPSS macro in Word:

  1. Copy the fixSPSS macro to a plain text file and save it as "SPSStab.bas", or click here to download "SPSStab.bas". (Save to disk as a text file, then remove the ".txt" extention).
  2. In Word, press alt-F11 to open the Visual Basic editor (Tools>Macro>Visual Basic Editor"
  3. In the Visual Basic editor, press ctrl-M to import a Visual Basic macro (File>Import File).
  4. Open the file "SPSStab.bas". This will add a module called SPSSTab.
  5. Use alt-Q to close the Visual Basic editor.

Usage

Paste the SPSS pivot table in Word, then place the cursor somewhere in the table. Run the macro by pressing alt-F8, then select fixSPSS.

[Home]