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

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.

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.

Working With VBA

To get started connecting Word to Excel, you'll need to make sure you can reference the latter in VBA.

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.

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

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:

Taking Things Further

If you want to test your abilities, why not automate your report even further? You can remove that ugly gray command button from your Microsoft 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 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.

Image Credit: Punyaphat Larpsomboon via Shutterstock.com