Search this blog:

TO ASK A QUESTION: If you have a question or need help with Office, please feel free to use the 'Click to Contact' link at the bottom of this page. You'll get a form that you can use to email a question to me. (I had been getting a ton of spam when accepting direct emails, so only emails that use this form will get through to me.)

Please be sure to mention the version of Office you are using when you send your question.

I answer all e-mails that I receive via this form, as long as they are polite :)

Since disabling comments on this site, I'm actually hearing from more of you with questions ... so, as it seems people prefer to email rather than comment, I'm going to leave comments disabled. As always, you can ask me any Office-related questions you have. If the question is outside of my expertise, I'll try to direct you to where you can get an answer.

Hello again! As promised in today's earlier post with the PowerPoint VBA issue -- here's that interesting Word VBA topic I wanted to address ...

You might have seen discussion of this in comment threads to earlier posts, but here's the deal:

The reader who posted the question had a bit of a daunting task in front of her that she wanted to automate... She has to replace hundreds of identical text references throughout a document with hyperlinks. She'd come up with some nice code using the Find object ... and was looking to add a loop to get what she needed.

Here is approximatley the code she started with:

(Incidentally -- as you probably know, VBA requires an underscore to break up a single line of code -- where you don't see an underscore here is just where my blog had to wrap the text -- if you copy and paste any code in this article into VBA, it will wrap correctly. Also - my blog platform loses the indents in pasted text ... and I'm not going to take time today to put them back in for these examples (busy day! :) -- but you know you'll have easier to read code if you indent nested code (such as indenting the If statement that's within the For Each loop in my suggested code below :))

Dim myfd As Find

For Each myfd In Selection.Find

With Selection.Find
.ClearFormatting
.Style = ActiveDocument.Styles("Bold Char")
.Text = "Matt."
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindAsk
.Format = True
.MatchCase = True
.Execute
ActiveDocument.Hyperlinks.Add Anchor:=Selection.Range, Address:="", _
SubAddress:="Matthew", ScreenTip:="", TextToDisplay:="Matt."
End With

That works well for one instance at a time, but I'm going to suggest using just a loop instead, rather than in addition. The Find object is most effective, I think, when you can replace with something that allows you to use

Selection.Find.Execute Replace:wdReplaceAll

to take care of the whole deal at once.

When you need a loop -- I suggest letting the loop do the work - so that's what we'll look at.

As you saw in the Find example above, we need to search for all instances of an abbreviation -- in this example, that's "Matt." -- and replace it with a hyperlink that reads the same, but links to a bookmark called "Matthew."

I'm going to use the Words collection object to search for the word "Matt" with the formatting specified.

Sub ChangeToLinks()

Dim awd As Range

For Each awd In ActiveDocument.Words

If awd.Text = "Matt" And awd.Style = "Bold Char" Then
awd.Select
Selection.MoveRight Unit:=wdCharacter, Count:=1, Extend:=wdExtend

ActiveDocument.Hyperlinks.Add Anchor:=Selection.Range, Address:="", _
SubAddress:="Matthew", ScreenTip:="", TextToDisplay:="Matt."
End If
Next awd

End Sub

Now, searching word-by-word in a long document will take a couple of minutes to run ... but it's a quick and clean solution. I created a 200 page sample document with about 5 matching references per page, and the macro took just over 2 minutes to execute (on a pretty speedy new computer, but with several documents and several other apps running).

Read on for explanations of how that loop is set up and why ...

If you search for 'word object' in Word VBA help, you'll get the words collection object -- and that article will tell you this:

"Each item in the Words collection is a Range object that represents one word. There is no Word object."

For this reason, the variable used for the loop -- I called it awd -- is defined as a range.

Then, I just looped through all words in the document...

When it found the word Matt formatted with the specified character style, it selected it -- extended the selection to include the period that follows the word -- and then replaced that selection with the specified hyperlink.

*************************

This is such a great example of how VBA can save time on daily document production ... imagine the time to manually replace each of hundreds of words with hyperlinks. And, as it turns out, the person that asked about this code has to do this same thing in dozens of documents... so a macro is such a good way to go.

But, it's also an example of the fact that VBA is really flexible. The code I recommend is far from the only way to go. There are several solutions to most code problems -- which is part of the beauty, I think, of learning VBA.

That said, if you have a different solution you'd prefer over mine and would like to recommend -- please post it in a comment for everyone to share in ... I think it would be cool and interesting to look at different takes on this one ...

Happy Thursday!

© 2004-2008 Stephanie Krieger | Click to Contact | Syndicate this site | Powered by Movable Type
Design by Michelle Jones