locked
Help Creating a Macro RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello, 

    I am new to creating macros and need help. I want to create a macro that will streamline the copying and pasting I do between documents. Specifically, I need the macro to do the following:

    • Copy text I have selected in one document (for this example, let's call it "Document A")
    • Activate the second document ("Document B") and perform a search of the copied text
    • Select the first instance of the copied text (which happens to be listed in the first column of a table)
    • Tab (or move) to the adjacent cell and make a selection
    • Copy the selection
    • Activate Document A
    • Add a comment to the selected word from the first step
    • Paste the copied text from Document B in the Comment bubble

    I am struggling with getting the copied text from the Clipboard. I have read that I need to activate the available reference, "Microsoft Forms 2.0 Object Library," which I have done, and use the following code: 

     

    Dim DataObj As MSForms.DataObject
     Set DataObj = New MSForms.DataObject
     DataObj.GetFromClipboard

    strPaste = DataObj.GetText(1)

    I'm not exactly sure how to use the above code with what I have created below. Can someone please help?


    Sub CopyandPasteMacro()
       If Documents.Count <> 2 Then
          MsgBox "You must have two documents open!"
          Exit Sub
       End If

       Set ThisDoc = ActiveDocument

       If ThisDoc = Documents(1) Then
          Set OtherDoc = Documents(2)
       Else
          Set OtherDoc = Documents(1)
       End If

       OtherDoc.Activate
       Selection.MoveRight Unit:=wdCell
       Selection.Copy
       ThisDoc.Activate
       Selection.Comments.Add Range:=Selection.Range
       Selection.PasteAndFormat (wdFormatOriginalFormatting)

       Set ThisDoc = Nothing
       Set OtherDoc = Nothing

    End Sub

    Monday, December 7, 2015 6:58 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    There isn't any need to work with or manipulate the clipboard. Neither is there any need to select anything other than the initial text you want to comment. Try something based on:

    Sub Demo()
    Application.ScreenUpdating = False
    Dim RngSel As Range, RngSrc As Range, StrFnd As String, Cmnt As Comment
    If Documents.Count <> 2 Then
      MsgBox "You must have two documents open!"
      Exit Sub
    End If
    Set RngSel = Selection.Range: StrFnd = RngSel.Text
    With ActiveWindow.Next.Document.Range
      With .Find
        .ClearFormatting
        .Replacement.ClearFormatting
        .Forward = True
        .Format = False
        .MatchCase = True
        .MatchWholeWord = True
        .MatchWildcards = False
        .MatchAllWordForms = False
        .Text = StrFnd
        .Replacement.Text = ""
        .Execute
      End With
      If .Find.Found = True Then
        If .Information(wdWithInTable) = True Then
          Set RngSrc = .Cells(1).Next.Range
          With RngSrc
            .End = .End - 1
            Set Cmnt = RngSel.Comments.Add(RngSel, "")
            Cmnt.Range.FormattedText = .FormattedText
          End With
        End If
      End If
    End With
    Set RngSel = Nothing: Set RngSrc = Nothing: Set Cmnt = Nothing
    Application.ScreenUpdating = True
    End Sub

    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Proposed as answer by George123345 Tuesday, December 22, 2015 8:17 AM
    • Marked as answer by George123345 Wednesday, December 23, 2015 7:41 AM
    Tuesday, December 8, 2015 1:07 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    There isn't any need to work with or manipulate the clipboard. Neither is there any need to select anything other than the initial text you want to comment. Try something based on:

    Sub Demo()
    Application.ScreenUpdating = False
    Dim RngSel As Range, RngSrc As Range, StrFnd As String, Cmnt As Comment
    If Documents.Count <> 2 Then
      MsgBox "You must have two documents open!"
      Exit Sub
    End If
    Set RngSel = Selection.Range: StrFnd = RngSel.Text
    With ActiveWindow.Next.Document.Range
      With .Find
        .ClearFormatting
        .Replacement.ClearFormatting
        .Forward = True
        .Format = False
        .MatchCase = True
        .MatchWholeWord = True
        .MatchWildcards = False
        .MatchAllWordForms = False
        .Text = StrFnd
        .Replacement.Text = ""
        .Execute
      End With
      If .Find.Found = True Then
        If .Information(wdWithInTable) = True Then
          Set RngSrc = .Cells(1).Next.Range
          With RngSrc
            .End = .End - 1
            Set Cmnt = RngSel.Comments.Add(RngSel, "")
            Cmnt.Range.FormattedText = .FormattedText
          End With
        End If
      End If
    End With
    Set RngSel = Nothing: Set RngSrc = Nothing: Set Cmnt = Nothing
    Application.ScreenUpdating = True
    End Sub

    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Proposed as answer by George123345 Tuesday, December 22, 2015 8:17 AM
    • Marked as answer by George123345 Wednesday, December 23, 2015 7:41 AM
    Tuesday, December 8, 2015 1:07 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi writeforaliving89,

    Please try the Paul's suggestion first, if you have any further question about coding, please ask in MSDN forum:

    https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=worddev

    Regards,

    George Zhao
    TechNet Community Support

    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.
    • Proposed as answer by George123345 Wednesday, December 23, 2015 7:41 AM
    Tuesday, December 22, 2015 8:19 AM