Word 2010--Change linked path "globally"

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Word 2010--Change linked path "globally"

Post by HansV »

Does this work better (change the paths as needed)?

Code: Select all

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
Best wishes,
Hans

JimmyC
3StarLounger
Posts: 382
Joined: 08 Feb 2010, 16:08

Re: Word 2010--Change linked path "globally"

Post by JimmyC »

Hans---thank you! I will try the new code on Tuesday as tomorrow is October month-end closing. I will let you know. Thank you. Jim

JimmyC
3StarLounger
Posts: 382
Joined: 08 Feb 2010, 16:08

Re: Word 2010--Change linked path "globally"

Post by JimmyC »

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

JimmyC
3StarLounger
Posts: 382
Joined: 08 Feb 2010, 16:08

Re: Word 2010--Change linked path "globally"

Post by JimmyC »

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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Word 2010--Change linked path "globally"

Post by HansV »

Sorry, change the line near the end of the macro (not the one near the beginning!)

Code: Select all

    Options.UpdateLinksAtOpen = False
to

Code: Select all

    Options.UpdateLinksAtOpen = True
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Word 2010--Change linked path "globally"

Post by HansV »

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.
Best wishes,
Hans

JimmyC
3StarLounger
Posts: 382
Joined: 08 Feb 2010, 16:08

Re: Word 2010--Change linked path "globally"

Post by JimmyC »

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.

Code: Select all

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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Word 2010--Change linked path "globally"

Post by HansV »

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?
Best wishes,
Hans

JimmyC
3StarLounger
Posts: 382
Joined: 08 Feb 2010, 16:08

Re: Word 2010--Change linked path "globally"

Post by JimmyC »

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.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Word 2010--Change linked path "globally"

Post by HansV »

What did the (working) field code look like before you ran the macro? Did it contain " "?
Best wishes,
Hans

JimmyC
3StarLounger
Posts: 382
Joined: 08 Feb 2010, 16:08

Re: Word 2010--Change linked path "globally"

Post by JimmyC »

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.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Word 2010--Change linked path "globally"

Post by HansV »

You answered my question instantly :grin:

Are all the links to .xlsm workbooks or are there also links to other types such as .xls or .xlsx?
Best wishes,
Hans

JimmyC
3StarLounger
Posts: 382
Joined: 08 Feb 2010, 16:08

Re: Word 2010--Change linked path "globally"

Post by JimmyC »

Hans--all the links are to a single excel file...xlsm. Jim

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Word 2010--Change linked path "globally"

Post by HansV »

I'll try to find something, but the fact that the link originally worked without the quotes and now it doesn't really complicated the problem!
Best wishes,
Hans

JimmyC
3StarLounger
Posts: 382
Joined: 08 Feb 2010, 16:08

Re: Word 2010--Change linked path "globally"

Post by JimmyC »

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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Word 2010--Change linked path "globally"

Post by HansV »

See if this works:

Code: Select all

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
Best wishes,
Hans

JimmyC
3StarLounger
Posts: 382
Joined: 08 Feb 2010, 16:08

Re: Word 2010--Change linked path "globally"

Post by JimmyC »

Hans,
Thank you for your patience....I actually think I going crazy!!!

Here is a screen shot of the path after running the latest code you provided:
New_Path_Capture.JPG

Here is a screen shot of the result:
New_Path_Capture.JPG

The cell referenced has data so why do I now get an error message? I am so lost---thank you so much for your help! Jim
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Word 2010--Change linked path "globally"

Post by HansV »

It looks like the "new" link in one of your previous reply to me, so I have no idea why it doesn't work...
Best wishes,
Hans

JimmyC
3StarLounger
Posts: 382
Joined: 08 Feb 2010, 16:08

Re: Word 2010--Change linked path "globally"

Post by JimmyC »

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.

JimmyC
3StarLounger
Posts: 382
Joined: 08 Feb 2010, 16:08

Re: Word 2010--Change linked path "globally"

Post by JimmyC »

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