Changing Field Links in a Word Document from one excel file to another

Fed77 · #1 07-03-2015, 02:02 AM

Thanks in advance,
I have a word document that have links to an excel spreadsheet throughout the body and in the header and footer.

I need to update the source to all the links by using a macro in word VBA.
The following macro should be able to offer a dialog box to user then they select file and it replaces current source in all links in the word doc, but it is not working in the header and footer.
Someone could help me to figure out what is wrong?
__________________________________________________ _______________

Code:

Sub Update_link()
'
' Update_link Macro
'
Dim oStory As Range
Dim oField As Field
Dim dlgSelectFile As FileDialog 'Filedialog Object
Dim thisFiled As Field
Dim selectedFile As Variant ' contain filepath of selected item
Dim newfile As Variant
Dim fieldCount As Integer, FldInx As Integer
'
'create FileDialog object as File picker Dialog boz
'
Set dlgSelectFile = Application.FileDialog(FileDialogType:=msoFileDialogFilePicker)
'
With dlgSelectFile
  ' 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
    For Each selectedFile In .SelectedItems
      newfile = selectedFile 'gets new filepath
    Next selectedFile
  Else 'user clicked cancel
  End If
End With
Set dlgSelectFile = Nothing
'
'update fields
'
For Each oStory In ActiveDocument.StoryRanges
  fieldCount = oStory.Fields.Count
  FldInx = 1
  While FldInx < fieldCount
    On Error Resume Next
    oStory.Fields(FldInx).LinkFormat.SourceFullName = newfile
    On Error GoTo 0
    FldInx = FldInx + 1
  Wend
Next oStory
'
End Sub

__________________________________________________ ______________

macropod · #2 07-03-2015, 02:57 AM

You need to add code that loops through Word's header & footer StoryRange objects by Section. See, for example: https://www.msofficeforums.com/word-...html#post73667

Fed77 · #3 07-06-2015, 07:23 AM

I tried to follow your suggestion but something is not working.
I'm not able to find out a correct form to express linkformat.sourcefullname with sections...
Could you help me?

Code:

Sub Update_link()
'
' Update_link Macro
'
Dim oStory As Range
Dim oField As Field
Dim section As section
Dim HdFt As HeaderFooter
Dim dlgSelectFile As FileDialog 'Filedialog Object
Dim thisFiled As Field
Dim selectedFile As Variant ' contain filepath of selected item
Dim newfile As Variant
Dim fieldCount As Integer, FldInx As Integer, k As Integer
Dim myRange As Range
'
'create FileDialog object as File picker Dialog boz
'
Set dlgSelectFile = Application.FileDialog(FileDialogType:=msoFileDialogFilePicker)
'
With dlgSelectFile
  ' 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
    For Each selectedFile In .SelectedItems
      newfile = selectedFile 'gets new filepath
    Next selectedFile
  Else 'user clicked cancel
  End If
End With
Set dlgSelectFile = Nothing
'
'update fields
'
With ActiveDocument
  For Each oStory In ActiveDocument.StoryRanges
    fieldCount = oStory.Fields.Count
    FldInx = 1
    While FldInx < fieldCount
      On Error Resume Next
      oStory.Fields(FldInx).LinkFormat.SourceFullName = newfile
      On Error GoTo 0
      FldInx = FldInx + 1
    Wend
  Next oStory
  '
  For Each section In ActiveDocument.Sections
    For Each HdFt In section.Headers
      Set myRange = HdFt.Range
      For Each aField In myRange.Fields
        aField.LinkFormat.SourceFullName = newfile
      Next aField
    Next HdFt
  Next section
End With
'
End Sub

macropod · #4 07-06-2015, 01:57 PM

Given that it's possible for the links to exist in something other than fields, try:

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
      Next
      For i = .ShapeRange.Count To 1 Step -1
      If Not .ShapeRange(i).LinkFormat Is Nothing Then .ShapeRange(i).LinkFormat.SourceFullName = vItem
      Next
      For i = .InlineShapes.Count To 1 Step -1
        If Not InlineShapes(i).LinkFormat Is Nothing Then InlineShapes(i).LinkFormat.SourceFullName = vItem
      Next
    End With
  Next
End With
'
Application.ScreenUpdating = True
MsgBox "Done!"
End Sub

PS: When posting code, please use the code tags, indicated by the # button on the posting menu.

Fed77 · #5 07-07-2015, 01:32 AM

Something is not working. The links are updated but they lose excel cell selection (every link shows the whole worksheet).

macropod · #6 07-07-2015, 02:21 AM

That suggests the links were themselves mal-formed. I've seen that behaviour before and thought MS might have fixed it by now. Are you changing the workbook names, or just their path? If it's the latter, the macro could be recoded to change just the path. Hopefully that would leave the rest of the field intact.

Fed77 · #7 07-07-2015, 03:27 AM

Sorry again and thank you very much for your help.

The new excel file has only different filename and path respect the origin one.

I'm quite confuse:

1) If I run last macro you posted, everything is working except header link

2) If I insert macro's text in an AutoOpen macro (to run it automatically when I open word), every link is updated (IN THE HEADER TOO) but it shows the entire worksheet!!!!

macropod · #8 07-07-2015, 03:55 AM

Quote:

Originally Posted by Fed77

I'm quite confuse:

1) If I run last macro you posted, everything is working except header link

2) If I insert macro's text in an AutoOpen macro (to run it automatically when I open word), every link is updated (IN THE HEADER TOO) but it shows the entire worksheet!!!!

There is no reason I'm aware of for either behaviour - whichever way you run the macro, all links should be updated with the same content as before. That said, try the following:

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

Fed77 · #9 07-08-2015, 12:53 AM

Great! Now everything is working! Thank you so much.

But If I copy macro's text in an AutoOpen macro, every link shows the whole origin worksheet!!!! It is really strange....

macropod · #10 07-08-2015, 02:24 AM

The only thing I can think of that could account for this difference in behaviour is that the document and/or the links its using have some form of corruption.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How can I save a Word Document as a PDF file with a merged field filename?	kp2009	Word VBA	5	08-27-2015 11:45 PM
How can I embed a word document so I don't lose the links therein	Dracven	Outlook	2	03-31-2014 04:02 AM
Using Quick Parts >> Ref >> Field (Third word changing font)	sonny49	Word	1	01-07-2014 10:01 AM
Embedded Links failing in word document	jim_jim	Word	0	02-05-2013 07:48 AM
Edits links to Excel file in PowerPoint	ziba	PowerPoint	4	01-09-2012 08:48 AM