Changing Word link source via VBA resets Update method to "Automatic"

Hey all,

I have a Word document with several hundred links to an Excel sheet. I copied the below Word macro from another forum and then tweaked it for my own use to allow the user to select a new Excel file via the file open dialog box and change the link source on all links to the newly selected document. It works fairly well, but it resets the Update method to "Automatic" on all of the links. I have to manually set the Update method on all links back to "Manual." My users are not going to remember to do that, and they are likely to have both the Word and the linked Excel documents open simultaneously.

How can I either stop it from doing this, or set the method back to "Manual" via code? I can't figure that out, and I can't have the document updating all links every time the Excel document is changed since there are so many connections. Help?

[code]

Public Sub changeSource()

Dim dlgSelectFile As FileDialog 'FileDialog object 'Dim thisField As Field Dim selectedFile As Variant 'must be Variant to contain filepath of selected item Dim newFile As Variant Dim fieldCount As Integer

'On Error GoTo LinkError

'create FileDialog object as File Picker dialog box Set dlgSelectFile = Application.FileDialog(FileDialogType:=msoFileDialogFilePicker)

With dlgSelectFile

.Filters.Clear 'clear filters
.Filters.Add "Microsoft Excel Files", "\*.xls, \*.xlsb, \*.xlsm, \*.xlsx" 'filter for only Excel files

'use Show method to display File Picker dialog box and return user's action If .Show = -1 Then 'step through each string in the FileDialogSelectedItems collection For Each selectedFile In .SelectedItems newFile = selectedFile 'gets new filepath Next selectedFile Else 'user clicked cancel Exit Sub End If End With

Set dlgSelectFile = Nothing

'update fields fieldCount = ActiveDocument.Fields.Count

For x = 1 To fieldCount 'Debug.Print x 'Debug.Print ActiveDocument.Fields(x).Type If ActiveDocument.Fields(x).Type = 56 Then 'only update Excel links. Type 56 is an excel link ActiveDocument.Fields(x).LinkFormat.SourceFullName = newFile 'DoEvents End If Next x

MsgBox "Source data has been successfully imported."

Exit Sub

LinkError:

Select Case Err.Number Case 5391 'could not find associated Range Name MsgBox "Could not find the associated Excel Range Name for one or more links in this document. " & _ "Please be sure that you have selected a valid Quote Submission input file.", vbCritical

Case Else
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical

End Select

End Sub
[/code]