Excel to Word Document Automation

This is the next post in the add-ins series, where we are exploring various add-ins that enhance and extend the capabilities of Excel. In this post, we’ll take a look at the Excel-to-Word Document Automation add-in. In summary, it enables us to get Excel data into a Word (or Power Point) document. It creates a link between the documents making it easy to update the Word document for any Excel changes. It can bring in tables, graphics, and values. Once the link is established, the data will continue to flow even if you change sheet names, file names, or file locations.

Objective

Before we get too far, let’s get a general sense of what the add-in does.

It allows you to transfer values, images, and tables from Excel into Word (or Power Point). But … wait up Jeff … I can already do that with a standard Copy/Paste! Yes, and for one-time projects, a copy/paste is all you need.

However, if this is something you do on a recurring-basis, every day, week, or month, this add-in can be very helpful.

You basically identify the specific items in Excel that you want to transfer. Then, in your Word document, you retrieve the selected elements. Once this mapping has been established, you can click a button to have any Excel changes sent to your Word document.

While Excel has had this basic capability built-in for years or decades, this add-in addresses some of the traditional limitations. For example, this add-in will continue to work even if you change sheet names, file names, or file locations.

Plus, the add-in will continue to work if you insert cells, rows, or columns because the add-in relies on defined names … brilliant! Let’s get into the details.

App Store

The Excel-to-Word Document Automation add-in is available in the app store. To get to the app store, just click Insert > Get Add-ins. Install the Excel-to-Word Document Automation add-in and you’ll be ready to go.

Once installed, you’ll see a new Automate Content button on the Home tab.

Note: depending on your version of Excel, you may or may not have access to these add-ins.

Walkthrough

Let’s walk through the entire process. We’ll start with setting up the Excel file.

Excel

We have some stuff in Excel that we’d like to get into a Word document. For example, we want to get the following vendor name, table, and graph from Excel to Word:

Within the Add-in, we define the prefix that we will use to identify any Excel elements we wish to transfer. For example, I set my prefix to tfr_ as shown:

The next step is to use this prefix when naming the various elements you wish to make available to Word.

There are three elements I want to transfer: the Vendor name, the Table, and the Graph.

• I gave the Vendor name cell the following name: tfr_name
• I gave the Table the following name: tfr_table
• And I named the Graph: tfr_chart

Once the individual elements are named, you click the Submit Content button:

You’ll then receive a notification indicating how may elements were uploaded:

Our Excel work is complete! We can now bring these elements into our Word document.

Word

In Word, we need to install the same Add-in. Just head to the app store and install it just as we did in Excel.

We begin by clicking the Get Excel Content button.

We can then select which elements to retrieve from the drop-downs and click the Insert Content/Update Link button:

And the Excel content appears in our Word document, and we can apply standard Word formatting as desired:

We can insert the table as well:

Click Insert Content and bam …

And finally our graph:

And bam…

And now for the moment of truth … the updates.

Updates

Let’s say we update our Excel file next period, like this:

We simply click the Submit Content button in the Excel add-in. The upload notification confirms the elements were successfully uploaded.

We head back to our Word document and click Update Document:

And bam, the updated company name appears, the new row in the table appears, and the next month in the chart appears …

Conclusion

This add-in will be VERY helpful if you create values, tables, or charts in Excel and need to export them to Word/Power Point on a recurring basis. If you have any other add-ins you’d like to suggest, please share by posting a comment below … thanks!