www.makeuseof.com Open in urlscan Pro
54.157.137.27 Public Scan

Back to summary

URL:
https://www.makeuseof.com/tag/integrate-excel-data-word-document/
Submission: On November 18 via api (November 18th 2021, 3:53:00 pm UTC) from US — Scanned from DE

Form analysis
1 forms found in the DOM

POST /search/

<form method="post" class="search-form js-searchBox icon i-search" id="searchform2" action="/search/">
  <input id="js-search-input" type="text" name="keyword" class="input-text search-input" autocomplete="off" placeholder="Search MUO" autofocus="">
</form>

Text Content

WWW.MAKEUSEOF.COM

* PC & Mobile
* Windows
* Mac
* Linux
* Android
* iPhone & iPad
* Internet
* Security
* Programming
* Lifestyle
* Entertainment
* Productivity
* Creative
* Gaming
* Social Media
* Hardware
* Technology Explained
* Buyer's Guides
* Smart Home
* DIY
* Product Reviews
* Free Stuff
* Free eBooks
* Giveaways
* Top Lists
* Free Cheat Sheets
* Deals
* Awards
* About
* About MUO
* Advertise
* Contact
* Privacy
* Shop
* Write For Us
* Newsletter
* Podcasts

FOLLOW MUO

*
*
*
*
*
*
*

* Home
* Productivity

HOW TO INTEGRATE EXCEL DATA INTO A WORD DOCUMENT

By Ryan Dube Published Aug 25, 2017
Share Share Tweet Email

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 an Excel spreadsheet. 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 in Excel and Word.

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.

MAKEUSEOF VIDEO OF THE DAY
Powered By

10 Sec

Amazon Selling Its Own PCR COVID-19 Tests #shorts

Next
Stay

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

How to Update Your Raspberry Pi to the Latest Raspbian OS

Want to keep your Raspberry Pi up-to-date? Here's how to upgrade Raspbian using
a standard update or by upgrading to a new version.

Read Next
Share Share Tweet Email
Related Topics
* Productivity
* Digital Document
* Visual Basic Programming
* Microsoft Word
* Microsoft Excel

About The Author
Ryan Dube (938 Articles Published)

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.

www.makeuseof.com Open in urlscan Pro 54.157.137.27 Public Scan

Form analysis 1 forms found in the DOM

POST /search/

Text Content

www.makeuseof.com Open in urlscan Pro
54.157.137.27 Public Scan

Form analysis
1 forms found in the DOM