Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Other Office > Word VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Word VBA Discuss using VBA to program Word.
Welcome to the p2p.wrox.com Forums.

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 .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old September 3rd, 2009, 11:51 AM
Authorized User
 
Join Date: Sep 2009
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Exclamation Faster way to update external links in a large document

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
Reply With Quote
  #2 (permalink)  
Old September 3rd, 2009, 11:10 PM
Friend of Wrox
Points: 3,060, Level: 23
Points: 3,060, Level: 23 Points: 3,060, Level: 23 Points: 3,060, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

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
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
Reply With Quote
  #3 (permalink)  
Old September 4th, 2009, 09:28 AM
Authorized User
 
Join Date: Sep 2009
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!
Reply With Quote
  #4 (permalink)  
Old September 5th, 2009, 11:41 AM
Friend of Wrox
Points: 3,060, Level: 23
Points: 3,060, Level: 23 Points: 3,060, Level: 23 Points: 3,060, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi

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

ActiveDocument.Fields.Update

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
Reply With Quote
  #5 (permalink)  
Old September 5th, 2009, 11:55 AM
Authorized User
 
Join Date: Sep 2009
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #6 (permalink)  
Old September 5th, 2009, 10:59 PM
Friend of Wrox
Points: 3,060, Level: 23
Points: 3,060, Level: 23 Points: 3,060, Level: 23 Points: 3,060, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

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
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
Reply With Quote
  #7 (permalink)  
Old September 8th, 2009, 08:51 AM
Authorized User
 
Join Date: Sep 2009
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #8 (permalink)  
Old March 30th, 2011, 11:40 AM
Registered User
 
Join Date: Mar 2011
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Another Method

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
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Firefox - XSLT - document.links has no properties tripecac XSLT 5 April 27th, 2007 01:35 PM
External Multimedia Links bcogney XML 1 February 26th, 2007 10:28 PM
opening external links in a new window. madhukp Javascript How-To 4 February 23rd, 2005 02:27 AM
Removing external links in Excel tcarnahan Excel VBA 9 February 22nd, 2004 07:21 AM
Updating external links Justine Access VBA 3 January 14th, 2004 05:24 PM



All times are GMT -4. The time now is 10:53 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.