Word 2010--Change linked path "globally"

[JimmyC]

I have at least 60 word documents that contain linked fields to an Excel worksheet. Everything was working fine until the network gurus changed the path name of the linked drive as we needed to increase the hard drive size.

I know that I can press Alt-f9 and display the linked path and then do a find (i.e., \\\\svr-ford\\shared\\.....) and replace with (i.e., \\\\ford.com\\shared\\shared) in each individual word document and then re-save the document---but is there anyway via macro, etc. to globally change the path as illustrated for the 60+ word files in the folder without opening each document manually, performing the find and replace and then finally re-saving the document?

I fear the answer is "no" but it never hurts to ask the experts. Thanks for any and all advice.
JimC

[HansV]

If all documents are stored in a single folder, you could use a macro to open each document in turn, change the path, then save and close it...

[JimmyC]

Hans...thanks for the feedback.

I have no idea how to even start such a macro. All the word documents are located in the same folder and all need the same "find and replace" per my first post. The documents do not have the field link visible when first opened as this would confuse the typist. So I need to somehow send the command Alt-F9 to view the fields, then execute the find and replace, then send another command Alt-F9 to make the link again invisible and then save the file with the same file name. Some of the word documents (probably less than 10 documents) have more 75 linked fields with most documents having between 35-45 linked fields. The Excel file that hosts all the information used in the linked fields in the Word documents is also saved in the same folder.

Thank you for sharing your time and talent to help others like me,
JimC

[HansV]

Here is a macro. Before running it, change the three constants at the beginning of the code.

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
    ' Reduce screen flicker
    Application.ScreenUpdating = 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
    Application.ScreenUpdating = True
End Sub

You may want to test it on a copy of the folder first.

[JimmyC]

Hans---I am really such a novice at VBA---I want to confirm your code is meant to run from an "empty" word document or will I insert a module in the Excel workbook that hosts the "data" and run the code from there? Jim

[Charles Kenyon]

See also the Add-In from Greg Maxey and Graham Mayor for batch processing.
https://gregmaxey.com/word_tip_pages/pr ... addin.html
http://www.gmayor.com/document_batch_processes.htm

Here are instructions on how to use macros posted in a forum:
http://www.gmayor.com/installing_macro.htm
http://gregmaxey.com/word_tip_pages/ins ... acros.html

[HansV]

Hi Jim,

The code is to be copied into and run from a Word document.

[JimmyC]

Hans--thank you. I will work on this task later today once most of our typists have left for the day. I always like to learn--so I may be back with a question. It is interesting in all my "Google" searching I could not find a way to "show" the path fields and it is actually just a straight forward with ShowFieldCodes--which I didn't know existed. Again, thank you. Jim

[JimmyC]

Charles---thank you for the additional references. I will book mark these for future reference. Jim

[Charles Kenyon]

You can find some great resources, white papers by Paul Edstein, on fields in the sticky threads in this forum:
http://www.msofficeforums.com/word/

Here is my page on fields, which is most valuable for its links.
http://www.addbalance.com/usersguide/fields.htm

[HansV]

When I want to know how to do something in code, I often record a short macro, then look at the recorded code, and use the F1 key to learn more.

For example:
- Click the 'Record macro' button in the status bar at the bottom of the window.
- Specify the active document to store the macro in.
- Click OK.
- Press Alt+F9.
- Click the 'Record macro' button again (it is now a 'Stop Recording' button).
- Press Alt+F11 to activate the Visual Basic Editor.
- Open the newly created module (it is usually named NewMacros).
- This is what I got:

Code: Select all

Sub Macro1()
'
' Macro1 Macro
'
'
    ActiveWindow.View.ShowFieldCodes = Not ActiveWindow.View.ShowFieldCodes
End Sub

- Click anywhere in the word ShowFieldCodes and press F1.
- This takes you to Microsoft's help page on the ShowFieldCodes property, with several code examples:

S2904.png

- This is often (but not always) enough to know how to modify the code, if needed.

[JimmyC]

Hans,
Everything I read about the macro recording being "worthless" particularly for Excel---this is something I would have never thought to do. I do realize not all advice on the internet is sound, but the macro recorder seems to get "bashed" allot on various sites, etc. Thank you. Jim

[HansV]

The macro recorder certainly is less than optimal, and for some features it is worthless, but it can still provide useful insight into the code you need.

[JimmyC]

Hans---thanks for all your help and guidance. I wonder how many individuals, like me, you have helped over the years. I can't answer my own question---but its a BIG number. I will let you know how I fare as I need to wait a couple more hours before I can run the macro....Jim

[JimmyC]

Hans
I am sorry but I am stuck....I am getting a "run-time error '52" Bad file name or number on the line: strFile = Dir(strFolder & "*.doc*") I decided to "test" the macro by copying the documents to a spare hard drive on my PC...It is drive "W" and I used the following path: "\\3TB_Removable\A_A_A_EstatePlanningDocs\" I am "guessing" that my path is not correct but I do not know how to correct it. I have attached a screen shot too. Sorry, to be a bother. Thank you
Jim

[HansV]

Which folder on the W: drive contains the documents?

[JimmyC]

I did figure out how to get the correct path. When the macro runs, when a word file is opened, I am prompted whether I want to update the fields. How should I answer this question? Thanks

[HansV]

Try the following:
- Below the line Application.ScreenUpdating = False, insert

Code: Select all

    Application.DisplayAlerts = False

and above the line Application.ScreenUpdating = True, insert

Code: Select all

    Application.DisplayAlerts = True

Does that suppress the prompt?

[JimmyC]

Hans---I will try this and get back to you. It maybe Monday as I am experiencing computer problems at the moment. Jim

[JimmyC]

Hans,
I added the two lines of code so I don't get the prompt.

The macro still hangs...at the bottom of the screen this message flashes "Contacting\\and here is the old server path to the drive..Press ESC to cancel. But even if I press ESC, word is frozen and then crashes.

I am only "testing" one document in the folder and have substituted other word documents thinking the one I picked was somehow corrupted. I get the same behavior regardless of the single word document I place into the test folder. It is almost like the word file is immediately trying to link the field to the old server prior to running the find and replace commands.

Is there a way to halt word from trying to link to the old server, prior to changing to the new path?

Thanks for your patience. Jim

UPDATE: I get the same flashing message when I open the word document manually. If you wait long enough Word must run through the cycle and then I can do the find and replace manually. But you cannot do this until whatever cycle is running finishes.