Changing field links in multiple word documents from one excel file to another

yondor · #1 03-02-2023, 02:34 AM

Hello,

I have multiple word files (word 1, word 2, etc.) which take data from one excel file (excel 1). These files are essentially templates, which I would like to be able to copy to a new folder, and then change data in the excel file to result in word files with the new data.

I have successfully used the code in post 8 in the following thread (thanks macropod!) to update field links in one of the word documents to the new excel file:
https://www.msofficeforums.com/word-...one-excel.html

I would like to ask if there is a way that I can cycle through all my word documents to process them all at once with this code?

Also, is it possible to change the name of the word documents to include information from the excel file before saving? e.g. "word 1 'B2'.docx", "word 2 'B3'.docx", where B2 and B3 will contain the writing from the corresponding excel cells.

Many thanks in advance!

yondor · #2 03-02-2023, 09:22 PM

After looking some more on this forum, I tried the following code:
(source:https://www.msofficeforums.com/word-...ocx-files.html)

Code:

Sub UpdateDocuments()
Application.ScreenUpdating = False
Dim strFolder As String, strFile As String, strDocNm As String, wdDoc As Document
strDocNm = ActiveDocument.FullName: strFolder = GetFolder
If strFolder = "" Then Exit Sub
strFile = Dir(strFolder & "\*.docx", vbNormal)
While strFile <> ""
  If strFolder & "\" & strFile <> strDocNm Then
    Set wdDoc = Documents.Open(FileName:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
    With wdDoc
      'Call your other macro or insert its code here
      Call Update_Links
      .Close SaveChanges:=True
    End With
  End If
  strFile = Dir()
Wend
Set wdDoc = Nothing
Application.ScreenUpdating = True
End Sub
 
Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function

Using the above to call the below code. The below alone already works for me on a single word doc:

Code:

Sub Update_Links()
'
' Update_Link Macro
'
Dim Rng As Range, i As Long, vItem As Variant
'
'Display File Picker Dialog
With Application.FileDialog(FileDialogType:=msoFileDialogFilePicker)
  ' use Show method to dispaly File Picker dialog box and return user's action
  If .Show = -1 Then
  'step throug each string in the FileDialogSelectedItems collection
    vItem = .SelectedItems(1)
  Else
    Exit Sub
  End If
End With
'
'update fields
'
Application.ScreenUpdating = False
With ActiveDocument
  For Each Rng In .StoryRanges
    With Rng
      For i = .Fields.Count To 1 Step -1
        If .Fields(i).Type = wdFieldLink Then
          .Fields(i).LinkFormat.SourceFullName = vItem
          .Fields(i).Update
        End If
      Next
      For i = .ShapeRange.Count To 1 Step -1
        If Not .ShapeRange(i).LinkFormat Is Nothing Then
          .ShapeRange(i).LinkFormat.SourceFullName = vItem
          .ShapeRange(i).LinkFormat.Update
        End If
      Next
      For i = .InlineShapes.Count To 1 Step -1
        If Not .InlineShapes(i).LinkFormat Is Nothing Then
          .InlineShapes(i).LinkFormat.SourceFullName = vItem
          .InlineShapes(i).LinkFormat.Update
        End If
      Next
    End With
  Next
End With
'
Application.ScreenUpdating = True
MsgBox "Done!"
End Sub

However, the UpdateDocuments macro isn't working for me to update all documents. Any thoughts? Apologies, I'm new to this..

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Changing custom properties in multiple word documents	wwballar42	Word VBA	12	04-29-2024 04:06 PM
Changing footer image in Multiple word documents	killabyte	Word VBA	2	09-23-2020 05:49 AM
Merge one Excel file into multiple Word Documents	Rushpac	Mail Merge	2	01-27-2017 02:49 PM
Changing Field Links in a Word Document from one excel file to another	Fed77	Word VBA	9	07-08-2015 02:24 AM
Inputing Text from Excel sheet to multiple Word documents and Visio File	Jazz43	Office	1	05-30-2013 02:08 PM