Word 2010--Change linked path "globally"

[HansV]

The path is displayed in the field code. But you can check one thing:
- Open one of the documents.
- Select File > Info on the left hand side.
- Click Properties > Advanced Properties on the right hand side.
- Is there anything in the 'Hyperlink base:' box?

S2923.png

[JimmyC]

Hans, here is the screen shot:

CaptureProperties.JPG

[HansV]

OK, it's blank. I'm very sorry, but I am out of ideas.

[HansV]

Except: can't the IT department rename the server?

[JimmyC]

Hans, Thanks again for your patience and time to help me.

As you probably surmised, I was turned down by the IT department to maintain the current path. My "consolation" prize is the "new" drive is in a Dell virtual hard drive cluster so it will never "independently" die again and, in theory, the path will never change. Even if this "hard drive" needs to be larger someday, it is merely a change in the Dell virtual hard drive cluster and this will never impact the "new" current mapping. The downside is there are days and days of work to fix the path.

I just can't find any information on this, and yes I am a terrible google searcher, but I cannot be the first person in the world to have this issue. My other significant concern is my employer is transitioning from Win 7 / 2010 MS Office to Win 10 / 2019 MS office next year and I don't want to do this terrible exercise of changing the paths again because of a change in Word 2019.

Thanks again, Jim

[HansV]

I don't think moving to Windows 10 and Office 2019 would make a difference.

[JimmyC]

Hans--Thanks. These documents need to fixed ASAP, but I am soooooooooo concerned that I will need to do it again sometime next year with Win10/Office 2019 upgrade. I guess I proceed with the "hope" Win10/office 2019 is a non-event as far as the links are concerned.

I have continued to experiment with the links....Once I create a new working link, I can manually change the path and it does not have any impact on the original linked value. Even if I change the Excel cell reference to another cell in the same worksheet, the link continues to retrieve the cell value used in the original creation of the link. I have never seen anything like this before as the path displayed with the Alt-F9 toggle can be edited--or appears to be edited---but it has absolutely no impact on the linked value created from the original link. I can even change the workbook file name to a non-existent Excel file name and the link still returns the value from the workbook in the original link. Surely the "real" link is hidden somewhere else that end users cannot modify. Jim

[HansV]

I assume that you have selected one or more of the links and press F9 to update them?

[JimmyC]

Hans--no I was not pressing F9 after the update....back to the experiment. Thanks. Jim

[JimmyC]

Hans---Can I ask a "stupid" question....would I need to press F9 for each link that the macro updates for it to function?

The reason for my question is if I manually type in the missing " " in both places, press Alt-F9 to close the link and then press F9---the link is again functional.

Further, if I merely delete one of the " in the link after your today's provided macro has run and merely retype the " then press Alt-F9 and then F9 by itself---the link is again operational. In fact, I can change any value in the link and the re-type the deleted character, press F9 and the link is functional again. Something with this manual cycle makes the link operational again. I have just done this cycle for 20 linked fields in a document---merely delete the first ", retype it, press Alt-F9, then F9 and the link is repaired. Any idea how this could be done in VBA?

Jim

[HansV]

You can press Ctrl+A to select the entire document, then press F9 to update all fields.
The following version of the macro does that before saving the document:

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
        ' Update all fields
        doc.Fields.Update
        ' 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

[JimmyC]

Hans---Your generosity and willingness to help is beyond my comprehension. I will try this and report back...It maybe Monday as there is system maintenance scheduled for tonight and most of the weekend so I will not have access to the network drive. Enjoy your weekend. Jim

[JimmyC]

Hans,
The last code does not add the " " like the previous code--or does it but when the F9 is "pressed" the " " are not saved?

Another oddity....if you make a change to a field in the Excel file---the link is no longer dynamic and updates--but if I highlight the entire Word document and press F9----the link then "refreshes" to capture the field change in the Excel file. When I create a Word doc from scratch--link a field to the same Excel worksheet---any change I make the field is Excel is automatically reflected in the Word document--but this behavior no longer exists in the documents we have been trying to modify the link. This is really weird.

You must have patience beyond measure to work with this stuff all day.
Jim

[HansV]

The code does add the " ", and they remain after updating the fields:

S2925.png

But the links obviously don't work, since your network is not available to me.

S2926.png

So I can't test it further than that.

[JimmyC]

OK...When I look at the link in the document after the macro, there is no " "...I will do more troubleshooting.

[HansV]

The \a switch in the field code should make the field update automatically. I have no idea why that doesn't work.