Faster way to update external links in a large document

utilityplayer469 · September 3rd, 2009, 10:51 AM

I have a large document (~100 pages) and it has hundreds of links to a single excel file because the document is used for several different jobs. I have created a macro that udates the source to all of the links very quickly but I need a code that will update the links faster. I believe the document is opening and closing the excel file for every link. Right now it is taking approximately 2 hours to update the links. Please help! Thanks

Shasur · September 3rd, 2009, 10:10 PM

Hi

Do you mean to say the Word document opens the Excel and checks for specific info for each link?

Can you post your code snippet so that we can optimize it

Cheers
Shasur

utilityplayer469 · September 4th, 2009, 08:28 AM

In my word document, I have a lot of words that are linked to different cells in one excel file. If I highlight the whole document, in word, and F9 to update the links, it takes about 2 hours. I was looking for a code that would update the links faster and more efficiently. The code that I have now is for updating the source (because the file is copied and moved into new folders). This code I don't want to change because I want it to be a separate action. The only code I have found to update the links is as follows:

Sub updateLinks()
'created to update the links
'added August 25, 2009 by Melissa Murphy
Dim oStory As Range
Dim oField As Field
For Each oStory In ActiveDocument.StoryRanges
For Each oField In oStory.Fields
oField.Update
Next oField
Next oStory
End Sub

But this one takes right at two hours to update all of the links as well.

Thanks for your time!

Shasur · September 5th, 2009, 10:41 AM

Hi

Since you are updating all fields, can you try the following:

ActiveDocument.Fields.Update

Cheers
Shasur

utilityplayer469 · September 5th, 2009, 10:55 AM

Thank you for the help but that doesn't make the links update any faster. It still takes the same almost 2 hours.

Thanks,

Melissa

Shasur · September 5th, 2009, 09:59 PM

Hi Mellisa

I am afraid if you are looping through the Word.story for this too

Can you please try

Code:

Sub updateAllLinks()
ActiveDocument.Fields.Update
End Sub

If this is not the one, can you post your modified code?

Cheers
Shasur

utilityplayer469 · September 8th, 2009, 07:51 AM

Thank you for your help again. I have posted the same code that you have provided, to update the links, but it does not increase the wait time for the document to update all 200+ links.

Thanks,

Melissa

macroman · March 30th, 2011, 10:40 AM

Hi Melissa,
Not sure if you or anyone else is still watching this forum. I'm in the same situation as you were. Can I ask if you found a solution? I have come up with code that at least gets mine down to about 30 mins for 400+ links. I first open the linked Excel file using one of the links in the document and then use this code to update each.

Code:

Sub UpdateLinksIndividually()
Application.ScreenUpdating = False
Dim vNumFields As Long
Dim i As Long
vNumFields = ActiveDocument.Fields.Count

If vNumFields = 0 Then
MsgBox "No fields found, exiting."
Exit Sub
End If

For i = 1 To vNumFields
ActiveDocument.Fields(i).Update
Next i

MsgBox "Updating Complete!"
Application.ScreenRefresh
End Sub

Don't know if that helps at all. Anyone else have suggestions?
Thanks,
Macroman