You are currently viewing the Word VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .

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

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.

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