?
Solved

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

Posted on 2009-07-08
16
Medium Priority
?
1,905 Views
Last Modified: 2012-05-07
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
0
Comment
Question by:Cory Vandenberg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 3
  • 2
  • +1
16 Comments
 
LVL 18

Author Comment

by:Cory Vandenberg
ID: 24807461
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
 
LVL 18

Author Comment

by:Cory Vandenberg
ID: 24807590
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
 
LVL 37

Expert Comment

by:Joanne M. Orzech
ID: 24807732
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
Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

 
LVL 18

Author Comment

by:Cory Vandenberg
ID: 24807811
Hm, same problem, when the user alters the form textbox, the bookmark disappears.  Ugh!
0
 
LVL 18

Author Comment

by:Cory Vandenberg
ID: 24807897
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
 
LVL 18

Author Comment

by:Cory Vandenberg
ID: 24807953
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
 
LVL 21

Expert Comment

by:Eric Fletcher
ID: 24808718
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
 
LVL 18

Author Comment

by:Cory Vandenberg
ID: 24809336
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
 
LVL 21

Expert Comment

by:Eric Fletcher
ID: 24809456
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
 
LVL 18

Author Comment

by:Cory Vandenberg
ID: 24809509
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
 
LVL 21

Expert Comment

by:Eric Fletcher
ID: 24809632
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
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 2000 total points
ID: 24810209
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
 
LVL 18

Author Closing Comment

by:Cory Vandenberg
ID: 31601288
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
 
LVL 18

Author Comment

by:Cory Vandenberg
ID: 24810370
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
 
LVL 18

Author Comment

by:Cory Vandenberg
ID: 25368809
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
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 25369192
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question