Solved

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

Posted on 2009-07-08
16
1,856 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:WarCrimes
  • 10
  • 3
  • 2
  • +1
16 Comments
 
LVL 18

Author Comment

by:WarCrimes
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:WarCrimes
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
 
LVL 18

Author Comment

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

Author Comment

by:WarCrimes
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:WarCrimes
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:EricFletcher
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:WarCrimes
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 21

Expert Comment

by:EricFletcher
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:WarCrimes
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:EricFletcher
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 500 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:WarCrimes
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:WarCrimes
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:WarCrimes
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

I'm writing to share my clumsy experience in using this elegant tool so you can avoid every stupid mistake I made. (I leave it to the authorities to decide if this deserves a place in the Knowledge archives.)  Now that I am on the other side of my l…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

759 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now