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

robnun · #1 01-02-2025, 11:32 AM

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!

Code:

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 · #2 01-02-2025, 01:02 PM

Quote:

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 · #3 01-02-2025, 01:22 PM

Hi! Try this code:

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

robnun · #4 01-03-2025, 06:24 AM

Quote:

Originally Posted by vivka

Hi! Try this code:

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 · #5 01-03-2025, 10:24 AM

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:

Code:

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 · #6 01-02-2025, 01:32 PM

Your Find/Replace code could be reduced to:

Code:

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 · #7 01-02-2025, 02:28 PM

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

macropod · #8 01-03-2025, 12:52 PM

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:

Code:

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
paste from clipboard to selected paragraphs	yacov	Word VBA	5	02-04-2024 01:41 PM
Replicate the copy/paste function, without involving the clipboard	alex100	Word VBA	8	11-09-2020 08:19 AM
Need to copy specific formated text from Word and paste from clipboard into a console application	Userx	Word VBA	0	10-09-2020 05:58 AM
Error with Clipboard - Getting Spaces Out of Clipboard	StephenRay	Word VBA	14	09-27-2017 01:07 PM
Find & selected paras with highlighted text & copy to clipboard	marceepoo	Word VBA	1	09-14-2012 08:20 PM