#1
|
|||
|
|||
Changing Field Links in a Word Document from one excel file to another
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 Last edited by macropod; 07-06-2015 at 01:41 PM. Reason: Added code tags & formatting |
#2
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
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 Last edited by macropod; 07-06-2015 at 01:43 PM. Reason: Added code tags & formatting |
#4
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Something is not working. The links are updated but they lose excel cell selection (every link shows the whole worksheet).
|
#6
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
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!!!! |
#8
|
||||
|
||||
Quote:
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#9
|
|||
|
|||
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.... |
#10
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Tags |
header and footer, link, update |
Thread Tools | |
Display Modes | |
|
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 |