Use Excel data to populate a template in Word

Use Excel data to populate a template in Word. Create form letters from data listed in Excel. Check out my free templates and templates with code are available for purchase for $50 USD
www.easyexcelanswers.com/temp...
Check out my online training www.easyexcelanswers.com/courses.html
Are you struggling to update your Microsoft SQL Server data?
I recommend using well-known Excel to update and manage your data in SQL Server using the SQL Spreads Excel add-in.
Click sqlspreads.com/?pid=barbhende... to find out how SQL Spreads works best for non-technical end users and download our free 14-day trial. You will get two months free when purchasing an annual subscription using our link.
For more help visit my website www.easyexcelanswers.com or email me at [e-mail].
Contact me regarding customizing this template for your needs.
Click for online Excel Consulting www.calendly.com/easyexcelanswers
I am able to provide online help on your computer at a reasonable rate.
www.amazon.com/shop/barbhende...
I use a Blue condenser Microphone to record my videos, here is the link
amzn.to/37gyyGa
Check out Crowdcast for creating your webinars
app.linkmink.com/a/crowdcast/83
If you need to buy Office 2019 follow
amzn.to/2VX5dv8
I use Tube Buddy to help promote my videos
Check them out
www.Tubebuddy.com/easyexcelan...
Follow me on Facebook
/ easyexcel.answers
TWEET THIS VIDEO • Use Excel data to popu...
Follow me on twitter
easyexcelanswers
IG @barbhendersonconsulting
You can help and generate a translation to you own language
kzbin.info_cs_p...
*this description may contain affiliate links. When you click them, I may receive a small commission at no extra cost to you. I only recommend products and services that I've used or have experience with.
templates including code are available for $50 USD
How to insert VBA code in Excel
• How to insert VBA code...
code
Sub ReplaceText()
Dim wApp As Word.Application
Dim wdoc As Word.Document
Dim custN, path As String
Dim r As Long
r = 2
Do While Sheet1.Cells(r, 1) (not equal to) ""
Set wApp = CreateObject("Word.Application")
wApp.Visible = True
Set wdoc = wApp.Documents.Open(Filename:="C:\Users\screa\OneDrive
econstructionBH\Documents\Excel\welcome.dotx", ReadOnly:=True)
With wdoc
.Application.Selection.Find.Text = "||name||"
.Application.Selection.Find.Execute
.Application.Selection = Sheet1.Cells(r, 1).Value
.Application.Selection.EndOf
.Application.Selection.Find.Text = "||address||"
.Application.Selection.Find.Execute
.Application.Selection = Sheet1.Cells(r, 2).Value
.Application.Selection.EndOf
.Application.Selection.Find.Text = "||city||"
.Application.Selection.Find.Execute
.Application.Selection = Sheet1.Cells(r, 3).Value
.Application.Selection.EndOf
.Application.Selection.Find.Text = "||postal code||"
.Application.Selection.Find.Execute
.Application.Selection = Sheet1.Cells(r, 4).Value
.Application.Selection.EndOf
.Application.Selection.Find.Text = "||name||"
.Application.Selection.Find.Execute
.Application.Selection = Sheet1.Cells(r, 1).Value
.Application.Selection.EndOf
custN = Sheet1.Cells(r, 1).Value
path = "C:\Users\screa\OneDrive\Desktop\invoices\"
.SaveAs2 Filename:=path & custN, _
FileFormat:=wdFormatXMLDocument, AddtoRecentFiles:=False
End With
r = r + 1
Loop
End Sub

[@BarbHendersonconsulting]

thank you for watching my video. Because I am not able to put square brackets in the description of video, I have replaced the with ||field_name|| in the code in the description.

[@ColoradoOrion]

FYI, these are technically "angle brackets". Square brackets are on the same keys as the curly braces { }.

[@jerryhamilton1150]

Good Example. I noticed where you had a second instance to replace the same text that you put in a second set of code for the next instance. Could you not use a replace all to replace all instances of the same text?

[@SpotlightImpact]

This is cool Barb! Can't wait to try this out!

[@BarbHendersonconsulting]

thank you

[@drallisimo34]

very useful tutorial.thank you!

[@BarbHendersonconsulting]

Glad it was helpful!

[@crafty_tams]

Thanks for sharing, that’s brilliant

[@BarbHendersonconsulting]

Glad you enjoyed it

[@bakesmash882]

Very helpful! Trying to crack the code on loading an image now 🎉 Ty for the video

[@BarbHendersonconsulting]

I am glad you like it!

[@Didjamama]

Hey Barb excellent tutorial I got everything running great based off this and added a few line to add more data point! The problem I can't seem to solve is with taking the number formatted as accounting or currency in Excel but it displays as a generic number in Word. I've played (trial and error) with NumberFormat = but I can't seem to tackle this. Any suggestions on what I should research to resolve?

[@BarbHendersonconsulting]

I did a quick search and came up with "Format(123123, "Currency")" .So I think that you would select the field and then apply that command. Good luck

[@helderlopes6715]

Thanks Barb. Your solution is working just fine! Do you know how to populate fields on headers? It seems to work only if the fields are included in the main body of the Word Template.

[@BarbHendersonconsulting]

I have never tried to populate the header!

[@user-rs9ws5df5c]

Thank you for the video! I have an issue where when I used the code to create the saved documents with the merge fields, the merged information is not in the correct location and the merge field names are still present (the info i wanted shows at top of page and merge field names are still in table). The preview in the template works fine, but the saved documents are incorrect. Any suggestions?

[@BarbHendersonconsulting]

If the template works fine, try again

[@MichaelMinder]

Hi Barb! Great video! Very instructive and broken down and explained very carefully. When trying to run the macro, I seem to be getting a "Compile error: user-defined type not defined" for the the line of code reading "Do while Sheet1.Cells(r, 1) (not equl to)""....What could I be doing wrong. Need your guidance. Thanks.

[@BarbHendersonconsulting]

I am not able to place symbols in the description of the video. the command should read do while sheet1.cells(r,1) ""

[@alexen88]

@@BarbHendersonconsulting Hi Barb, I am still getting "run-time error:'424': Object required" when i add the command. Any help/Tips/clearification?

[@TBoy205]

@@BarbHendersonconsulting Maybe post the code to a site like pastebin instead of in the description?

[@beckyjanefox]

Hi Barb! This is great thanks. Would it also work to take data from Excel and put it into a formatted table in word?

[@BarbHendersonconsulting]

Yes, absolutely, Check out this video kzbin.info/www/bejne/havMiYGpZZhrrsU

[@dfrzrdfrzr]

This has been EXTREMELY helpful! Thank you. I am looking to create if logic based on information within the cell - do you have any examples? For example: If the cell has "blocking issues: 1" write BLOCKED on the word template.

[@BarbHendersonconsulting]

No, sorry. You would have to include an "if" function of some kind

[@user-tx6fl9os2s]

Great video. Just seeing where I find the codes you mentioned

[@BarbHendersonconsulting]

In the description, toward the end

[@bbigboy01]

What options are available, due to data security issues, to not use One Drive? Can I use just a local or private server file location for my Word macro/template?

[@BarbHendersonconsulting]

I think you would have to download both to your personnel computer. Macros do not run on networks.

[@JordanNash-dm1cd]

Hi, I run the code as exact as you have it but get an error message from the off even trying to open the word document from vba code. Do you have any ideas what code be causing the issue?

[@BarbHendersonconsulting]

The only thing I can think of is your references. On the vba window if you go to tools - references. These are the references that I have checked. *Visual Basic for Applications *Microsoft Excel 16.0 Object Library *OLE Automation *Microsoft Office 16.0 Object Library

[@Pandora44bc]

thanks Barb!! saved me from writing 27 letters!

[@BarbHendersonconsulting]

Happy to help!

[@bdgalli]

😍 You're a f*** genius!!! Thanks!

[@BarbHendersonconsulting]

Glad it helped!

[@fatabbott61]

Hi Barb - great tutorial. My Q is I'd like to use the data input on excel in vertical format so headers would be in column A and the input would be in Column B - how would I adjust the code?

[@BarbHendersonconsulting]

That is a big change, I will have to think about that one!

[@ErwinLius]

Hey Barb, thanks for the video, i wonder if we can add pages instead and not save as a new word document over and over?

[@BarbHendersonconsulting]

I have never tried to do that

[@TheAthornation]

With a few small edits to Barb's code (thanks to ChatGPT) I got it working after copying and pasting my template onto as many pages as I needed. You could probably automate the copying of the template into as many pages as you need, but I didn't bother for my short dataset. Sub ReplaceText() Dim wApp As Word.Application Dim wdoc As Word.Document Dim custN, path As String Dim r As Long Dim pageNum As Integer r = 2 pageNum = 1 Set wApp = CreateObject("Word.Application") wApp.Visible = True Set wdoc = wApp.Documents.Open(Filename:="file path of word template", ReadOnly:=True) Do While Sheet1.Cells(r, 1) "" With wdoc .Application.Selection.Find.Text = "" .Application.Selection.Find.Execute .Application.Selection = Sheet1.Cells(r, 1).Value .Application.Selection.EndOf .Application.Selection.Find.Text = "" .Application.Selection.Find.Execute .Application.Selection = Sheet1.Cells(r, 2).Value .Application.Selection.EndOf .Application.Selection.Find.Text = "" .Application.Selection.Find.Execute .Application.Selection = Sheet1.Cells(r, 3).Value .Application.Selection.EndOf .Application.Selection.Find.Text = "" .Application.Selection.Find.Execute .Application.Selection = Sheet1.Cells(r, 4).Value .Application.Selection.EndOf ' Insert a new page .Application.Selection.InsertNewPage ' Increment the page number pageNum = pageNum + 1 End With r = r + 1 Loop ' Save the final modified document custN = "Progress_Report" path = "file path to save document" wdoc.SaveAs2 Filename:=path & custN & ".docx", _ FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False ' Close the Word document and quit the application wdoc.Close wApp.Quit ' Release the object references Set wdoc = Nothing Set wApp = Nothing End Sub

[@owenmccoy3460]

Hey great video. I used your code and it works great but now I want to modify it so that it only applies to visible rows when I have a filter applied. Any suggestions?

[@BarbHendersonconsulting]

Have you tried with a filtered list. I know when you send a table to outlook it only send the filtered values.

[@KristyCrabtreeseattle]

I loved this tutorial! Question - if I wanted to create something that many people could use. Is there a way to get around them having the same the word template on their computers? Or having to adjust the macro to match the word template location?

[@BarbHendersonconsulting]

it probably would be best if you had a copy in numerous locations.

[@therealbenriley]

Thanks for this it was really useful. I used it to auto generate reports but as each line feeds down looking for the next field name how would you get it to check the header for one?

[@BarbHendersonconsulting]

If you have all your fields in one row, it should not got to the next row looking for a field.

[@therealbenriley]

@@BarbHendersonconsulting Hi, don’t think I was very clear. In the word template I have a for document number but it’s in the header and appears to be missed. I have read a few threads and they appear to use headerfooterindex to search and add things to the headers?

[@BarbHendersonconsulting]

@@therealbenriley I have never tried to put the field in a header! Good Luck

[@alexen88]

Hi Barb, I am still getting "run-time error:'424': Object required" when i add the command do while sheet1.cells(r,1) "". Any help/Tips/clearification?

[@BarbHendersonconsulting]

you might not have all the references that you need. On the visual basic window -tools -references; I have Visual basic for Application, Microsoft Excel 16.0 Object Library, OLE Automation and Microsoft Office 16.0 Object library checked

[@brentondowd3224]

Is there a way to have a "Save As" window pop up when it is run, so the user can designate where the word doc is saved? I plan to use this macro for multiple people to use across multiple projects, so saving the document to the same file location would not make sense in my case.

[@BarbHendersonconsulting]

I suppose that you could put in a pop-up window asking where you could save it.

[@centz_9]

Great Video! Can I make this into one document only? I want to create each page on Word docx for each row in Excel.

[@BarbHendersonconsulting]

I believe so

[@nkakonne]

Hi Barb, great video! Thank you so much - where would I find the code?

[@BarbHendersonconsulting]

Scroll down to the bottom of the description

[@user-qb2ns4kj1d]

Hey Barb! Excellent video. Really helpful. But I have a single field repeating atleast 10 times in the template. How to replace all of them in one shot?

[@BarbHendersonconsulting]

you can install this code ActiveDocument.Bookmarks("\StartOfDoc").Select to move to the top of the document and then loop until all the fields are filled.

[@jayatikanakia7950]

This is a great breakdown and really helpful, can you help me understand how can I do this using google sheets excel? Can I use the same script for Google Excel?

[@BarbHendersonconsulting]

Unfortunately the code for Excel is written in VBA and the code for Google sheets is Java Script, so it will not work.

[@Amrorpooo]

Great video barb, very helpful as a beginner. I do have a question though, how do i go about saving the file without keeping the file open? I just want to have the script do all the text replacement without needing to open them all, because i have like 40 rows, so that is 40 files remained open on my computer that i have to close manually. Or if it does "need" to open them all, at least close them once done, any idea?

[@BarbHendersonconsulting]

I think it would be best to keep it open

[@NotSoLittleBro]

Great video! I have been looking for a video like this for days. Does this work with multiple worksheets within a single workbook?

[@BarbHendersonconsulting]

you would have to include the worksheet name as well as the cell reference

[@NotSoLittleBro]

could I instead name each cell value I need and call on those instead? for instance in excel I change the name of cell r-1 to "Name". Then in the code could I instead replace r-1 with "Name"@@BarbHendersonconsulting

[@user-ws5ld3ke6r]

Dear Barb, Thank you very much for your tutorial! It is exactly what I was searching for. But If I have 500 rows in my excel file and today I need to populate templates, for example, from 23rd to 54th row, tomorrow I will have to populate another row range. My question is - what should be inserted into the code to proceed only part of the rows?

[@BarbHendersonconsulting]

the easiest way you could do that is to copy the desired range to another sheet and run it off that sheet.

[@user-ws5ld3ke6r]

Dear Barb, thank you for the idea

[@nithish9041]

for me , the first time values correctly updated but if the same field repeats it does't update the value how can i resolve this issue

[@BarbHendersonconsulting]

F8 through the code and make sure that the "r" value is actually increasing.

[@TateThacker-hf4mz]

Hi there! For some reason, the Word documents the macro creates will only save to a mysterious 'template' folder I can't seem to find on my computer, even though I put in the path to save to a different folder. Any suggestions?

[@BarbHendersonconsulting]

It really tried to put it in the templates folder, you might have to change it a couple of times

[@carynwilson6732]

I was getting the "5152" error because I was trying to create filenames from the value of a cell that contained characters that aren't allowed. Like colons. I replaced these with hyphens and it seemed to work.

[@BarbHendersonconsulting]

good to hear!

[@ElectricSheep2199]

Hey Barb! Great Video and very educational. I am having a 5152 Error popping and when I debug it - it points out .SaveAs2 Filename:=path & custN, _ FileFormat:=wdFormatXMLDocument, AddtoRecentFiles:=False The bug says that it's an invalid location but not sure what's wrong with the code as it's the same as yours. Please help and thanks!

[@BarbHendersonconsulting]

The code would work if you would like to save it to my computer! To determine the path go to the file folder where the file is and go to the menu bar and select copy path. Then you can replace my path with your path.

[@ElectricSheep2199]

@@BarbHendersonconsulting I did put in my own pathway - This is what I have: custN = Sheet1.Cells(r, 1).Value path = "C:\Users\jared.bagnall\Desktop\ALPHA\" .SaveAs2 Filename:=path & custN, _ FileFormat:=wdFormatXMLDocument, AddtoRecentFiles:=False

[@ElectricSheep2199]

​@@BarbHendersonconsulting Is it this that needs to change: .SaveAs2 Filename:=path & custN ?

[@juanvalentemeridarodriguez7147]

Having difficulty doing it on a office 365 for mac.

[@BarbHendersonconsulting]

I have never tried it on either 365 or on a Mac, sorry!

[@user-ds5pc6he4c]

Hi! This is only pulling my first row of data (row 2) and not the entire sheet. Do you have advice on how to correct this?

[@BarbHendersonconsulting]

Ensure that you have theses lines at the bottom r = r + 1 Loop if you have those f8 through your code and make sure that the r is increasing in the code.

[@user-ds5pc6he4c]

@@BarbHendersonconsulting Thank you for the reply! I do have these lines at the bottom and my r is increasing in the code. It properly pulls the data from the first row and properly transfers it to the Word template, but then stops and throws the following error, Run-time error '9105': Application-defined or object-defined error.

[@BarbHendersonconsulting]

What you could do is name your fields in Excel. For example Name=sheet1.cells(r,3).value and then just before the r=r+1 you could refresh the values Name="".

[@daltonosmanski6444]

Hey Barb, how do you get number formatting to carry over? Excel Shows 1,000,000 however in Word it displays as 1000000. Thank you in advance!

[@BarbHendersonconsulting]

That is a good question, let me work at it and get back to you

[@mcknites1981]

Hi Barb, your video is a lifesaver, it is brilliant! However, i am having an error with this part ".SaveAs2 Filename:=path & custN, _ FileFormat:=wdFormatXMLDocument, AddtoRecentFiles:=False", i am using Microsoft Office Professional LTC 2021...I cant seem to save, name the file due to fileformat...

[@BarbHendersonconsulting]

I don't know what the solution would be, I am not familiar with Microsoft Office Professional LTC 2021

[@user-hk1ib7pp1e]

Thank you, can we make all pages in one word file?

[@BarbHendersonconsulting]

Watch this video for longer documents kzbin.info/www/bejne/pWG9qpiDmdN6b80

[@user-hk1ib7pp1e]

@@BarbHendersonconsulting thank you but this is not what I want to do. I have a word template with one page and data in Excel. I want to do something like a mail merge and extract all the data to that word template

[@carloscalleperez]

if i want to copy a excel chart in a especific position, how i can do that? because a chart its not a text and i dont know how to do it

[@BarbHendersonconsulting]

I have never explored that

[@carloscalleperez]

@@BarbHendersonconsulting today I finish the macro, its easy i use a word document with some especific words and the macro look for them and paste the text or charts from excel

[@ElectricSheep2199]

Is it possible to save them as .PDF? If, so what do you reccomend as far as code?

[@BarbHendersonconsulting]

My expertise is Excel not Word, this is my best guess ActiveDocument.ExportAsFixedFormat OutputFileName:= _ path & custn.pdf ExportFormat:=wdExportFormatPDF

[@ElectricSheep2199]

@@BarbHendersonconsulting You may not have the expertise but you rock nevertheless. That code worked :)

[@BarbHendersonconsulting]

@@ElectricSheep2199 I am happy it worked for you

[@nevizerkle5774]

Hi Barb! When i click run, there does not appear to be any files created. I checked the file path and it's correct. I cannot figure out why it's not generating an error or docs

[@BarbHendersonconsulting]

My advice is to "F8" through the code to see where it is getting hung up

[@nevizerkle5774]

​@BarbHendersonconsulting thanks Barb! I adjusted the code so it reads (r, "A") and is now pulling all the correct data and generating the docs, however now I can't figure out why it is populating the top of my doc with the info instead of the square bracketed text as indicated in the code... 🤦‍♂️

[@user-uv4dw8qb7e]

I am getting Compile Error: User-defined type not defined on second line of code wApp As Word.Application

[@BarbHendersonconsulting]

On the visual basic window -tools -references; I have Visual basic for Application, Microsoft Excel 16.0 Object Library, OLE Automation and Microsoft Office 16.0 Object library checked

[@kevinlong5864]

@@BarbHendersonconsulting I'm getting the same error and I have all the correct references checked. Any other ideas for fixing that error?

[@shipcore6757]

@@kevinlong5864 You need You need Microsoft Word 16.0 Object Library too.

[@weixin9270]

@@BarbHendersonconsulting I have all 4 checked but still have the same messge. How come?

[@TateThacker-hf4mz]

I got this to work, then adjusted it to do a different sheet. On that sheet, it will only do a few rows (it changes how many each time) then it fails. It says the issue is on .Application.Selection.Find.Text = "" and gives me "Run-time error '462': The remote server machine does not exist or is unavailable." What would you suggest I try?

[@BarbHendersonconsulting]

take a look at this one, it is for a longer list of data to word kzbin.info/www/bejne/pWG9qpiDmdN6b80

[@TateThacker-hf4mz]

Thank you!! Will do@@BarbHendersonconsulting

[@sanjayt222]

How do I retain formatting of the template?

[@BarbHendersonconsulting]

the line Set wdoc = wApp.Documents.Open(Filename:="C:\Users\screa\OneDrive econstructionBH\Documents\Excel\welcome.dotx", ReadOnly:=True) The read only prevents the word template from being changed

[@SouZiTros]

Impeccable work. Really helped. Is there a way for the code to also terminate every winword.exe that stays open in the backround?

[@BarbHendersonconsulting]

I would be worried about closing it because it might close the process

[@jansiegel5575]

Thank you Barb, great video! 1) I am experiencing an issue that, it write the data from sheet directly in one line like "Donald Duck11112Vancuver..." Do you have idea why? 2) would it be possible to first find every occurrence of in dotx, replace, and just after that move to another cell (f.e. )? How would you write it? Thank you

[@BarbHendersonconsulting]

1) I think that it has to do with the formatting on your word document. it replaces the field exactly were it is found. 2)The way I do it does not allow the software to overwite by accident.

[@SrirakshaArjun]

even i am facing the same issue. Instead of finding the and in the word document, it just displays continuously in the beginning "Donald Duck11112Vancuver" in one line. Let me know if you have any solution.

[@indreleonaviciene]

Hi Barb, it is extremely helpful. Thank you so much for this. If Word document needs to be named "Mickey Mouse some place" (name+address), how should this be changed correctly? custN = Sheet1.Cells(r, 1).Value Appreciate your help.

[@BarbHendersonconsulting]

That should do it!

[@indreleonaviciene]

@BarbHendersonconsulting "custN = Sheet1.Cells(r, 1).Value" creates document name of only one value (column 1). If document name must consist of column 1 and column 2 of each row, how this should be done?

[@SparxNet]

Wouldn't it be easier to just use the Mail Merge functionality to link particular cells in the Excel sheet to specified fields in the Word document? No need for any VBA code or macros. You can then print out all or specific records either to new individual DOC files or directly to a printer.

[@BarbHendersonconsulting]

another option

[@finhas8865]

I've tried mail merge method. It's good for something you want to print immediately, as is. But if you want to create a document with some additional pages, or if you need to set some conditions, this method is much better and more flexible.

[@amandarose9188]

so it's a mail merge essentially?

[@BarbHendersonconsulting]

like mail merge but with VBA

[@kamranvohra]

Hi Barb I am getting a compile error User-defined type not defined

[@BarbHendersonconsulting]

two reasons might be: 1.I am not able to place symbols in the description of the video. the command should read do while sheet1.cells(r,1) "" 2.On the visual basic window -tools -references; I have Visual basic for Application, Microsoft Excel 16.0 Object Library, OLE Automation and Microsoft Office 16.0 Object library checked

[@shipcore6757]

You need Microsoft Word 16.0 Object Library too.

[@andjelak2504]

​@@shipcore6757and then what to use as a object? Because its required now

[@BrandonBChambers]

What the heck happened at 3:37?

[@BarbHendersonconsulting]

Nothing! before explaining the code I needed to show you what references are required for the code to run!