Updated by Brad Jones on August 25, 2017.
During your work week, there are probably many 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 is accumulated and updated in a spreadsheet. Excel is a great program for everything from creating professional-looking charts. 6 New Excel charts and how to use them. 6 New Excel charts and how to use them. Need to get more out of your Excel charts? Here's a complete guide to some of the new charts introduced in the latest version of Excel. Read More Here are the essential Microsoft Excel templates for you. Read More
In this article, we're going to dive into behind-the-scenes VBA scripting that allows you to program connections between data that is stored in an Excel file and Word documents where reports are produced.
It's surprisingly easy to integrate Microsoft Excel data into Microsoft Word once you know how to add the correct references and how to design the VBA code syntax behind the scenes.
In this example, I'm going to start with a fairly simple Excel spreadsheet. In practice, the Excel file can consist of several spreadsheets with a large amount of data, it doesn't matter.
As long as you know where to find the data in the spreadsheet, you can access and grab it using VBA. How to send emails from an Excel spreadsheet using VBA scripts How to send emails from an Excel spreadsheet using VBA scripts Our code template will help you set up automated emails from Excel using Collaboration Data Objects (CDO ) and VBA scripts. Read more.
This is what my sample spreadsheet looks like. It is a list of total expenses that have been calculated throughout the year.
Let's say you have a manager who would like to see a well-formatted report that describes expenses, grouping similar items together, 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 dropdown icon to insert several different elements into your document.
Use this menu to insert a Label .
Once you've placed the label on 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 give the tag a name so that the VBA can identify it. Right click on the label and go to Properties . Find the (Name) field and call it something you'll remember.
Now add a Command Button of the same Legacy tools dropdown list and double click it to open the VBA editor. When you get your code working later, you can modify it so that the code runs in the Document open() event. You'll see that in the object dropdown boxes in the editor window.
To start connecting Word to Excel, you need to make sure you can reference Excel in VBA. The Excel VBA Programming Tutorial for Beginners The Excel VBA Programming Tutorial for Beginners VBA is a powerful Microsoft Office tool. You can use it to automate tasks with macros, set triggers, and much more. We will introduce you to basic Excel visual programming with a simple project. Read more.
Click 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 data from an Excel spreadsheet and automatically update the label title with the data.
Here is 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 \ expend.xlsx") ThisDocument.total_expenses.Caption = exWb. Hojas ("Hoja1"). Celdas (12, 2) exWb.Cerrar Establecer exWb = Nothing End Sub See how that works? The "exWb" Excel Application object opens the Microsoft Excel file at the path you provided, and will go directly into the specific sheet and cell number, extract the data, and put it into the label's title property which I named total expenses . All you need to edit in your script is the file path and the tag name.
To test your command button, save your document and remember to specify that you want Word macro-enabled document for your code to work.
Here is the VBA macro in action.
The tricky part about dealing with tags in Word is that sometimes it's hard to line up at the end of a sentence or next to any other text.
One way to overcome this is by embedding some of the text along with the data into the VBA code itself. As you can see here, I put the static text directly into the title when creating the tag.
Now all you have to do is include that text when you update the label with your VBA script, and simply add the data from the Microsoft Excel file to the end of that text.
This is what that kind of code would look like.
Dim objExcel As New Excel.Application Dim exWb As Excel.Workbook Establezca exWb = objExcel.Workbooks.Open ("c: \ Users \ Brad \ Desktop \ expander.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 ( "Hoja1"). Células (2, 2) ThisDocument.total_tolls.Caption = "Tolls:" & exWb.Sheets ("Sheet1"). Cells (3, 2) ThisDocument.total_fuel.Caption = "Fuel:" & exWb. Hojas ("Hoja1"). Celdas (10, 2) exWb.Cerrar conjunto exWb = Nada You can use string concatenation. “Y” Symbol to place connect the static text with the data extracted from the Excel sheet. This is what the final results look like in the updated Word document:
If you want to test your skills, why not automate your 5 Resources for Excel Macros report to automate your spreadsheets? 5 Resources for Excel Macros to Automate Your Spreadsheets Looking for Excel Macros? Here are five sites that have what you're looking for. Read More You can remove that ugly gray command button from your Microsoft Word document, simply by running the data refresh script in 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 have to do is open it, and all the labels will be automatically updated with the data from the updated Microsoft Excel file. Just click Print , and send the report to your manager. A 30-minute job just turned into a one-minute print!
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.