Sub UpdatePath()
' Change these paths as needed
' Old link path
Const strOldPath = "\\\\svr-ford\\shared\\"
' New link path
Const strNewPath = "\\\\ford.com\\shared\\shared\\"
' Folder containing the documents (single \)
Const strFolder = "\\ford.com\shared\shared\"
' Variables
Dim strFile As String
Dim doc As Document
Dim f As Boolean
' Reduce screen flicker
Application.ScreenUpdating = False
' Suppress prompts
Application.DisplayAlerts = False
' Store update setting
f = Options.UpdateLinksAtOpen
' Temporarily stop updating links
Options.UpdateLinksAtOpen = False
' Loop through the documents in the folder
strFile = Dir(strFolder & "*.doc*")
Do While strFile <> ""
' Open the document
Set doc = Documents.Open(FileName:=strFolder & strFile, AddToRecentFiles:=False)
' Display field codes
ActiveWindow.View.ShowFieldCodes = True
' Update path
doc.Content.Find.Execute FindText:=strOldPath, ReplaceWith:=strNewPath, Replace:=wdReplaceAll
' Hide field codes
ActiveWindow.View.ShowFieldCodes = False
' Close and save the document
doc.Close SaveChanges:=True
' Get the next filename
strFile = Dir
Loop
' Restore original settings
Options.UpdateLinksAtOpen = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Hans---no surprise, it looks like the links updated. I am still validating since there so many Word documents. Again, thank you for all of your help and advice. Jim
Hans,
The links appear to updated correctly--but the links do not update now. So when I toggle to Alt-F9, all the paths appear to be correct. But they will not update, the field is still filled with the value from Excel during the last working iteration (i.e., prior to the drive path being changed).
I have gone into the Excel worksheet and changed variables, re-generate the documents, but when the document opens it never updates the linked fields. Is there something that I need to "turn back on" with the documents as didn't the macro suppress the update process as this was causing the hang in word, etc. during the find and replace to correct the linked patch? I am searching in Google, but can't seem to locate what needs to be reset so the "links" are operational again. Thank you. Jim
Also, to turn on automatic updates now:
Select File > Options.
Select Advanced.
Scroll down to the General section.
Tick the check box "Update automatic links at open".
Click OK.
Hans--thanks. I did turn the automatic updates on, but the links still don't update. So I thought I re-run the macro. Here is my code...but now it won't run at all. I created a Module 1 and copied the code below. When I press run/sub user form...nothing happens. When I debug, the Sub UpdatePath() highlight yellow and when I click to run, it disappears and nothing happens. It is almost like the macro module is now dead. I am really struggling. Sorry.
Sub UpdatePath()
' Change these paths as needed
' Old link path
Const strOldPath = "\\\\svr-kwgd12\\shared\\"
' New link path
Const strNewPath = "\\\\kwgd.com\\shared\\shared\\"
' Folder containing the documents (single \)
Const strFolder = "W:\_A_A_A_EstatePlanningDocs\JLL_Documents\Test\"
' Variables
Dim strFile As String
Dim doc As Document
Dim f As Boolean
' Reduce screen flicker
Application.ScreenUpdating = False
' Suppress prompts
Application.DisplayAlerts = False
' Store update setting
f = Options.UpdateLinksAtOpen
' Temporarily stop updating links
Options.UpdateLinksAtOpen = False
' Loop through the documents in the folder
strFile = Dir(strFolder & "*.doc*")
Do While strFile <> ""
' Open the document
Set doc = Documents.Open(FileName:=strFolder & strFile, AddToRecentFiles:=False)
' Display field codes
ActiveWindow.View.ShowFieldCodes = True
' Update path
doc.Content.Find.Execute FindText:=strOldPath, ReplaceWith:=strNewPath, Replace:=wdReplaceAll
' Hide field codes
ActiveWindow.View.ShowFieldCodes = False
' Close and save the document
doc.Close SaveChanges:=True
' Get the next filename
strFile = Dir
Loop
' Restore original settings
Options.UpdateLinksAtOpen = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Click anywhere in the macro, then press F8 repeatedly to step through the code. Does that provide a clue, for example that the Do While strFile <> "" loop immediately exits?
Hans,
My first thought was to re-run the macro with code change on the original documents since I had preserved them before running the macro initially. That didn't work and I am following your advice to trouble shoot now. In the interim, I opened one of the word docs and manually added a "new" linked field. Please see screen shot below.
The link in smaller font is the one I added manually and it works. But I noticed the extra " " in the linked path that is not in the larger font one---the larger font one is an original link with the path updated by the macro. Even when I go in and manually type in the " " to match the link I just created, the original link still does not work. I was hoping that by adding the " " the original link would function and I could have you help me get a macro to run to add the " " for all the links---but that appears to be a waste of time since my manually typing the " " to match the newly created working link does not make the original link operational.
It sure looks like my only choice is to open every document and manually replace the links. This will take me weeks to do....any ideas?
Thanks Jim
You do not have the required permissions to view the files attached to this post.
Hans---I just went back and checked the "original" documents with the old path and none of the links in these documents had the extra " ". The person that used these documents told me the links were working fine until the drive had to be replaced and the path changed. Now I am really puzzled why the new created link has the extra " " in it. Please see the screenshot. Jim
You do not have the required permissions to view the files attached to this post.
Last edited by JimmyC on 08 Nov 2019, 12:22, edited 1 time in total.
Hans--thanks! I thought maybe it had something to do with me creating a new field in the existing documents but when I open a blank document and insert a link to the same ExXcel file it has the extra " " in the link path too. The only "change" outside of the hard drive mapping change would be MS patches. Also, the original drive was a physical drive in a server and when this drive died, the tech guys "replaced" the drive by mapping the new drive to a virtual Dell cluster so that in theory this single drive will never need replaced or re-mapped again. Jim
Sub UpdatePath()
' Change these paths as needed
' Old link path
Const strOldPath = "\\\\svr-kwgd12\\shared\\"
' New link path
Const strNewPath = "\\\\kwgd.com\\shared\\shared\\"
' Folder containing the documents (single \)
Const strFolder = "W:\_A_A_A_EstatePlanningDocs\JLL_Documents\Test\"
' Variables
Dim strFile As String
Dim doc As Document
' Reduce screen flicker
Application.ScreenUpdating = False
' Suppress prompts
Application.DisplayAlerts = False
' Stop updating links
Options.UpdateLinksAtOpen = False
' Loop through the documents in the folder
strFile = Dir(strFolder & "*.doc*")
Do While strFile <> ""
' Open the document
Set doc = Documents.Open(FileName:=strFolder & strFile, AddToRecentFiles:=False)
' Display field codes
ActiveWindow.View.ShowFieldCodes = True
' Update path
Selection.HomeKey Unit:=wdStory
With Selection.Find
.Wrap = wdFindStop
Do While .Execute(FindText:=strOldPath)
Selection.Text = strNewPath
Selection.InsertBefore Text:=Chr(34)
Selection.Collapse Direction:=wdCollapseEnd
.Execute FindText:=".xlsm"
Selection.InsertAfter Text:=Chr(34)
Selection.Collapse Direction:=wdCollapseEnd
Selection.Move Unit:=wdCharacter, Count:=1
Selection.InsertBefore Text:=Chr(34)
Selection.MoveEndUntil Cset:=" "
Selection.InsertAfter Text:=Chr(34)
Selection.Collapse Direction:=wdCollapseEnd
Loop
End With
' Hide field codes
ActiveWindow.View.ShowFieldCodes = False
' Close and save the document
doc.Close SaveChanges:=True
' Get the next filename
strFile = Dir
Loop
' Restore original settings
Options.UpdateLinksAtOpen = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Yes, the path now matches the one I get when I create a new link in a newly created word document...Is there somewhere else, perhaps hidden, that also keeps the path? Thanks for all of your help and patience.
Hans---it is almost like the visible path when you toggle Alt-F9 cannot be changed once its created. When I created a new link, it works. But if I manually change the referenced cell in the path after creation of my new link, its is ignored--the link continues to return the initial value from the original cell location when the link was created. So my modification to change the cell reference is totally ignored. The "real" link must be saved somewhere else besides the visible path that stores the link, yes? Any ideas? Thanks. Jim