none
Programmatically update Word Link/Embedded Files Source Location RRS feed

  • Question

  • Hi I'm not sure if I posted this in the right forum, so please feel free to point me in the right direction.
    We are in the middle of migrating thousands of Word and Excel (Office 2010) files from Novel servers to Windows servers.

    The issue we have is that the vast majority of this Word documents have link or embedded Excel files.
    We need to update the source file location in the Word files and manually doing so is out of the question, this will be an impossible task.

    I have search and found .NET code that will modify the source location of link/embedded files in Word.

    Is there a way to accomplish this automatically or with minimum manual intervention?
    Is there any sample code that I can modify to accomplish this?
    I'm using VS2012 and VB (I know this is not the best, I should use C#).
    Any advice will be greatly appreciated

    Gil


    • Edited by GRuiz Friday, January 29, 2016 1:24 PM
    Monday, January 18, 2016 3:51 PM

Answers

  • >>>Is there a way to accomplish this automatically or with minimum manual intervention?

    To specify how links are updated, follow these steps:
        1.    Choose Linked Worksheet Object -> Links... in the popup menu to display the Links dialog box:
    popup menu in Word 2016

       2.    The Links dialog box lists all of the links in the active Word document. Select one or more. To select multiple links, click once on the first link, press Ctrl, and then click once on each of the other links that you want to edit. If you have several links and want to select them all, click once on the top link, press Shift, and then click once on the last link in the list. 

       3.    Choose Automatic for automatic links, or choose Manual for manual links. You can specify different options for different links.

    •With manual links, Word updates the links only when you decide to update them.
     There are two methods to update links manually. The simplest is to position the cursor somewhere in the linked object and then press F9. The other is to choose Linked Worksheet Object -> Links....

    Using Linked Worksheet Object -> Links... may be quicker, however, if you have several links in your document because this method enables you to see a list of all links. On the other hand, you can also press Ctrl+A to select everything in your document and then press F9. (Unfortunately, Ctrl+A doesn't select links inside headers, footers, and comments, although it's rare that linked objects are placed in these areas.) This updates all fields in your document, not just linked objects.

    The default setting is to update all links automatically when the information in the source file changes, but you can specify either method for any link.

    You can lock and unlock a link in the Links dialog box. However, you can also lock a link by selecting a link and pressing Ctrl+F11; unlock the link by pressing Ctrl+Shift+F11.

    Rather than locking a link, why not set the link to Manual and then just don't update it-to ensure that you don't accidentally manually update it. If, for instance, you press Ctrl+A to select the entire document and then press F9 to update the table of contents, index, and cross-references, you are also updating unlocked manual links.

    >>>Is there any sample code that I can modify to accomplish this?
    I'm using VS2012 and VB (I know this is not the best, I should use C#).<<<

    To re-set each Excel link to manual update, you could refer to VBA codes then change them into C#:

    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 '
     Dim x As Long
     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
     With ActiveDocument
       fieldCount = .Fields.Count
       For x = 1 To fieldCount
         With .Fields(x)
           'Debug.Print x '
           Debug.Print .Type
           If .Type = 56 Then
             'only update Excel links. Type 56 is an excel link
             .LinkFormat.SourceFullName = newFile '
             .Update
             .LinkFormat.AutoUpdate = False
             DoEvents
           End If
         End With
       Next x
     End With
     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

    For more information, click here to refer about Changing Word link source via VBA resets Update method to "Automatic"

    • Marked as answer by David_JunFeng Friday, January 29, 2016 9:46 AM
    Saturday, January 23, 2016 5:28 AM
  • >>>Many thanks for the excellent coding. I have just one small question. Is it possible to code the file format being used as per the link snippet below:

    From: "...R17C11" \b}" representing Bitmap

    To: "...R17C11" \p}" representing Picture etc.<<<

    According to your description, please correct me if I have any misunderstandings on your question, you could use Link field that the Link field links information from another application to your Word document by using OLE. Microsoft Word inserts this field when you copy information from another application and paste it into a Word document by using the Paste Special command (Home tab, Clipboard group, Paste command arrow button).

    For more information, click here to refer about Field codes: Link field

    • Marked as answer by GRuiz Wednesday, March 9, 2016 1:15 PM
    Wednesday, March 9, 2016 8:25 AM

All replies

  • We are doing the research about your problem. There might be some delay about the response. Appreciate your patience.
    Wednesday, January 20, 2016 9:41 AM
  • >>>Is there a way to accomplish this automatically or with minimum manual intervention?

    To specify how links are updated, follow these steps:
        1.    Choose Linked Worksheet Object -> Links... in the popup menu to display the Links dialog box:
    popup menu in Word 2016

       2.    The Links dialog box lists all of the links in the active Word document. Select one or more. To select multiple links, click once on the first link, press Ctrl, and then click once on each of the other links that you want to edit. If you have several links and want to select them all, click once on the top link, press Shift, and then click once on the last link in the list. 

       3.    Choose Automatic for automatic links, or choose Manual for manual links. You can specify different options for different links.

    •With manual links, Word updates the links only when you decide to update them.
     There are two methods to update links manually. The simplest is to position the cursor somewhere in the linked object and then press F9. The other is to choose Linked Worksheet Object -> Links....

    Using Linked Worksheet Object -> Links... may be quicker, however, if you have several links in your document because this method enables you to see a list of all links. On the other hand, you can also press Ctrl+A to select everything in your document and then press F9. (Unfortunately, Ctrl+A doesn't select links inside headers, footers, and comments, although it's rare that linked objects are placed in these areas.) This updates all fields in your document, not just linked objects.

    The default setting is to update all links automatically when the information in the source file changes, but you can specify either method for any link.

    You can lock and unlock a link in the Links dialog box. However, you can also lock a link by selecting a link and pressing Ctrl+F11; unlock the link by pressing Ctrl+Shift+F11.

    Rather than locking a link, why not set the link to Manual and then just don't update it-to ensure that you don't accidentally manually update it. If, for instance, you press Ctrl+A to select the entire document and then press F9 to update the table of contents, index, and cross-references, you are also updating unlocked manual links.

    >>>Is there any sample code that I can modify to accomplish this?
    I'm using VS2012 and VB (I know this is not the best, I should use C#).<<<

    To re-set each Excel link to manual update, you could refer to VBA codes then change them into C#:

    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 '
     Dim x As Long
     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
     With ActiveDocument
       fieldCount = .Fields.Count
       For x = 1 To fieldCount
         With .Fields(x)
           'Debug.Print x '
           Debug.Print .Type
           If .Type = 56 Then
             'only update Excel links. Type 56 is an excel link
             .LinkFormat.SourceFullName = newFile '
             .Update
             .LinkFormat.AutoUpdate = False
             DoEvents
           End If
         End With
       Next x
     End With
     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

    For more information, click here to refer about Changing Word link source via VBA resets Update method to "Automatic"

    • Marked as answer by David_JunFeng Friday, January 29, 2016 9:46 AM
    Saturday, January 23, 2016 5:28 AM
  • David

    Thanks for the code this will help a lot with the Excel with Word links.
    Is there a way to do the same but with Word with Excel links?
    I have research for days but haven't got a definitive answer for Word?

    Friday, January 29, 2016 1:28 PM
  • >>>Thanks for the code this will help a lot with the Excel with Word links.
    Is there a way to do the same but with Word with Excel links?<<<

    According to your description, I suggest that you could refer to below code to check Link Files whether they are Excle files:

    Sub UpdateLinkDataSource() Dim NewFileName As String

    NewFileName="your excel file path" With ActiveDocument fieldCount = .Fields.Count For x = 1 To fieldCount With .Fields(x) 'Debug.Print x ' Debug.Print .Type If .Type = 56 Then 'only update Excel links. Type 56 is an excel link Debug.Print .LinkFormat.SourceFullName If IsExcelFile(.LinkFormat.SourceFullName) Then .LinkFormat.SourceFullName = NewFileName .Update .LinkFormat.AutoUpdate = False DoEvents End If End If End With Next x End With End Sub Function IsExcelFile(filePath As String) As Boolean 'Retrieve extension of file fileextension = Mid(filePath, InStrRev(filePath, ".") + 1) 'define which extensions you want to allow arr = Array("xls", "xlsx") For Each Item In arr If Item = fileextension Then IsExcelFile = True End If Next IsExcelFile = False End Function


    For more information, click here to refer about WdFieldType Enumeration

    Monday, February 1, 2016 8:59 AM
  • David

    Many thanks for the excellent coding. I have just one small question. Is it possible to code the file format being used as per the link snippet below:

    From: "...R17C11" \b}" representing Bitmap

    To: "...R17C11" \p}" representing Picture etc.

    Thank you

    Jim


    Monday, March 7, 2016 2:28 PM
  • >>>Many thanks for the excellent coding. I have just one small question. Is it possible to code the file format being used as per the link snippet below:

    From: "...R17C11" \b}" representing Bitmap

    To: "...R17C11" \p}" representing Picture etc.<<<

    According to your description, please correct me if I have any misunderstandings on your question, you could use Link field that the Link field links information from another application to your Word document by using OLE. Microsoft Word inserts this field when you copy information from another application and paste it into a Word document by using the Paste Special command (Home tab, Clipboard group, Paste command arrow button).

    For more information, click here to refer about Field codes: Link field

    • Marked as answer by GRuiz Wednesday, March 9, 2016 1:15 PM
    Wednesday, March 9, 2016 8:25 AM
  • I have a problem with a word document. There are linked images in the header, but I'm unable to access them by vba. The only way I can change the link is by going to File menu > Edit file links... 
    When I get into Edit file links dialog, the update method for selected link is greyed

    I've tried to look for the document fields and the header fields, all get a count of zero

    I'm sorry if my description is not clear enough, please ask for clarifications!

    Thank you!

     

    Cesc Llagostera

    Tuesday, May 28, 2019 8:19 AM