Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-03-2015, 02:02 AM
Fed77 Fed77 is offline Changing Field Links in a Word Document from one excel file to another Windows 7 64bit Changing Field Links in a Word Document from one excel file to another Office 2010 64bit
Novice
Changing Field Links in a Word Document from one excel file to another
 
Join Date: Jul 2015
Location: Venice
Posts: 5
Fed77 is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 07-03-2015, 02:57 AM
macropod's Avatar
macropod macropod is offline Changing Field Links in a Word Document from one excel file to another Windows 7 64bit Changing Field Links in a Word Document from one excel file to another Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #3  
Old 07-06-2015, 07:23 AM
Fed77 Fed77 is offline Changing Field Links in a Word Document from one excel file to another Windows 7 64bit Changing Field Links in a Word Document from one excel file to another Office 2010 64bit
Novice
Changing Field Links in a Word Document from one excel file to another
 
Join Date: Jul 2015
Location: Venice
Posts: 5
Fed77 is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 07-06-2015, 01:57 PM
macropod's Avatar
macropod macropod is offline Changing Field Links in a Word Document from one excel file to another Windows 7 64bit Changing Field Links in a Word Document from one excel file to another Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 07-07-2015, 01:32 AM
Fed77 Fed77 is offline Changing Field Links in a Word Document from one excel file to another Windows 7 64bit Changing Field Links in a Word Document from one excel file to another Office 2010 64bit
Novice
Changing Field Links in a Word Document from one excel file to another
 
Join Date: Jul 2015
Location: Venice
Posts: 5
Fed77 is on a distinguished road
Default

Something is not working. The links are updated but they lose excel cell selection (every link shows the whole worksheet).
Reply With Quote
  #6  
Old 07-07-2015, 02:21 AM
macropod's Avatar
macropod macropod is offline Changing Field Links in a Word Document from one excel file to another Windows 7 64bit Changing Field Links in a Word Document from one excel file to another Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #7  
Old 07-07-2015, 03:27 AM
Fed77 Fed77 is offline Changing Field Links in a Word Document from one excel file to another Windows 7 64bit Changing Field Links in a Word Document from one excel file to another Office 2010 64bit
Novice
Changing Field Links in a Word Document from one excel file to another
 
Join Date: Jul 2015
Location: Venice
Posts: 5
Fed77 is on a distinguished road
Default

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!!!!
Reply With Quote
  #8  
Old 07-07-2015, 03:55 AM
macropod's Avatar
macropod macropod is offline Changing Field Links in a Word Document from one excel file to another Windows 7 64bit Changing Field Links in a Word Document from one excel file to another Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by Fed77 View Post
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
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #9  
Old 07-08-2015, 12:53 AM
Fed77 Fed77 is offline Changing Field Links in a Word Document from one excel file to another Windows 7 64bit Changing Field Links in a Word Document from one excel file to another Office 2010 64bit
Novice
Changing Field Links in a Word Document from one excel file to another
 
Join Date: Jul 2015
Location: Venice
Posts: 5
Fed77 is on a distinguished road
Default

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....
Reply With Quote
  #10  
Old 07-08-2015, 02:24 AM
macropod's Avatar
macropod macropod is offline Changing Field Links in a Word Document from one excel file to another Windows 7 64bit Changing Field Links in a Word Document from one excel file to another Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
Reply

Tags
header and footer, link, update

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Changing Field Links in a Word Document from one excel file to another 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

Other Forums: Access Forums

All times are GMT -7. The time now is 08:44 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft