Update Links in Word to Excel

[Rick Kula]

All,

I have an Access Program that copies and excel document
from a standard template, populates it with values from a
db, then copies a word doc from a standard template (this
document includes links to the standard excel template,
anywere from 100 to 1000). Then the program changes the
link source the the new excel spreadsheet.

This process takes approx 1/2 hour to complete. Is there a
faster way then stepping through the object model to
complete this task?

 

[Cindy Meister -WordMVP-]

Hi Rick,

I have an Access Program that copies and excel document
from a standard template, populates it with values from a
db, then copies a word doc from a standard template (this
document includes links to the standard excel template,
anywere from 100 to 1000). Then the program changes the
link source the the new excel spreadsheet.

This process takes approx 1/2 hour to complete. Is there a
faster way then stepping through the object model to
complete this task?

Are the linked Excel tables linked as objects, or as Word
tables? If the former, do they have any text-wrap formatting?
(Are the Shapes or InlineShapes?)

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jan
24 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail

 

[Rick Kula]

They are linked as objects a mix of Shapes and InlineShapes.
The document is a financial analysis, so we have some charts and
some tables from excel. There are are links to particular cells.

Rick

 

[Cindy Meister -WordMVP-]

Hi Rick,

They are linked as objects a mix of Shapes and InlineShapes.
The document is a financial analysis, so we have some charts and
some tables from excel. There are are links to particular cells.

There's no way to speed up changing the links of the Shapes,
because there's no way to get to the underlying field codes.

For the InlineShapes you can use Find/Replace on the field codes:
Alt+F9 to turn on the field codes, Find/Replace the path, then
Alt+F9 again. ActiveDocument.Fields.Update will then update these
links.

Please note, however, that the time you save may be minimal: the
actual updating of what the brings across is probably the larger
factor, and there's no way to increase this. For every link, Excel
and the workbook are being opened in the background, the data
passed across, and Excel being unloaded again. For 1000 links this
can easily take half and hour.

Another approach I've used is to save the path in a custom
document property and use a DocProperty field in the LINK field.
Then the path needs to be changed in only one place, and the
individual links can be updated as required (not necessarily all
at once on opening the document, for example).

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30
2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail

 

[Rick Kula]

Cindy,

Thanks for the response. You confirmed what I had thought.

Regards,

Rick