I am automating word using vba. Need Help replacing paragraph with variable information

Puneet Singh · #1 02-04-2022, 10:36 AM

I am attaching file in this thread.
I want to automate one paragraph in attach document. Paragraph is in front of MISC NOTES:
That paragraph is dyanamic i recieved a value and i need to paste that in place of that paragraph first i delete the existing one and paste a new one.

Charles Kenyon · #2 02-04-2022, 11:39 AM

Just a note that I moved your question into the vba forum.

gmayor · #3 02-04-2022, 11:38 PM

MISC NOTES: is in table 3 of the form. There is an empty paragraph between tables 2 and 3, which I assume is where you want the value? That being the case you can use the following macro to write the value in that paragraph. Macro1 is an example of how to use that macro to write data from the clipboard to the paragraph.

Code:

Sub Macro1()
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 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
    With oRng
        .Start = .Previous.Paragraphs.Last.Range.Start
        .Collapse 1
        .End = oRng.Paragraphs(1).Range.End - 1
        .Text = sText
    End With
    '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

Puneet Singh · #4 02-05-2022, 07:14 AM

Thanks for the response
I am getting compile error and also i attach the file i want to save the paragraph in grey area.

gmayor · #5 02-06-2022, 02:02 AM

All the code I posted should be copied into the module. Rename Macro1 as BAYTAG and delete the three CALLs. Macro1, as posted, calls the two supplementary macros. They are not separate processes.

Puneet Singh · #6 02-06-2022, 09:40 AM

Ok Thanks Graham...Sorry for bothering. Actually what the flow is 1st we get the paragraph value from excel Attaching image of excel and want to paste the same paragraph on word first we have to delete the existing paragraph and will paste what we copy from excel. which is already attached. Please share some input and then will dig into that.
This is last. Thanks

gmayor · #7 02-06-2022, 10:39 PM

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

Puneet Singh · #8 02-07-2022, 04:40 AM

Thanks Graham...I had checked the code and getting below error...
I think i am doing something wrong actually i am from RPA background and want to automate this...I try all thing but all in vain.

On button click i want to extract data from excel and have to paste that data on word file which we have already a given format.

gmayor · #9 02-07-2022, 05:26 AM

That is not an error message. It simply reports what is on the clipboard. Clearly you haven't copied the text from Excel as it is showing the code listing you copied from the forum.
If you want to include the collection of data from the worksheet then you may need to make some changes to:

Code:

Sub BayTag()
Dim sValue As String
    sValue = GetExcelData
    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 GetExcelData() As String
Dim strWorkbook As String: strWorkbook = "C:\Path\Job Aid Bay.xlsm" 'The path of the workbook
Dim xlApp As Object
Dim xlBook As Object
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err Then
        Set xlApp = CreateObject("Excel.Application")
    End If
    On Error GoTo 0
    Set xlBook = xlApp.Workbooks.Open(FileName:=strWorkbook)
    xlApp.Visible = True
    GetExcelData = xlBook.Sheets("MAIN").Range("B12") 'the Excel cell to copy
    xlBook.Close savechanges:=False
lbl_Exit:
    Err.Clear
    Set xlBook = Nothing
    Set xlApp = 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.Text = sText
    '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

Puneet Singh · #10 02-07-2022, 05:40 AM

Thanks sir and where we keep our word document in below code. I mean where we open the word doc and paste the paragraph in below code.

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.Text = sText
'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

Puneet Singh · #11 02-07-2022, 07:22 AM

Please check this error. Sorry please look.
Please look this is final one

gmayor · #12 02-07-2022, 10:14 PM

This is a Word macro, not an Excel macro! It is run from the open document.

Puneet Singh · #13 02-07-2022, 10:45 PM

Thankyou sir Thankyou so much its working. Will do samething for attach doc also.
in this i want to change the the paragraph. I highlight the paragraph in bold will do the same thing here also. how you find the paragraph number.

gmayor · #14 02-08-2022, 12:26 AM

Each line in that document is a separate paragraph.

There are thirty one paragraphs highlighted in bold?

There are six groups of paragraphs highlighted in bold.

What is it that you want to change them for? Do you want to change the whole lot for the content of a cell in Excel? Or do you want to change the blocks for the contents of six cells? Or do you want to replace 31 paragraphs with the content of 31 cells? Whichever it is you need to identify the cell(s).
Is the block always going to end with "*****THIS IS A COMPLETE UNDERTAKING*****"?

Puneet Singh · #15 02-08-2022, 02:01 AM

Whatever highlighted in bold i need to change that.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Replacing random paragraph endings in text	jp91306	Word	3	04-23-2019 03:32 PM
I want to create a bar chart of multiple variable. Then I need to draw trend lin of those variable	shimulsiddiquee	Excel	1	05-16-2017 07:39 AM
Replacing paragraph formatting before column break also changes the next paragraph after the break	jjmartin1340	Word	3	09-21-2015 10:50 PM
Run Time Error '91': Object variable or With block variable not set using Catalogue Mailmerge	Berryblue	Mail Merge	1	11-13-2014 05:36 PM