Pinterest Stumbleupon Whatsapp
Advertisement

Updated by Brad Jones on 25 August 2017.

During your work week, there are probably lots of times that you find yourself copying and pasting information from 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 6 New Excel Charts and How to Use Them 6 New Excel Charts and How to Use Them Need to get more out of your Excel charts? Here's a one-stop guide to some of the new charts introduced in the latest version of Excel. Read More , to setting up a project management tracker 10+ Useful Excel Templates for Project Management & Tracking 10+ Useful Excel Templates for Project Management & Tracking Templates are to project managers what knives are to professional chefs: indispensable. We show you how you can replicate successful projects by using ready-made templates in Microsoft Excel and beyond. Read More — 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 Excel data into 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.

So long as you know where to find the data in the spreadsheet, you’ll be able to reach in and grab it using VBA How to Send Emails From an Excel Spreadsheet Using VBA Scripts How to Send Emails From an Excel Spreadsheet Using VBA Scripts We'll show you how to set up automated emails from within Excel using Collaboration Data Objects, (CDO) and VBA scripts. Our code templates make this a lot easier than it sounds! Read More .

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

How to Integrate Excel Data Into a Word Document expense spreadsheet

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.

How to Integrate Excel Data Into a Word Document legacy tools

Use this menu to insert a Label.

How to Integrate Excel Data Into a Word Document insert 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.

How to Integrate Excel Data Into a Word Document label properties

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.

Working With VBA

To get started connecting Word to Excel, you’ll need to make sure you can reference the latter in VBA The Excel VBA Programming Tutorial for Beginners The Excel VBA Programming Tutorial for Beginners VBA is a Microsoft Office power tool. You can use it to automate tasks with macros, set triggers, and lots more. We'll introduce you to Excel visual basic programming with a simple project. Read More .

Prepare the Excel Data Import

Click on Tools, and then References. Scroll down the list until you see the Microsoft Excel 16.0 Object Library and select it.

How to Integrate Excel Data Into a Word Document excel object library

Once you’ve done this, the rest is just a matter of writing a ridiculously simple VBA script to pull in data from an Excel spreadsheet, and automatically update the label caption with the data.

Here’s the code:

Private Sub CommandButton1_Click()
Dim objExcel As New Excel.Application
Dim exWb As Excel.Workbook

Set exWb = objExcel.Workbooks.Open("C:\Users\Brad\Desktop\expenses.xlsx")

ThisDocument.total_expenses.Caption = exWb.Sheets("Sheet1").Cells(12, 2)

exWb.Close

Set exWb = Nothing

End Sub

See how that works? The “exWb” Excel application object opens the 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.

How to Integrate Excel Data Into a Word Document word macro enabled document

Here’s the VBA macro in action.

How to Integrate Excel Data Into a Word Document yearly expenses

Integrate Excel Labels

The hard part with dealing with labels in Word is that it’s sometimes hard to align it Use the Ctrl Key to Master Paragraph Formatting in Word Use the Ctrl Key to Master Paragraph Formatting in Word Formatting your paragraphs in Word using your mouse is fine but painstakingly slow. Using the Ctrl key, you can speed up the process and boost your productivity. Read More 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.

How to Integrate Excel Data Into a Word Document caption

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 Excel file to the end of that text.

Here’s what that kind of code would look like.

Dim objExcel As New Excel.Application
Dim exWb As Excel.Workbook
Set exWb = objExcel.Workbooks.Open("c:\Users\Brad\Desktop\expenses.xlsa")

ThisDocument.total_expenses.Caption = exWb.Sheets("Sheet1").Cells(12, 2)
ThisDocument.total_hotels.Caption = "Hotels: " & exWb.Sheets("Sheet1").Cells(5, 2)
ThisDocument.total_dining.Caption = "Dining Out: " & exWb.Sheets("Sheet1").Cells(2, 2)
ThisDocument.total_tolls.Caption = "Tolls: " & exWb.Sheets("Sheet1").Cells(3, 2)
ThisDocument.total_fuel.Caption = "Fuel: " & exWb.Sheets("Sheet1").Cells(10, 2)

exWb.Close

Set exWb = Nothing

You can use the string concatenation “&” symbol to place connect the static text with the data extracted from the Excel sheet. Here’s what the final results look like in the updated Word document:

How to Integrate Excel Data Into a Word Document finsihed report

Taking Things Further

If you want to test your abilities, why not automate your report 5 Resources for Excel Macros to Automate Your Spreadsheets 5 Resources for Excel Macros to Automate Your Spreadsheets Searching for Excel macros? Here are five sites that have got what you're looking for. Read More even further? You can remove that ugly gray command button from your Word document, simply by having the data-update script run on Document.Open() — the whole process will take place behind the scenes.

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 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.

Image Credit: Punyaphat Larpsomboon via Shutterstock.com

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. Julie Hall
    September 25, 2017 at 3:11 am

    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

  2. Jim
    June 27, 2017 at 4:13 pm

    Hi there,

    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 ?

    Thanks again !

    JM

  3. Andre
    August 20, 2016 at 10:28 pm

    I have many fields of the same label name, how do I make them to fill all at once? I tried naming all the labels with the same name, but Word doesn't recognize that as a valid method. What can I do?

  4. Ashton
    August 3, 2016 at 8:02 pm

    Ryan,

    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".

  5. Abdoulaye DIOP
    July 20, 2016 at 6:40 pm

    Hi guys,

    Why not to simply use the Word mailing tool? That's what I use and it takes me a feew seconds to few minutes to update my documents depending of the quantity of data managed.

    Kind regards

    • RB
      July 21, 2016 at 1:44 pm

      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.)

    • RB
      July 21, 2016 at 1:45 pm

      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.)

  6. Patrick
    May 16, 2016 at 10:11 pm

    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.

  7. Hamza Zubair
    May 10, 2016 at 7:45 am

    Exactly how do you "have the data-update script run on Document.Open()"

  8. Lucas
    January 10, 2016 at 7:32 pm

    I'm trying to use to copy some cells filled with texts, but it doesn't work for text, just for numbers.

    How to set text variables?

    • mp
      March 10, 2017 at 10:55 am

      Just copy the cells. Choose "Paste special". Check "Paste link" and choose if you want it pasted as an object, picture, text or something other.

  9. Anonymous
    September 23, 2015 at 6:08 pm

    Thanks Ryan, the tutorial is awesome! Also thanks to IS0 for the link to http://www.gendo.me , I've shared it with a friend and he said he cut the time of making regional reports in half!

  10. Anonymous
    July 2, 2015 at 2:39 pm

    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

  11. Juli
    May 12, 2015 at 8:31 pm

    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!

  12. Steve Sullivan
    March 25, 2015 at 2:02 pm

    Sorry, I meant Ryan!

  13. Steve Sullivan
    March 25, 2015 at 1:17 pm

    Thanks Brian. Is the any way to make add a conditional (IF) statement to suppress lines that have a .00 total?

    Steve

    • Tyler Vanderby
      August 27, 2017 at 7:04 pm

      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.

    • Tyler Vanderby
      August 27, 2017 at 7:07 pm

      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?

  14. zem
    March 18, 2015 at 12:04 pm

    ("C:UsersZESDesktopCOSTINGTRUNIONGANTRY2.xlsx") I am getting an error for this path.Help.

  15. Alvin B.
    March 2, 2015 at 4:16 pm

    This isn't "surprisingly easy", it's damn complicated.

    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.

    • IS0
      March 24, 2015 at 3:17 pm

      There is a simpler way, use GenDo http://www.gendo.me

      Just insert Excel column names where you need them to be in a Word document, press a button and get a few thousand files you need!

      • Anonymous
        September 23, 2015 at 6:08 pm

        Thanks!

  16. SFAN
    February 10, 2015 at 7:48 pm

    hello Ryan,

    Good solution, I have linked excel to word using your solution, done in office 2013. But i am experiencing problem sending solution to my client with office 2010 and MAC.

    Can you please help in doing the some with office 2010 and MAC 2011.
    Best Regards,
    SFAN

  17. Mike
    January 9, 2015 at 10:32 am

    Hi Ryan,

    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.

  18. Jeremiah
    June 28, 2012 at 5:32 pm

    Awesome tip. Thanks Ryan. One question though. Can the user specify which excel document they want the data imported from?

    • Ryan Dube
      July 1, 2012 at 4:25 am

      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"?