VBA to find all occurrences of a string and replace them with a Ref field

I have multiple occurrences of the string "PROJECT_NAME" in my document text and headers.  I need to replace these with Ref fields to point to the bookmark named "BM_Project_Name".  The solution needs to be done via VBA.  I am working in Excel, but can adapt your Word VBA, so no worries.

WC
LVL 18
Cory VandenbergSenior Risk ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Cory VandenbergSenior Risk ManagerAuthor Commented:
Ok, so the solution to the related question does what I asked, but does not solve my problem.

I'm trying to put a field in the document that when changed by the user will be used as a reference for multiple other fields that can then update with the new value.

Putting a bookmark doesn't help me, as once the user edits the enclosed value, the bookmark disappears.

So before you answer the question originally asked, I guess I should ask, is what I want to do even possible?  Have variable text in a document that is used as a reference for other locations in the document?

WC
0
Cory VandenbergSenior Risk ManagerAuthor Commented:
Would perhaps using a form textbox be the way to go?

Make the textbox appear invisible, but then the text inside of it would be able to be referenced, correct?

WC
0
Joanne M. OrzechManager, Document Services CenterCommented:
I guess I still don't understand how you're going to get then Project Name.... because you didn't want to use an Input Box.... but however you're going to get it, you can still use the macro I gave you that does a search and replace .... you don't have to go through all this....
0
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Cory VandenbergSenior Risk ManagerAuthor Commented:
Hm, same problem, when the user alters the form textbox, the bookmark disappears.  Ugh!
0
Cory VandenbergSenior Risk ManagerAuthor Commented:
J,

I need to replace the string constants "PROJECT_NAME" with a Ref field so that after the document is created, if the user changes the value in the field being referenced, the Ref fields all update.

If there is no way to set up a range in the document that I can always reference using Ref fields, then this is all just a waste of time.

Using bookmarks won't do, because if the user selects the entire text of the bookmark and starts typing their new project name in, the bookmark is deleted, which would make all the Ref fields invalid.  I need a way that the user can update the project name directly in the document (no input boxes), and then all the Ref fields, or whatever other kind of field I need, will be able to see this new value and update accordingly with F9.

Sorry if I haven't been the clearest on this so far.

If there is no way to easily do this, I'll just have to tell users they will have to manually replace all occurrences, or rerun the Report Writer with the proper Project and/or Segment Name.  I was just trying to avoid that.

WC
0
Cory VandenbergSenior Risk ManagerAuthor Commented:
J,

the macro you provided in the related question.

I don't want to replace "PROJECT NAME" with the value of the project name (which I have stored in code).  I want to replace it with a field that will reference a location in the Document that can be edited after the document is created, so that if that location's text changes, all the fields referencing it will change with an Update Fields (F9)

I think that is about as clear as I can make this problem.

Thanks for the help so far.

WC
0
Eric FletcherCommented:
You can use Find and Replace to replace every instance of a pattern with a field code -- and if you need that to be in VBA, you could record it and edit as needed.

1. Create an instance of the field you want, select it, and copy it so it is in your clipboard.
2. Open the Find and Replace dialog:
    Find box: PROJECT_NAME
    Replace with box: ^c (this gets interpreted as the clipboard contents)
    Replace All

Now you'll have that field in each instance.
0
Cory VandenbergSenior Risk ManagerAuthor Commented:
Ok, so let's say I could even do that, which I'm not sure is feasible, as 1) the recorder in Word is horrible, especially compared to Excel.  Often times after turning on the Word macro recorder, I lose tons of functionality (such as not being able to right click or select anything in the document).

My question is this:  is it possible to have a field in a document that can be edited by the user, which can then be referenced by these Ref fields I want to insert, so that after the user-edited field is updated the Ref fields can be updated to show the new value?

If that isn't even possible, I have no need to replace with fields.  I'm currently replacing with hard coded strings, and telling user to manually do a find/replace, or just rerun the app with the correct names.

Starting to think this is more trouble than it's worth.

WC
0
Eric FletcherCommented:
As mentioned above, having fields based on something a user can edit is risky because they can delete it accidentally (your comment re bookmarks). You could prompt them, but that is a field. You could use a document variable where the info is saved in the Document Properties.

Depending on where the variable is, a field will do what you want.

Recording is normally pretty easy: are you using Word 2007? (If so, it isn't as easy IMHO!) If you can identify where the variable should be, I can show you some code to do it.
0
Cory VandenbergSenior Risk ManagerAuthor Commented:
No, 2003 here at work.

Ya, the point was to reduce the amount of manual work the user has to do, but I think it's going to be more pain than it's worth to do this.

I was hoping you could define a region, field, or something (like naming a cell in Excel as a named range) then when the value of that range changed, the refs would be able to detect that upon an update fields.

Seeing that bookmarks are no good for editing, as they are deleted if the user isn't careful, and form inputboxes are equally terrible, I'm just going to forego this and tell users they need to rerun the app with the appropriate text for their project and segment names, or manually find/replace them in Word.

Thanks for the input.

Have a good evening,

WC
0
Eric FletcherCommented:
Sure, no problem. Unlike Excel, where a cell is always there even if a user deleted the content, Word doesn't have any fixed regions -- at least not within the editable area.

FYI, the method I'd use -- and this is NOT necessarily ideal for users -- would be to set the variable in one of the Document Property fields -- say Subject. Then, in the document you can set a field as { Subject } to have it display the content. The user won't have accidental access to it, and the variable gets updated automatically if you change it in the dialog.

I use this for version control; I have a button that pops up the Doc Props dialog for easy access, and put the field in a page footer. You can also define custom properties with this.
0
GrahamSkanRetiredCommented:
This a Word macro to replace all the particular text with REF fields
Private Sub CreateRefFields()
Dim rngStoryType As Range
Dim rngCurrentStory As Range
Dim rngFind As Range
' Go through all story ranges in the document, including shapes,
' headers & footers.
For Each rngStoryType In ActiveDocument.StoryRanges
    Set rngCurrentStory = rngStoryType 'set rngCurrentStory to first range in story
    Do
        Set rngFind = rngCurrentStory
        With rngFind.Find
            .Text = "PROJECT_NAME"
            Do While .Execute
                ActiveDocument.Fields.Add rngFind, wdFieldRef, "BM_Project_Name"
                rngFind.Collapse wdCollapseEnd
                rngFind.End = rngCurrentStory.End
            Loop
        End With
        rngCurrentStory.Fields.Update
        Set rngCurrentStory = rngCurrentStory.NextStoryRange
    Loop Until rngCurrentStory Is Nothing
Next rngStoryType
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cory VandenbergSenior Risk ManagerAuthor Commented:
I have changed the accepted solution so that anyone finding this thread in the future will know which solution to use.  I am still not going to use this as I don't know a good way to have an editable field as the reference these Ref fields will look to for their value.
0
Cory VandenbergSenior Risk ManagerAuthor Commented:
Very nice solution, Graham.  Too bad I'm not going to end up using it though, but a very cool thing to know how to do.

WC
0
Cory VandenbergSenior Risk ManagerAuthor Commented:
Hilarious,

I Googled "word replace all occurences of a string with a ref field" and my question was the top hit.

I am actually going to use this code now Graham to replace constant string values that were conditionally put into my document.  Glad I asked this question after all, because as I said, it's a very useful solution.

Thanks again,
WC
0
GrahamSkanRetiredCommented:
It's always nice to see that your own question is a Google hit.

I have been into genealogy for a while, and Google used to return only a few illustrious forbears for my surname. Now I get thousands which bury any relevant hits.  Embarrassingly, many are for myself or some of my macros- "Hoist with my own petard".|
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Word

From novice to tech pro — start learning today.