Transposing tables in place in Word using VBA

Question

I'm trying to write a method to transpose tables in Word using Visual Basic and the Excel object model. However, I'm having trouble using paste special with the copied Word object, since its not just plain text.

Is it possible to force a plain text paste with transpose? Is there a better way to do this?

My current code is as follows:

Public Sub transposeTable()

Selection.Copy
Dim oXlApp As New Excel.Application
Dim oXlBook As Excel.Workbook
Set oXlApp = CreateObject(Class:="Excel.Application")
Set oXlBook = oXlApp.Workbooks.Add
oXlBook.Sheets(1).Range("A1").Select
oXlApp.ActiveCell.PasteSpecial Transpose:=True
oXlBook.Sheets(1).Cells.SpecialCells(xlCellTypeConstants).Select
oXlApp.Selection.Copy
Selection.Paste
End Sub

The error I'm recieving is:

Run-time error '1004':
PasteSpecial method of Range class failed

Answer 1

Have you tried pasting without the formatting?

oXlApp.ActiveCell.PasteSpecial Paste:=xlPasteValues, Transpose:=True

Answer 2

I've managed to get it working now, using an intermediate paste step.

Public Sub transposeTable()

Selection.Cut
Dim oXlApp As New Excel.Application
Dim oXlBook As Excel.Workbook
Set oXlApp = CreateObject(Class:="Excel.Application")
Set oXlBook = oXlApp.Workbooks.Add
oXlBook.Sheets(1).Range("A1").Select
oXlApp.ActiveSheet.Paste
oXlBook.Sheets(1).Cells.SpecialCells(xlCellTypeConstants).Select
oXlApp.Selection.Copy
oXlBook.Sheets(2).Select
oXlBook.Sheets(2).Range("A1").Select
oXlApp.ActiveCell.PasteSpecial Transpose:=True
oXlBook.Sheets(2).Cells.SpecialCells(xlCellTypeConstants).Select
oXlApp.Selection.Copy
Selection.Paste
oXlBook.Close (False)
oXlApp.Quit
End Sub