Pinterest Stumbleupon Whatsapp
Ads by Google

integrate excel into wordDuring 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 an Excel spreadsheet. We’ve offered a lot of really cool tips and tools for Excel over the years, such as Saikat’s article on producing professional looking charts How To Create Attractive Professional Looking Charts Using The Chart Tools Of MS Word 2010 How To Create Attractive Professional Looking Charts Using The Chart Tools Of MS Word 2010 Charts, as an illustrated way of showing boring facts and figures, has always helped to embellish professional Word documents. Charts help readers compare data and understand trends with a glance. But how do you create... Read More , and Steve’s article on cool Excel templates for project management 15 Useful Excel Templates for Project Management & Tracking 15 Useful Excel Templates for Project Management & Tracking Read More .

In this article, I’m going to dive a little more into the background VBA scripting that allows you to actually program connections between data that might be stored in an Excel file and Word documents where you may be producing reports.

It’s actually 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.

Integrating Data From Excel Into Your Word Document

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

So long as you know where the data resides in the spreadsheet, you’ll be able to reach in and grab it using VBA.

Ads by Google

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

integrate excel into word

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

You can do this by incorporating objects like textboxes or 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 box. Use the Legacy Tools dropdown icon to find where you can insert labels into your document.

move excel data to word

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 dropdown list, place it in your document, 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 dropdown boxes in the editor window.

To get started connecting Word to Excel, you’ll need to reference Excel. Click on Tools, and then References. Scroll down the list until you see the “Microsoft Excel 12.0 Object Library” and select it.

move excel data to word

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 how that works.

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

Set exWb = objExcel.Workbooks.Open("c:\temp\expenses.xls")

ThisDocument.yrTotal.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 you named “yrTotal“.

Here’s the VBA macro in action.

move excel data to word

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.

excel data to word

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:\temp\expenses.xls")

ThisDocument.yrTotal.Caption = exWb.Sheets("Sheet1").Cells(12, 2)
ThisDocument.totHotels.Caption = "Hotels: " & exWb.Sheets("Sheet1").Cells(5, 2)
ThisDocument.TotDining.Caption = "Dining Out: " & exWb.Sheets("Sheet1").Cells(2, 2)
ThisDocument.totTolls.Caption = "Tolls: " & exWb.Sheets("Sheet1").Cells(3, 2)
ThisDocument.totFuel.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.

integrate excel into word

Again, if you don’t want to have a big, ugly grey command button in your word document, just have the data-update script run on Document.Open(), and it’ll all take place behind the scenes.

In fact, in many cases you could create the initial document once, and then you’ll 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. All you have to do is click to Print, and submit the report to your manager. A 30 minute report just turned into a 1 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.

Shutterstock

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

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

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

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

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

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

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

  7. John Summers
    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!

  8. Katherine Parra
    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

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

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

    Sorry, I meant Ryan!

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

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

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

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

      • John Summers
        September 23, 2015 at 6:08 pm

        Thanks!

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

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

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

Leave a Reply

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