How to import and maintain dynamic tables in Word 2

[DeenaLarsen]

Hi,
We are trying to create a Word document with about 100 dynamic tables from either an access db, excel or SQL db.(These tables are basically an inventory of an SQL database as a reference guide). We will update whatever db or excel application, and we want the Word document to be linked to reflect these changes.

EXCEL--When we do a copy from Excel, paste special as link to Word, life is great--until we add another row to the Excel spreadsheet. Then every single line on every single table gets moved down one--and the tables no longer make sense.

MS QUERY from ACCESS--Works great, except the resulting table is not dynamic--we can't figure out how to get changes in Access to appear in the Word document.

NEED:
We need to be able to do a dynamic link-like paste --but to be able to define the excel spreadsheet coordinates in an MS query way (create a table of all the elements with sitedefx, or mbtag...)

Please help! Has anyone had any success with something like this?

 

[Remou]

Hi
You can used named ranges in Excel. These can be pasted as Linked Worksheet Objects. If a range is changed, you need only redefine that range for the tables to update correctly.
Access tables and queries can be added to Word using the Database toolbar. Data can be inserted as Fields.
I tested the above (lightly) using Office 2k.

 

[DeenaLarsen]

Ramou, THANK YOU!

You pointed me in the right direction.

To create a series of dynamic tables in Word that are based on Excel spreadsheets, you have to work with named ranges.

Name the range in the excel spreadsheet by selecting the cells you want in the table and typing a name in the formula bar. (Names cannot have spaces or weird characters)

Copy this range

Paste as special in the Word document, selecting Paste Link and Microsoft Excel Worksheet object.

Now if you want to add or subtract rows in your selected range, just go ahead and do that in Excel.

After modifying your rows in Excel, you must re-apply the range to the modified rows.

To do this, select the rows and go to insert--name--define. You'll get a list of names, and all you have to do is click on the old name and hit enter.

Voila, the table now reflects correctly in Word and Excel.

HOORAY!!! You saved my project!!!

 

[macropod]

Hi Deena,

You still need to be aware that you can't insert (or link to) an object spanning more than one page. If you do, only the first page will show up. To get around this limitation, you can link to the Excel workbook using the 'formatted text (RTF)' option. That gives you a table in Word that can span more than one page.

Cheers

 

[DeenaLarsen]

Ahh, but if I link using the formatted text (RTF) option, I lose the dynamic linking updates?

I've just played with pasting as rtf--when I add a new row to the Excel table and redefine the name to include the new row, then I lose a row off the end of my table in Word.

Maybe my best bet is to split up tables into no more than 20 rows so that they don't span a page.

Are you talking about spanning pages in Word or in Excel? I assume Excel?

Thanks!

 

[macropod]

Hi Deena,

I tried both:
. adding rows in the middle of the existing range; and
. extending the range to include new rows,
in Excel, and Word handled both RTF link scenarios correctly. Did you remember to update the link in Word?

As for page spanning, the limit is within Word. For example, if you embed a two-page Excel object in Word, only as much as will fit on the first page will show - increase the page size in Word and you'll see more. Similarly, if you're half-way down the page in Word and embed a full-page Excel object, Word will place the whole object on the next page.

Cheers

 

[DeenaLarsen]

Thanks MacroPod

I don't know what I was doing the first time--but I was able to save and paste special as RTF rather than Microsoft Object and it works just fine.

Still, I am breaking the tables into small portions.

After all, it's only trees I'm killing