Paste from Clipboard, Find and Replace All, Then Copy Results Back to The Clipboard

[robnun]

I am a macro novice and am trying to come up something that does the following in MS Word:

1) Paste the contents of the clipboard as plain text (into a Word document).

2) Find and replace all instances of <, >, and # with a dash.

3) Copy the resulting text back to the clipboard.

I found the following snippet online and it accomplishes steps 1-2 above, but I can't figure out how to copy the results back to the clipboard (Step 3). I am trying to use "ContentControl.Copy" but not sure where to add it into the existing code.



Thanks in advance for your help!

Sub FindReplaceAll()
Selection.Collapse Direction:=wdCollapseStart
Selection.PasteSpecial DataType:=wdPasteText
Dim StrFind As String, StrRepl As String
Dim FindItem As String, ReplaceItem As String
Dim i As Long
Dim aNote As Endnote
StrFind = "<,>#"
StrRepl = "-,-,-"
Set RngTxt = Selection.Range
For i = 0 To UBound(Split(StrFind, ","))
    FindItem = Split(StrFind, ",")(i)
    ReplaceItem = Split(StrRepl, ",")(i)
    Selection.HomeKey wdStory
    Selection.Find.ClearFormatting
    With Selection.Find
        .ClearFormatting
        .Replacement.ClearFormatting
        .Text = FindItem
        .Replacement.Text = ReplaceItem
        .Format = False
        .MatchWholeWord = True
        .MatchAllWordForms = False
        .MatchWildcards = False
        .Execute Replace:=wdReplaceAll
    End With
    For Each aNote In ActiveDocument.Endnotes
        With aNote.Range
          .Find.Execute FindText:=FindItem, ReplaceWith:=ReplaceItem, Replace:=wdReplaceAll
        End With
    Next aNote
Next i
End Sub

[Italophile]

Originally Posted by robnun

I am trying to use "ContentControl.Copy" but not sure where to add it into the existing code.

Why? There is nothing in your code to indicate that you are pasting anything into a Content Control.

[vivka]

Hi! Try this code:

Sub FindReplaceAll()

Dim RngTxt as range, StrFind As String, StrRepl As String
Dim i As Long
    StrFind = "<,>,#"
    StrRepl = "-,-,-"

    Selection.Collapse Direction:=wdCollapseStart
    Set RngTxt = Selection.range
    Selection.PasteSpecial DataType:=wdPasteText
    RngTxt.End = Selection.range.End
    RngTxt.Select

    For i = 0 To UBound(Split(StrFind, ","))
        With Selection.Find
            .ClearFormatting
            .Replacement.ClearFormatting
            .text = Split(StrFind, ",")(i)
            .Replacement.text = Split(StrRepl, ",")(i)
            .Format = False
            .MatchWholeWord = True
            .MatchAllWordForms = False
            .MatchWildcards = False
            .Execute Replace:=wdReplaceAll
        End With
    Next i
    Selection.Copy
End Sub

[macropod]

Your Find/Replace code could be reduced to:

With Selection.Find
  .ClearFormatting
  .Replacement.ClearFormatting
  .Execute Findtext:="[<>#]", ReplaceWith:="-", MatchWildcards:=True, Forward:=True, Wrap:=wdFindStop, Replace:=wdReplaceAll
End With

But, as implied in Italophile's reply, your question lacks context. It's not even apparent why you're doing the copy/paste activity, let alone what all the stuff in your code to do with footnotes has to do with it.

[macropod]

Cross-posted at: MS Answers
For cross-posting etiquette, please read: A message to forum cross posters - Excelguru

[robnun]

Originally Posted by vivka

Hi! Try this code:

Sub FindReplaceAll()

Dim RngTxt as range, StrFind As String, StrRepl As String
Dim i As Long
    StrFind = "<,>,#"
    StrRepl = "-,-,-"

    Selection.Collapse Direction:=wdCollapseStart
    Set RngTxt = Selection.range
    Selection.PasteSpecial DataType:=wdPasteText
    RngTxt.End = Selection.range.End
    RngTxt.Select

    For i = 0 To UBound(Split(StrFind, ","))
        Selection.Find.ClearFormatting
        With Selection.Find
            .ClearFormatting
            .Replacement.ClearFormatting
            .text = Split(StrFind, ",")(i)
            .Replacement.text = Split(StrRepl, ",")(i)
            .Format = False
            .MatchWholeWord = True
            .MatchAllWordForms = False
            .MatchWildcards = False
            .Execute Replace:=wdReplaceAll
        End With
    Next i
    Selection.Copy
End Sub

Thank you, Vivka. Your solution worked perfectly!

[vivka]

You are welcome, Robnun! Two remarks if you don't mind: 1) I've deleted one unneeded line in the code in Post 3; 2) I think the following code that uses the Macropod's idea is better for your case:

Sub FindReplaceAll_2()

Dim RngTxt As range
    Selection.Collapse Direction:=wdCollapseStart
    Set RngTxt = Selection.range
    Selection.PasteSpecial DataType:=wdPasteText
    RngTxt.End = Selection.range.End
    RngTxt.Select
        With Selection.Find
            .ClearFormatting
            .Replacement.ClearFormatting
            .text = "[<>#]"
            .Replacement.text = "-"
            .Forward = True
            .Wrap = wdFindStop
            .MatchWildcards = True
            .Execute Replace:=wdReplaceAll
        End With
    Selection.Copy
End Sub

[macropod]

You still haven't given any context, so it's impossible to know what the best way of handling the task is. All this copying, pasting & cutting seems to be a lot of pointless circumlocution.

That said, the code could be reduced to:

Sub Demo()
Application.ScreenUpdating = False
Dim Rng As Range: Set Rng = ActiveDocument.Range(0, 0)
With Rng
  .PasteSpecial DataType:=wdPasteText
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Execute Findtext:="[<>#]", ReplaceWith:="-", MatchWildcards:=True, Forward:=True, Wrap:=wdFindStop, Replace:=wdReplaceAll
  End With
  .Cut
End With
Set Rng = Nothing
Application.ScreenUpdating = True
End Sub