Find and replace or copy and paste from excel to word

Question

I'm trying do generate some files docx of data from excel to word. I'm just learning the basic of VBA, so I lasted a few hours to find this a find and replace logical. But at the time I tried with a lot of text, more than 255 characteres it's not worked well.

Maybe you can find some simple solution. This is the code abelow:

Sub gera_plano()

Set objWord = CreateObject("Word.Application")

objWord.Visible = True

Set arqPlanos = objWord.Documents.Open(ThisWorkbook.Path & "\Modelo de Plano de Aula 
(macro).docx")
Set conteudoDoc = arqPlanos.Application.Selection

For colTab = 1 To 20

 conteudoDoc.Find.Text = Cells(1, colTab).Value
 conteudoDoc.Find.Replacement.Text = Cells(2, colTab).Value
 conteudoDoc.Find.Execute Replace:=wdReplaceAll
  
 Next

 arqPlanos.SaveAs2 (ThisWorkbook.Path & "\Planos\Aula - " & Cells(2, 3).Value & " -T" & Cells(2, 
1).Value & ".docx")

arqPlanos.Close
 objWord.Quit

Set arqPlanos = Nothing
Set conteudoDoc = Nothing
Set objWord = Nothing

MsgBox ("Plano gerado com sucesso!")

End Sub

Thank you very much for your helop

You could shorten the Find expressions via the judicious use of *wildcards* and a *wildcard* Find. If the replacement srings are also more than 255 characters, they could be copied from Excel to the clipboard, and the Replacement expression in all cases could be simplified to ^c. — macropod, May 13 '22 at 04:08
Hello, unfortunately I still have a very basic level to understand this change. I tried but without success and the way you said it seems to be so simple. Would it be too much to ask for you to change the code for me? Really thank you! — Rogério Bonorino, May 14 '22 at 00:23

score 0 · Answer 1 · answered May 16 '22 at 00:46

Modified, your macro would look like:

Sub gera_plano()
' Note: The following code requires a reference to the
' MS Forms 2.0 Object Library, set in the VBE via Tools|References
' typically found in: C:\Windows\System32; or
' C:\Program Files (x86)\Microsoft Office\root\vfs\SystemX86
Dim objWord As Object, arqPlanos As Object, MyData As DataObject, strFnd As String
Set objWord = CreateObject("Word.Application")
Set MyData = New DataObject
Set arqPlanos = objWord.Documents.Open(ThisWorkbook.Path & "\Modelo de Plano de Aula(macro).docx")
With arqPlanos
  For colTab = 1 To 20
    strFnd = Cells(1, colTab).Text
    MyData.SetText Cells(2, colTab).Text
    MyData.PutInClipboard
    With .Find
      .MatchWildcards = True
      .Text = strFnd 
      .Replacement.Text = "^c"
      .Execute Replace:=wdReplaceAll
    End With
  Next
  .SaveAs2 (ThisWorkbook.Path & "\Planos\Aula - " & Cells(2, 3).Value & " -T" & Cells(2, 1).Value & ".docx")
  .Close
End With
objWord.Quit
Set arqPlanos = Nothing: Set objWord = Nothing
MsgBox ("Plano gerado com sucesso!")
End Sub

You would still need to do the work to convert the longer strings, especially, to the wildcard format. The links below explain how to use wildcards:

https://wordmvp.com/FAQs/General/UsingWildcards.htm

https://support.microsoft.com/en-us/office/examples-of-wildcard-characters-939e153f-bd30-47e4-a763-61897c87b3f4

Find and replace or copy and paste from excel to word

1 Answers1