Importing text values from Excel to Word in a desired

[aftbrah]

Hi everyone,

So I am looking to create a standardized word document with the correct values properly entered. How would I be able to pull values from different cells in an Excel sheet and auto populate it into Microsoft Word? I know there is a way. Any help would be kindly appreciated!

Thank you

 

[aftbrah]

Yes it would be a separate workbook for each property. Each property has its own folder on the hard drive/server which we can call a Property Folder for instruction sake. The templates in this case will be .docx and each Property Folder will contain a couple templates that have a similar first page, but different second pages thereafter. To save time and make all files look consistent, I am trying to achieve a VBA that will pull the values from the Workbook and insert them into their respective templates automatically. Hope this clarifies. Let me know if you require any further explanation and I will gladly provide.... and thanks again ?

If it makes it easier, I can log onto my computer at work tomorrow and pull the exact Workbook that I use and its corresponding Word docx template.

 

[EdNerd]

Try this. It worked on my system running Windows 10 and Office 2013.

Sub ReplaceTextInWordDoc()


''Early binding for Word - assumes reference set
''Assumes macro is in Excel and workbook is open


Dim appWD As Word.Application
Dim docWD As Word.Document
Dim rngWD As Word.Range


Dim wkb As Workbook
Dim wks As Worksheet
Dim rng As Excel.Range


Dim strDoc As String
Dim strFind As String
Dim strText As String


Set wkb = ActiveWorkbook
Set wks = wkb.ActiveSheet


strDoc = "C:\Your\Full\Document\Path\AndName\Here.docx"


On Error Resume Next
  Set appWD = GetObject(, "Word.Application")
  If Err.Number <> 0 Then
    Set appWD = CreateObject("Word.Application")
  End If
  Err.Clear
On Error GoTo 0


Set docWD = appWD.Documents.Open(strDoc)
appWD.Visible = True


docWD.SaveAs FileName:= _
      "C:\Your\New\Document\Path\AndName\Here.docx", _
      FileFormat:=wdFormatDocumentDefault


''*******************
''Repeat the block in between *** for each value that needs to be replaced


strFind = "[First Text]"
strText = wks.Range("A1").Value  'Replace A1 with actual cell address


Set rngWD = docWD.Content


  With rngWD.Find
    .Text = strFind
    .Replacement.Text = strText
    .Forward = True
    .Wrap = wdFindStop
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
  End With
  rngWD.Find.Execute Replace:=wdReplaceAll


''*******************


strFind = "[Second Text]"
strText = wks.Range("B1").Value  'Replace A1 with actual cell address


Set rngWD = docWD.Content


  With rngWD.Find
    .Text = strFind
    .Replacement.Text = strText
    .Forward = True
    .Wrap = wdFindStop
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
  End With
  rngWD.Find.Execute Replace:=wdReplaceAll


docWD.Save
docWD.Close
appWD.Quit
Set docWD = Nothing
Set appWD = Nothing


End Sub

 

[aftbrah]

This looks awesome! Just one last thing. How do I set a default file path without having to change the file path manually in the code each time? For example, the word docs will always be in "filepath\Reports\" such as the below

"C:\123 Street\Reports\Template.docx"
"C:\Main Street\Reports\Template.docx"
"C:\Broadway Ave\Reports\Template.docx"

etc

 

[EdNerd]

Can you put the address in the Excel worksheet somewhere?
If so, then you can code the doc file path as so:

strDoc = wks.Range("A1").Value
strDoc = "C:\" & strDoc & "\Reports\Template.docx"

 

[aftbrah]

Thanks for this. I had tried both of your codes including manually inserting file path in the code and no change was made to the .docx at all. Not sure why.... ughh lol

I am also using Windows 10 and running Office 2013...

 

[EdNerd]

-- Did the correct template open?
-- Did you replace [First Text] and [Second Text] in the code with the actual text you need to replace?