Paste from Word to Excel

[Anne Troy]

So, my table contains paragraph returns, and I want to maintain them, but paste my data to Excel.

Anybody want to write a routine that'll:

In Word
Select the table in which the cursor resides (Table-Select-table)
Find all paragraph returns (type ^p into find/replace or use that chr, I forget)
Replace with some oddball character like the pipe
Copy the table contents to the clipboad and wait for user to choose paste

In Excel
Paste

[xXLdev]

Here is the Word macro. Need to have the cursor in the table.

Sub ReplaceRetsCopy()
Selection.Tables(1).Select
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
    .Text = "^p"
    .Replacement.Text = "||"
    .Forward = True
    .Wrap = wdFindStop
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Copy
End Sub

Here is the Excel macro. Paste table into Excel. Run macro. Macro assumes that the table is still selected.

Sub InsertReturns()
Dim str As String
Dim cell As Range
For Each cell In Selection
    str = cell.Value
    cell.Value = Replace(str, "||", Chr(10))
Next
End Sub

You could automate this from start to end.

[Anne Troy]

Great. Word doesn't need all those FALSE thingees, so we'll chop them right out:

Sub ReplaceRetsCopy() 
 Selection.Tables(1).Select 
 Selection.Find.ClearFormatting 
 Selection.Find.Replacement.ClearFormatting 
  With Selection.Find
    .Text = "^p"
    .Replacement.Text = "||"
    .Forward = True
    .Wrap = wdFindStop
  End With 
 Selection.Find.Execute Replace:=wdReplaceAll 
 Selection.Copy 
End Sub 

[xXLdev]

I am not a Word VBA programmer. I just did the usual Macro Record trick.
I hope this does what you want.

[xXLdev]

Side note:

What is happening to the signatures. It shows up in the preview but not in the final post.

[Anne Troy]

Not sure what you mean, Cesar?

[xXLdev]

For the signature, I discovered that I had to allow my signature to be displayed. I had to go to the "User CP" to allow. The "Preview Option" has bug because it was showing the signature.

Also, for the Word macro, it changes the data. If you want to keep the clean you could either add Undo to the macro or do another Find/Replace.

[Anne Troy]

Actually, I think that setting is whether you want to view them, not USE your signature.

[shades]

Great. Word doesn't need all those FALSE thingees, so we'll chop them right out:

Sub ReplaceRetsCopy()
 Selection.Tables(1).Select
 Selection.Find.ClearFormatting
 Selection.Find.Replacement.ClearFormatting
  With Selection.Find
    .Text = "^p"
    .Replacement.Text = "||"
    .Forward = True
    .Wrap = wdFindStop
  End With
 Selection.Find.Execute Replace:=wdReplaceAll
 Selection.Copy 
End Sub

Just curious on the highlighted lines. Is VBA in Word like in Excel? So we can shorten the code from:

Selection.Tables(1).Select
 Selection.Find.ClearFormatting

to:

Selection.Tables(1).Find.ClearFormatting