Importing text values from Excel to Word in a desired

aftbrah

Board Regular
Joined
Sep 25, 2011
Messages
51
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

Board Regular
Joined
Sep 25, 2011
Messages
51
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.
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

EdNerd

Active Member
Joined
May 19, 2011
Messages
398
Try this. It worked on my system running Windows 10 and Office 2013.
VBA Code:
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

Board Regular
Joined
Sep 25, 2011
Messages
51
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

Active Member
Joined
May 19, 2011
Messages
398
Can you put the address in the Excel worksheet somewhere?
If so, then you can code the doc file path as so:
VBA Code:
strDoc = wks.Range("A1").Value
strDoc = "C:\" & strDoc & "\Reports\Template.docx"
 

aftbrah

Board Regular
Joined
Sep 25, 2011
Messages
51
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

Active Member
Joined
May 19, 2011
Messages
398
-- Did the correct template open?
-- Did you replace [First Text] and [Second Text] in the code with the actual text you need to replace?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,359
Messages
5,601,153
Members
414,431
Latest member
JustmemyselfandI

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top