Maintaining Word 2003 with linked Excel Tables 1

[Gavona]

Office 2003. We have a suite of word based reports that get updated monthly. Each has 5 to 10 tables (occasionally graphs) that are currently linked from excel workbooks. The tables can change shape (extra rows/columns) both at the start of each month and as the report is edited for that month. The challenge is in how to make sure that the tables can be easily updated/maintained, and new ones added by people unfamiliar with VBA. When the report is finalised or drafts are circulated for comment we break the links.

Never used word vba/object model and I am not an advanced word user so at this stage I am looking for tips as to the approach and any examples, useful links. (I have a target of 4 or 5 weeks to develop a solution.)

Current Plan
I was thinking of named ranges in the excel workbook with macros to assist there "in some way" (could probably work out a way to redefine named ranges with CurrentRegion and Offset and to highlight the current named range area so end user knows.
And a macro in the word document that will open linked files and refresh each linked table.

But should I get to better grips with words features?
Some sort of placeholder - maybe incorporating the excel rangename. Then copy the table in as a metafile or whatever (please advise). This controlled by a macro within the word document?

Advice welcomed!



Gavin

 

[fumei]

I am not quite understanding the problem. If the tables in Word are linked to Excel, do they not updated as required?

 

[Gavona]

Not when the excel table changes shape (one more or less row or column) - it seems relatively hard to define a link to a named range people cut and paste-link which results in cell references so part of the solution is to make inserting named range links easier. This is all maintained by users not particularly advanced in excel and certainly not in word.

You would not believe the problems this all causes. And the creation/refresh of the word documents is time critical.

I would also like to change the width of the link to fit the margins in word.

Gavin

 

[macropod]

Hi Gavin,

The simple solution is to edit the LINK fields in Word and replace the specified cell addresses with the range names. To do this, open Word, press Alt-F9 to toggle the field codes, then, in each LINK field, substitute the range names for the cell ranges. When you're done, press Alt-F9 again, then Ctrl-A, F9 to update. No code required.

Cheers
Paul Edstein
[MS MVP - Word]

 

[Gavona]

Thanks I haven't forgotten this tip just not got around to implementing it!

In 2007 at least my links look like this:

{LINK Excel.Sheet.8 "\\\\xxxxx\\yyyy\\zzzz\\...\\Service1 Current Month Tables.xls" "Pmnt Performance!BVPI8_10Day" \a \p}

What do the \a \p mean?

Gavin

 

[macropod]

For a full description of the LINK field's switches, see:

http://office.microsoft.com/en-us/word-help/field-codes-link-field-HP005186170.aspx

Cheers
Paul Edstein
[MS MVP - Word]

 

[Gavona]

Thanks, just what I needed.

Gavin