Find & Replace text in Field Code across multiple documents

RPM7 · #1 05-11-2017, 05:03 PM

Does anyone know if its possible to create a macro to find and replace a portion of field code across multiple documents?

The fastest way I can do it is:

Press (Alt+F9)
Press (Ctrl+H)
Insert old text in the 'Find' field
Insert New text in the 'Replace' field
Press (Alt+F9)
Repeat process for remaining documents

FYI: the field code being replaced is a link to an excel spreadsheet.
I wanted to use relative links, but apparently that's not possible

We duplicate a large bunch of documents for every project at work.
Some generic info has to be replaced in each document as per the project & this is the fastest way I can think of doing it.

gmayor · #2 05-11-2017, 09:05 PM

I take it that we can assume this is a hyperlink field, in which case the following macro will work (replace the paths etc as appropriate) when used as a custom process with
http://www.gmayor.com/document_batch_processes.htm which will perform the file/folder handling.

Code:

Function ReplaceLink(oDoc As Document)
    On Error GoTo err_Handler
    Dim oRng As Range
    Dim hLink As Hyperlink
    Set oDoc = ActiveDocument
    For Each hLink In oDoc.Hyperlinks
        If hLink.Address = "C:\Path\TargetWorkbook.xlsx" Then
            With hLink
                .Address = "C:\Path\Replacement.xlsx"
                .TextToDisplay = "C:\Path\Replacement.xlsx"
                .ScreenTip = "Click to open workbook"
                .Target = "C:\Path\Replacement.xlsx"
            End With
        End If
    Next hLink
    ReplaceLink = True
lbl_Exit:
    Exit Function
err_Handler:
    ReplaceLink = False
    Err.Clear
    Resume lbl_Exit
End Function

macropod · #3 05-11-2017, 09:50 PM

Quote:

Originally Posted by RPM7

FYI: the field code being replaced is a link to an excel spreadsheet.
I wanted to use relative links, but apparently that's not possible

If it's a LINK field then, no, those don't support 'relative' implementations. For all other kinds of field, though, check out the relative path field code implementation in:
https://www.msofficeforums.com/word/...nal-files.html
On the same page you'll find an attachment containing a macro that can be used to achieve an effect comparable to using relative paths for LINK fields as well.

Quote:

Originally Posted by RPM7

We duplicate a large bunch of documents for every project at work.
Some generic info has to be replaced in each document as per the project & this is the fastest way I can think of doing it.

If that's a once-off exercise for each document, that's probably not the optimum approach. A better approach might be to use a macro in the document template that automatically populates any documents based on that template with the data from the relevant Excel file the first time the file is saved to the appropriate folder (which the macro might enforce as soon as the document is created). After all, if the Excel data should remain static once the document is populated, you probably no longer need the links.

RPM7 · #4 05-11-2017, 10:52 PM

Thanks for the replies guys.

@Macropod, I actually used your post on windowssecrets as a reference. It has been very helpful; however, I don't have any VBA experience, so I'm somewhat stumbling along with this one.
As you say, the relative links work fine for INCLUDEPICTURE, which I'm using for updating our clients logo on each document. Its just the excel links that are causing an issue for me.

@gmayor, I've been playing around with your add-in.
How do I integrate the macro you've referred to?

Essentially, I'd like to be able to batch process all the documents to replace the project reference with a new one.

Code:

LINK Excel.Sheet.12 "\\\\C:\Users\Public\Documents\\Project 1\\Production\\Reference material\\Project Info.xlsx" Data!R2C3 \a \t  \* MERGEFORMAT }

LINK Excel.Sheet.12 "\\\\C:\Users\Public\Documents\\Project 2\\Production\\Reference material\\Project Info.xlsx" Data!R2C3 \a \t  \* MERGEFORMAT }

Thanks

macropod · #5 05-11-2017, 11:30 PM

As indicated in his post, Graham's macro assumed the use of hyperlinks, not LINK fields.

When you create a new project, for which the path appears to be based on
C:\Users\Public\Documents\Project 1\Production\Reference material\
with 'Project 1' being variable:
1. In which folder are the new Word files located?
2. Are all the Word files based on the same template?
3. Are the links meant to disappear once they've been updated to point to the correct folder and the current data have been captured (i.e. should their content ever change)?

RPM7 · #6 05-12-2017, 12:08 AM

Thanks macropod

1. The word documents are located in the folder "Production", whilst the spreadsheet is in "Reference material".
2. Yes, the word files are based on the same template.
3. It would be ideal if the links could remain, in case some information in the spreadsheet were to change.

gmayor · #7 05-12-2017, 12:58 AM

Paul is correct. I had assumed hyperlink fields, but the general premise will work for Link fields e.g.

Code:

Function ReplaceLink(oDoc As Document)
    On Error GoTo err_Handler
    Dim oRng As Range
    Dim oFld As Field
    ActiveWindow.View.ShowFieldCodes = True
    For Each oFld In oDoc.Fields
        If oFld.Type = wdFieldLink Then
            If InStr(1, oFld.code, "Project 1") > 0 Then
                Set oRng = oFld.code
                oRng.Text = Replace(oRng.Text, "Project 1", "Project 2")
                oFld.code = oRng
                oFld.Update
            End If
        End If
    Next oFld
    ActiveWindow.View.ShowFieldCodes = False
    ReplaceLink = True
lbl_Exit:
    Exit Function
err_Handler:
    ReplaceLink = False
    Err.Clear
    Resume lbl_Exit
End Function

It works with links I have created and changes the field using your data so it should work for you. Call it from the add-in as a Custom Process using the name ReplaceLink in the dialog. Test it on one document open in Word before batch processing

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Macro to find text and replace with form field containing that text	iiiiifffff	Word VBA	16	06-04-2016 01:47 AM
Macro to find coloured text and replace with form-field/formtext containing that text	tarktran	Word VBA	1	11-26-2014 08:12 AM
Macro for find/replace (including headers and footers) for multiple documents	jpb103	Word VBA	2	05-16-2014 04:59 AM
How do I find/replace the same word in multiple documents?	Ineedhelp!	Word	3	03-04-2014 03:50 PM
Find and replace multiple documents change style	BaPW	Word	0	08-14-2011 11:12 AM