Export N number of Data entries from excel to word

Question

I was wondering whether it's possible to create a data entry set from Microsoft Excel and export that directly into Microsoft Word?

For example, if I were making a phone book that lists the person's First Name, Last Name, address and phone number in Excel, this would automatically export the data to Word.

I'm not looking to have a set number of entries in Word and copy and paste, but rather as I add on more entries to Excel, the data is pushed from Excel into Word. I'm not an expert coder myself, but I've watched multiple videos on YouTube, and I am looking for guidance.

Answer 1

It's an old command that's no longer exposed in the Word UI by default: InsertDatabase. You'll find it in File/Options/Customize Ribbon or Quick Access Toolbar, under All Commands.

This inserts a field with the name Database, via a set of dialog boxes.

1. Get Data is the same as what you see in mail merge when selecting the data source. This uses any valid connection method (these days, ODBC or OLE DB - the latter is the default) to bind to the data source. In your case, an Excel worksheet. If you've assigned a Range Name to the data, that is also available for selection.

2. Data options You can set Query Options (filter/sort what comes in). You can also set Table Auto Format to choose a pre-defined (or user-defined) Table Style.

3. Insert Data where the records can be selected. This is important: activate the checkbox Insert Data as field. This is what will make the data dynamic.

4. OK to insert the data / field. The result will look something like the following, which you can see (and edit) by pressing Alt+F9 to toggle from field result to field code. This is the connection information.

{ DATABASE \d "C:\Users\[User name]\Documents\SampleChart.xlsx" \c "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\Cindy Meister\Documents\SampleChart.xlsx;Mode=Read;Extended Properties=\"HDR=YES;IMEX=1;\";Jet OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=\"\";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False" \s "SELECT * FROM ChartInfo" \h }

This field will not update automatically whenever anything in Excel changes, you have to force the update by clicking in it and pressing F9. Or you can use a macro to do so. Simplest, if you don't want to simply update all fields in the document body, would be to bookmark the table, plus a paragraph on each end and use

ActiveDocument.Bookmarks("Database").Range.Fields.Update 

Answer 2

It seems to me you could probably do this with something no more complicated than a LINK field in Word, which you can create quite simply via Copy (in Excel) then Paste Special>Paste Link (in Word), with a suitable paste format. If you name the Excel range before copying & pasting, the range in Word will auto-expend/contract to match what's happening with the Excel range. No code required.