During your work week, there are probably lots of times that you find yourself copying and pasting information from Microsoft Excel into Word, or the other way around.

This is how people often produce written reports based on data that’s accumulated and updated in a spreadsheet. Excel is a great program for everything from creating professional looking charts, to setting up a project management tracker — but when it comes to writing up a report, Word is a much better tool.

In this article, we’re going to dive into background VBA scripting that allows you to actually program connections between data that are stored in an Excel file and Word documents where you’re producing reports.

It’s surprisingly easy to integrate Microsoft Excel data into Microsoft Word once you know how to add the right references, and how to lay out the syntax of the background VBA code.

Setting Up the Spreadsheet

In this example, I’m going to start out with a fairly simple Excel spreadsheet. In practice, the Excel file can consist of multiple spreadsheets with lots of data — it doesn’t matter.

Here’s what my sample spreadsheet looks like. It’s a list of expense totals that have been calculated throughout the entire year.

Setting Up the Word Document

Let’s say you have a manager that would like to see a nicely formatted report that describes the expenses, grouping together like items and presenting the information in a layout that’s a little more aesthetically pleasing.

You can do this by incorporating objects like text boxes and labels into your Word document. When you’re in Word, just click on the Developer menu tab, and then select “Design Mode” in the Controls section. Use the Legacy Tools drop-down icon to insert various different elements into your document.

Use this menu to insert a Label.

Once you have the label placed in the document where you want it (not always an easy task), you’re ready to program the data feed. But first, you’ll need to name the label so that the VBA can identify it. Right click on the label and go into Properties. Find the (Name) field and call it something that you’ll remember.

Now, add a Command Button from the same Legacy Tools drop-down list, and double click it to open up the VBA editor. When you get your code working later, you can modify it so that the code runs on the Document Open() event. You’ll see that in the object drop-down boxes in the editor window.

See how that works? The “exWb” Excel application object opens the Microsoft Excel file at the path you provide it, and it’ll go right into the specific sheet and cell number, extract the data, and place it into the Caption property of the label that I named total_expenses. All you need to edit in your script is the file path and the label name.

Test Your Macro

To test out your command button, save your document, remembering to specify that you want a Word Macro-Enabled Document so that your code works.

Here’s the VBA macro in action.

Integrate Excel Labels

The hard part with dealing with labels in Word is that it’s sometimes hard to align it at the end of a sentence or alongside any other text.

One way of overcoming that is actually incorporating some of the text alongside the data in the VBA code itself. As you can see here, I’ve put the static text right into the Caption when I create the label itself.

Now, all you have to do is include that text when you update the label with your VBA script, and just append the data from the Microsoft Excel file to the end of that text.

This means that in many cases, you could create the initial document once, and then never have to create it again. All you’ll have to do is open it, and all of the labels will automatically update for you with the data from the updated Microsoft Excel file. Just click Print, and submit the report to your manager. A 30-minute job just turned into a one-minute printout!

Can you think of any other cool uses for this data-integration technique using VBA? Share some of your own ideas and thoughts in the comments section below.

I've got this Copile Error:
"User-Defined Type not defined" for the below line:
-Dim objExcel As New Excel.Application
I've also tried this one:
- Dim objExcel As Excel.Application
- Dim exWb As Workbook
- Set objExcel = New Excel.Application
But Still the same Error!

Have one question, Can I use this code to input Excel info into a Text Box or does this only work with the Label(s)? I have predefined text boxes with formatting already setup and want to use that if possible. thank you

Great tips, but if you are looking for a way of updating Excel-based content into Word documents you might want to consider using an Add-in. Our Excel-to-Word Content Automation Add-in works for text, tables and charts. You can learn more at analysisplace.com/Solutions/Document-Automation

First of all massive thanks! I've been spending an afternoon on this and finaly succedeed setup my new report :)
Still, I've 2 questions :
1°How can I keep the format of my cell ? (some are %)
2°How can I then, copy the report and paste it on Internet ?

Is there a way to have VBA recognize different types of column labels that mean the same thing? For example, my original spreadsheet, a quote, can have columns labeled "description" or "desc" depending on who creates the quote. I want the invoice to be populated even if the column header says "description" or "desc".

That worked great for me--thanks for the suggestion! In Word 2013, it was the Mailings -- Start Mail Merge menu option, and I used the Wizard and for "Select recipients" I picked "Use an existing list" and then found my Excel .xlsx file. (My use case was pretty simple, with just columns of data with headings on a single worksheet.)

That worked great for me--thanks for the suggestion! In Word 2013, it was the Mailings -- Start Mail Merge menu option, and I used the Wizard and for "Select recipients" I picked "Use an existing list" and then found my Excel .xlsx file. (My use case was pretty simple, so my spreadsheet just had columns of data with headings on a single worksheet.)

Now I want to be able to select which data to populate, like i have a row of data that i want to populate into the document, but the method listed above requires a code to be written based off an excel sheet, whereas I want to select, for instance, a year, and all the data I have for that year will populate the document. Basically I want to be able to select a list of names from a drop down menu say under year, and the document will populate according to the data i have on that specific year.

Hello Ryan!
Is it possible to direct the script to a file/row defined by me with a cell value?
I'm right now working in the script and it works perfect for the cell specified in the script.
Let me explain myself, I have a report where I have to add birthdate, an unique ID, gender and due date. This is all in a excel sheet that is continuously updated, all the info I need to transfer to my report is in 1 row that starts with an specific number (test number). So what I want is to open the report write number of test an get the rest by clicking from the excel sheet.
Is that possible?
Thanks
KP

Holy wow. My boss just walked in and did this EXACT THING:
"Now, lets say you have a manager that would like to see a nicely formatted report that describes the expenses, grouping together like items and laying it all out in a layout that’s a little more pleasing to the eye (and easier for the big boys upstairs to understand)."

I was able to google it in like 3 seconds, find this article, and send it to him. Thanks for making me look awesome and predicting precisely what we need!

I am wondering the same thing. I'm trying to use Excel as a database and extrapolate data from items quantity greater than 0 and put it into a word template for work! Trying to gather information and ideas.

I am trying to do the same thing for work. I want to use excel as a database and extrapolate data from items quantity greater than 0 and insert that data into a word template! Any code for the IF statement?

Why in 2015 have they not made this any simpler? I have to print a few thousand forms with data pulled from multiple cells in Excel... It's nearly IMPOSSIBLE. I am a teacher and am NOT paid to spend all day manipulating data.

Is it possible to extract one line at a time to a Word Document? What I have is an activity tracking spreadsheet, which is updated whenever (after a task). There are some mandatory data validation cells to fill in, but once everything is filled in I would like a user to press a button (say at the end of the line) and it pushes the row of information to a Word Document where the user can continue to fill in a summary of what they have been doing.

Well, the Workbook is opened with the "Open" command, and the sheet is selected using the exWb.Sheets("Sheet1") function, changing Sheet1 to whatever your sheet is called. Is this what you meant by "Excel document"?

Ryan has a BSc degree in Electrical Engineering. He's worked 13 years in automation engineering, 5 years in IT, and now is an Apps Engineer. A former Managing Editor of MakeUseOf, he's spoken at national conferences on Data Visualization and has been featured on national TV and radio.