View Single Post
 
Old 02-06-2022, 10:39 PM
gmayor's Avatar
gmayor gmayor is offline Windows 10 Office 2019
Expert
 
Join Date: Aug 2014
Posts: 4,137
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

If you copy the required text from Excel, the following will replace the text in the table. The table has a text box in the cell. If you don't want to keep that text box and its content, remove the marked section from the code.
Code:
Sub BayTag()
Dim sValue As String
    sValue = GetClipBoard
    If Not sValue = "" Then
        If MsgBox("Paste the value :" & vbCr & sValue, vbYesNo) = vbYes Then
            AddPara ActiveDocument, sValue
        End If
    End If
End Sub

Private Function GetClipBoard() As String
Dim oData As DataObject
Dim testClip As String
    On Error Resume Next
    Set oData = New DataObject
    oData.GetFromClipboard
    testClip = oData.GetText
    If Err.Number > 0 Then
        MsgBox "The clipboard is empty", vbCritical
        GetClipBoard = ""
    Else
        GetClipBoard = testClip
    End If
lbl_Exit:
    Err.Clear
    Set oData = Nothing
    Exit Function
End Function

Private Sub AddPara(oDoc As Document, sText As String)
Dim oRng As Range
Dim oShape As Range
Dim bProtected As Boolean
    'Unprotect the file
    If oDoc.Tables.Count < 3 Then
        MsgBox "Incompatible document", vbCritical
        GoTo lbl_Exit
    End If
    If Not oDoc.ProtectionType = wdNoProtection Then
        bProtected = True
        oDoc.Unprotect Password:=""
    End If
    Set oRng = oDoc.Tables(3).Range.Cells(2).Range
    oRng.End = oRng.End - 1

    'If you want to keep the content of the text box.....
    If oRng.ShapeRange.Count > 0 Then
        oRng.ShapeRange(1).Select
        Set oShape = oDoc.Tables(3).Range.Cells(4).Range
        oShape.Collapse 1
        oShape.Text = vbCr
        oShape.Collapse 0
        oShape.FormattedText = Selection.FormattedText
    End If
    '..... end of text box code

    oRng.Paste
    'Reprotect the document.
    If bProtected = True Then
        oDoc.Protect _
                Type:=wdAllowOnlyFormFields, _
                NoReset:=True, _
                Password:=""
    End If
lbl_Exit:
    Set oRng = Nothing
    Exit Sub
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote