How to Seamlessly Integrate Excel Data into Word Documents Using VBA

Updated by Brad Jones on August 25, 2017.

In my years working with Microsoft Office, I've seen countless professionals manually copy-pasting data from Excel spreadsheets into Word reports. It's time-consuming and error-prone. Excel shines for data crunching and charts—explore our guides on 6 New Excel Charts and How to Use Them and essential Microsoft Excel Templates.

Here, I'll share proven VBA techniques I've used to automate connections between Excel data sources and dynamic Word documents, ensuring reports update effortlessly.

Prepare Your Excel Spreadsheet

This example uses a simple yearly expense tracker, but the method scales to complex workbooks with multiple sheets. VBA grabs data by sheet and cell reference, regardless of size. For more automation ideas, see How to Send Emails from Excel Using VBA.

Set Up Your Word Document

Imagine your manager needs a polished report grouping expenses visually. In Word, enable the Developer tab, enter Design Mode under Controls, and use Legacy Tools to insert labels and buttons.

Insert a Label, position it, right-click for Properties, and name it (e.g., total_expenses).

Add a Command Button from Legacy Tools and double-click to launch the VBA editor. Later, shift to the Document_Open() event for seamless automation.

Mastering VBA for Excel-Word Integration

As an Excel VBA expert, I recommend starting with our Excel VBA Programming Tutorial for Beginners. First, reference Excel in Word's VBA.

Add Excel Reference

In the VBA editor, go to Tools > References and check Microsoft Excel 16.0 Object Library.

Now, write this simple script to pull data:

Private Sub CommandButton1_Click()
Dim objExcel As New Excel.Application
Dim exWb As Excel.Workbook
Set exWb = objExcel.Workbooks.Open("C:\Users\Brad\Desktop\expend.xlsx")
ThisDocument.total_expenses.Caption = exWb.Sheets("Sheet1").Cells(12, 2).Value
exWb.Close
Set exWb = Nothing
objExcel.Quit
Set objExcel = Nothing
End Sub

It opens the file, extracts the cell value, updates the label, and cleans up. Adjust the path and name as needed.

Test Your Macro

Save as a Word Macro-Enabled Document.

Enhance with Dynamic Labels

Labels can misalign; embed static text in VBA using concatenation (&). Edit the label caption directly:

Dim objExcel As New Excel.Application
Dim exWb As Excel.Workbook
Set exWb = objExcel.Workbooks.Open("C:\Users\Brad\Desktop\expend.xlsx")
ThisDocument.total_expenses.Caption = "Total Expenses: " & exWb.Sheets("Sheet1").Cells(12, 2).Value
ThisDocument.total_hotels.Caption = "Hotels: " & exWb.Sheets("Sheet1").Cells(5, 2).Value
ThisDocument.total_dining.Caption = "Dining Out: " & exWb.Sheets("Sheet1").Cells(2, 2).Value
ThisDocument.total_tolls.Caption = "Tolls: " & exWb.Sheets("Sheet1").Cells(3, 2).Value
ThisDocument.total_fuel.Caption = "Fuel: " & exWb.Sheets("Sheet1").Cells(10, 2).Value
exWb.Close
Set exWb = Nothing
objExcel.Quit
Set objExcel = Nothing

Advanced Automation Tips

Level up with resources like 5 Resources for Excel Macros to Automate Your Spreadsheets. Hide the button and trigger on Document_Open()—reports update automatically on launch. Create once, print forever.

What's your next VBA project? Share in the comments!