PDA

View Full Version : Sleeper: Paste from Word to Excel



Anne Troy
05-25-2004, 04:05 PM
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
05-27-2004, 12:06 PM
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
05-27-2004, 12:10 PM
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
05-27-2004, 12:17 PM
I am not a Word VBA programmer. I just did the usual Macro Record trick.
I hope this does what you want.

xXLdev
05-27-2004, 12:19 PM
Side note:

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

Anne Troy
05-27-2004, 12:30 PM
Not sure what you mean, Cesar?

xXLdev
05-27-2004, 02:12 PM
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
05-27-2004, 02:17 PM
Actually, I think that setting is whether you want to view them, not USE your signature.

shades
05-28-2004, 08:14 PM
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