Updating multiple excel links in word

Updating multiple excel links in word

Post by SXZhbiBWYW » Thu, 09 Oct 2008 22:18:01


I have a word document with several links to an excel file. If I update them
using a pseudo code as the following:

For Each InlineShape In ActiveDocument.InlineShapes
InlineShape.Fields.Update
Next InlineShape

it takes ages. As it seems, Word opens and closes the excel file for every
update. Of course, this takes some time when you have more than 50 links.

In my case, all links point to the same excel file. Taking into account that
fact, isn't there a way to speed up the process ?

Thanks for your help.

Ivan
 
 
 

Updating multiple excel links in word

Post by Doug Robbi » Fri, 10 Oct 2008 07:21:11

In Tools>Options>General, check the box for "Update automatic lkinks at
Open"

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 
 
 

Updating multiple excel links in word

Post by SXZhbiBWYW » Fri, 10 Oct 2008 17:27:02

Hi Doug,

Thanks for your help.

The links in my word document are all set to be updated manually. I just
want them to be updated when the MACRO is launched. However, I tried
unchecking the box "Update automatic links at open", as I figured out it
wouldn't apply to my program, and surprisingly enough the MACRO runs a little
faster.

Anyway, isn't there a way of telling word (using vba) to open just once my
excel source file, update all links in the word document and then close the
excel file? I guess that would save a lot of time compared to opening /
closing the same file for each link.

I guess there must be a workaround, I cannot be the first person willing to
update many excel links in a word document using vba without having to wait
for more than 10 min.

Ivan
 
 
 

Updating multiple excel links in word

Post by Jonathan W » Fri, 10 Oct 2008 18:20:56


Just a thought. Try getting your macro to start a copy of Excel and open the
target worksheet before you attempt to update the links, and then close
Excel once the links have all been updated. It might be that the link update
works much quicker when referencing a workbook that is already open

The Excel instance can always be invisible if you don't want users to see it
open and wonder why.


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
 
 
 

Updating multiple excel links in word

Post by SXZhbiBWYW » Fri, 10 Oct 2008 19:33:01

Thanks Jonathan, that did work pretty good. I've reduced to 1/6 the time it
takes to update the links.

Ivan