title: Paste MS Word revisions into Excel with VBA, maintaining formatting
body: <p>I've searched without success for how to do this...any help much appreciated.</p>
  
  <p>I've written a VBA macro that will go through an MS Word doc, identify each <em>revision</em> (eg, deletion/insertion), and put the revised text into a table in a new Word doc.  In the additional table columns, I put additional relevant information like the original (pre-revision) text, the name/initials of the reviser, page number of the revision, etc.</p>
  
  <p><strong>I want to do the same thing but put the info into Excel instead</strong>, <em>while retaining the formatting for</em> revisions (underscore=insertion,strikethrough = deletion).   I know this must be possible because when I 'manually' copy-and-paste my new 'revision table' into Excel, the formatting I want is preserved.  But when I try to code this step, that doesn't happen.  Tried recording a macro as I manually cut-and-pasted, but it didn't really help.   </p>
  
  <p>Here's my code to put the info into a new Word doc:</p>
  
  <pre><code> Sub TabulateRevisions()
          Dim oDoc As Document, nDoc As Document
          Dim PP As Paragraph
          Dim SS As Range
          Dim RR As Revision
          Dim r As Long, n As Long
          r = 2
          n = 1
  
  
  Set oDoc = ThisDocument
      oDoc.ActiveWindow.View.MarkupMode = wdInLineRevisions
          'Need to set this off so the cut and paste gets carries teh inserts and deletions
          If oDoc.TrackRevisions = True Then
              oDoc.TrackRevisions = False
          End If
  
          Documents.Add
  Set nDoc = ActiveDocument
  nDoc.PageSetup.Orientation = wdOrientLandscape
          nDoc.Tables.Add nDoc.Range, 1, 10
  
      For Each PP In oDoc.Paragraphs
              For Each SS In PP.Range.Sentences
                  For Each RR In SS.Revisions
                      'If RR.Type = 1 Or RR.Type = 2 Then 'uncomment to restrict revision types
                      SS.Select
                      On Error Resume Next 'this is just to prevent an error message telling me 'selection is marked as deleted'
                      SS.Copy
  
                      nDoc.Tables(1).Rows.Add
                      nDoc.Tables(1).Cell(r, 1).Range.Select
                      Selection.PasteAndFormat(wdFormatOriginalFormatting)
                      nDoc.Tables(1).Cell(r, 2).Range.Select
                      Selection.PasteAndFormat(wdFormatOriginalFormatting)
                      nDoc.Tables(1).Cell(r, 3).Range.Text = SS.Text
                      nDoc.Tables(1).Cell(r, 4).Range.Text = RR.Range.Text
                      nDoc.Tables(1).Cell(r, 6).Range.Text = r
                      If RR.Type = 1 Then
                          nDoc.Tables(1).Cell(r, 7).Range.Text = "insertion"
                      ElseIf RR.Type = 2 Then
                          nDoc.Tables(1).Cell(r, 7).Range.Text = "deletion"
                      End If
                      r = r + 1
                      ' End If
                  Next RR
              Next SS
          Next PP
  
          'Code below rejects all revisions in col 2 of nDoc table--these remain in col 1
          Dim CC As Cell
          For Each CC In nDoc.Tables(1).Columns(2).Cells
              n = n + 1
              For Each RR In CC.Range.Revisions
                  RR.Reject
              Next RR
          Next CC
      End Sub
  </code></pre>
  
  <p>Sorry; that's a bit long.  I've tried to tweak this code so that instead of creating a new Word doc, it opens an Excel wb and puts the info in there.  For the non-formatted info that's no problem, but I can't figure out how to retain formatting, like I did in col 1 of my nDoc.Tables(1).  I've tried various approaches, such as:</p>
  
  <pre><code>xlWB.worksheets(1).Cells(r, 1).Select 'or:
  xlWB.worksheets(1).Cells(r, 1).Activate 'followed by:
  ActiveSheet.Range.PasteAndFormat (wdFormatOriginalFormatting) 'and various other permutations of Word style commands
  'or:
  ActiveSheet.Range.PasteSpecial xlPasteAll 'and various other permutations of Excel-style commands
  </code></pre>
  
  <p>I've even resorted to trying to code to simply copy-and-paste my nDoc.Tables(1) into Excel (as that actually works when I do it manually), but no success.</p>
  
  <p>Any advice?  I should probably mention that I've got Office 2010, Windows 8.</p>
  
  <p>Thanks to anyone who's read this far!</p>