Microsoft Office Forums - View Single Post

Guessed · 08-19-2021, 05:41 PM

I found this link to help late-bind the clipboard object Office VBA Tips: Late Binding MSForms.DataObject to access the ClipBoard from VBA.

Adapting your code to incorporate this as a function results in the following. I'm not sure what you think your replace code is meant to do but it doesn't actually replace anything.

Code:

Sub FindReplaceClip()
  Dim sFind As String
  sFind = funClipText
  With Selection.Find
    .ClearFormatting
    .Text = sFind
    .Replacement.Text = "RECORD BEGINS"
    .Forward = True
    .Wrap = wdFindContinue
    .Format = True
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    .Execute
  End With
  Selection.HomeKey Unit:=wdLine
  Selection.MoveUp Unit:=wdScreen, Count:=1
End Sub

Function funClipText() As String
  Dim MyClip As Object, sFind As String
  Set MyClip = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
  MyClip.GetFromClipboard
  funClipText = MyClip.GetText(1)
End Function