lfrobes2
01-15-2009, 09:34 AM
Hello,
I have an automated document that links to an excel spreadsheet. When using Word 97, updating the links worked fine and very quickly. However, since a change to Windows XP Professional the updating now takes an age.
The code I have at the moment is as follows:
Sub UpdateLinkedInformation()
'procedure to update fields with an external file reference
Dim numFields As Integer, Source$, LoopCount As Integer, Message$, Buttons As Integer, _
Title$, Response As Integer, aStory As Range
Application.ScreenUpdating = False
numFields = ActiveDocument.Fields.Count
' if no fields then go no further
If numFields = 0 Then End
' want to give user an indication of the current external link
' so need routine to find filepath and filename in first external link
Source$ = ""
LoopCount = 1
Do While Source$ = ""
If ActiveDocument.Fields(LoopCount).Type = wdFieldLink Then
Source$ = ActiveDocument.Fields(LoopCount).LinkFormat.SourcePath _
& "\" & ActiveDocument.Fields(LoopCount).LinkFormat.SourceName
End If
LoopCount = LoopCount + 1
If LoopCount > numFields Then Exit Do
Loop
If Source$ = "" Then End
' let user decide whether to proceed or not
Message = "If the source spreadsheet is not open in Excel" & Chr(10) & _
"the update will take a very long time." & Chr(10) & Chr(10) & _
"The linked spreadsheet is:" & Chr(10) & _
Source$ & Chr(10) & Chr(10) & "Is this spreadsheet open?"
Buttons = vbYesNo + vbExclamation + vbDefaultButton2
Title = "Check - linked spreadsheet should be open"
Response = MsgBox(Message, Buttons, Title)
If Response <> vbYes Then End
'update contents of all fields
For Each aStory In ActiveDocument.StoryRanges
' first part covers first occurence of that story type
aStory.Fields.Update
'this part covers subsequent occurences
Do While Not (aStory.NextStoryRange Is Nothing)
Set aStory = aStory.NextStoryRange
aStory.Fields.Update
Loop
Next aStory
'highlight all linked fields
HighlightLinkedFields
'update all non-linked fields
UpdateFields
'clean up rogue hidden fields that Word97 creates
Application.ScreenUpdating = True
SortOutBookmarks
End Sub
The document isn't even very big (20 pages) and takes a couple of minutes to update and the document template will have to be applied to much larger files soon too.
Can anybody help please and suggest another code that will achieve the same outcome but at a fraction of the time?
Thanks in advance for any help.
I have an automated document that links to an excel spreadsheet. When using Word 97, updating the links worked fine and very quickly. However, since a change to Windows XP Professional the updating now takes an age.
The code I have at the moment is as follows:
Sub UpdateLinkedInformation()
'procedure to update fields with an external file reference
Dim numFields As Integer, Source$, LoopCount As Integer, Message$, Buttons As Integer, _
Title$, Response As Integer, aStory As Range
Application.ScreenUpdating = False
numFields = ActiveDocument.Fields.Count
' if no fields then go no further
If numFields = 0 Then End
' want to give user an indication of the current external link
' so need routine to find filepath and filename in first external link
Source$ = ""
LoopCount = 1
Do While Source$ = ""
If ActiveDocument.Fields(LoopCount).Type = wdFieldLink Then
Source$ = ActiveDocument.Fields(LoopCount).LinkFormat.SourcePath _
& "\" & ActiveDocument.Fields(LoopCount).LinkFormat.SourceName
End If
LoopCount = LoopCount + 1
If LoopCount > numFields Then Exit Do
Loop
If Source$ = "" Then End
' let user decide whether to proceed or not
Message = "If the source spreadsheet is not open in Excel" & Chr(10) & _
"the update will take a very long time." & Chr(10) & Chr(10) & _
"The linked spreadsheet is:" & Chr(10) & _
Source$ & Chr(10) & Chr(10) & "Is this spreadsheet open?"
Buttons = vbYesNo + vbExclamation + vbDefaultButton2
Title = "Check - linked spreadsheet should be open"
Response = MsgBox(Message, Buttons, Title)
If Response <> vbYes Then End
'update contents of all fields
For Each aStory In ActiveDocument.StoryRanges
' first part covers first occurence of that story type
aStory.Fields.Update
'this part covers subsequent occurences
Do While Not (aStory.NextStoryRange Is Nothing)
Set aStory = aStory.NextStoryRange
aStory.Fields.Update
Loop
Next aStory
'highlight all linked fields
HighlightLinkedFields
'update all non-linked fields
UpdateFields
'clean up rogue hidden fields that Word97 creates
Application.ScreenUpdating = True
SortOutBookmarks
End Sub
The document isn't even very big (20 pages) and takes a couple of minutes to update and the document template will have to be applied to much larger files soon too.
Can anybody help please and suggest another code that will achieve the same outcome but at a fraction of the time?
Thanks in advance for any help.