Text recognition and Hyperlinks

[dimmutal]

Hi there. This is the case. Assume you have some phrases in Word. At the beginning and at the end of this phrase there is a special character lets say #. For example lets say say that we have the phrase: #This forum is the best#. It helps a lot of #people#. Now assume that in an Excel column you have the paths that point to two files lets say C:/file1.pdf and C:/file2.pdf. What i want to do is to write a macro that will recognize the text that is between the #character and hyperlink it to the corresponding file, i.e. This forum is the best will be hyperlink to file1.pdf and people to file2.pdf. Any idea on how to write a macro on that, or any other idea??

To help you more the exact case is this. The phrases in the Word document, are book titles. And i have an excel which contains the paths to the corresponding pdf file of each book. So i want to hyperlink the titles in Word to the paths that are shown in Excel. In that way one will open the word and after clicking the desired title the corresponding book in pdf format will open.

Thanks

[MWE]

Hi there. This is the case. Assume you have some phrases in Word. At the beginning and at the end of this phrase there is a special character lets say #. For example lets say say that we have the phrase: #This forum is the best#. It helps a lot of #people#. Now assume that in an Excel column you have the paths that point to two files lets say C:/file1.pdf and C:/file2.pdf. What i want to do is to write a macro that will recognize the text that is between the #character and hyperlink it to the corresponding file, i.e. This forum is the best will be hyperlink to file1.pdf and people to file2.pdf. Any idea on how to write a macro on that, or any other idea??

To help you more the exact case is this. The phrases in the Word document, are book titles. And i have an excel which contains the paths to the corresponding pdf file of each book. So i want to hyperlink the titles in Word to the paths that are shown in Excel. In that way one will open the word and after clicking the desired title the corresponding book in pdf format will open.

ThanksWelcome to the forum.

Does the Excel file contain both the expected words/phrases to be found in Word and the file to which the hyperlink is to be built for that word/phrase. If so, then a macro in Word might proceed as follows:
a text search is done for the first word/phrase (bracketed by # in your example)
when the word/phrase is found, the selection is checked for an existing hyperlink (yes? no?); if no hyperlink is found, then the Excel file is searched for a matching word/phrase in, say, col1
when a match is found, the path and file name found in col2 of the Excel file are used to build a hyperlink in the Word doc.
the text search continues, i.e., steps 1 - 3, until the end of the doc is encounteredIs that about what you want to do?

[dimmutal]

Exactly. I can have both the path and the phrases in excel. Your idea is correct as an algorithm. But i'm a bit anaware of writing macros in Word. Any idea on how to search for the phrases in word and how to do the hyperlinking?

[fumei]

Here is a start for you.Sub MakeHyperlink()
Selection.HomeKey Unit:=wdStory
With Selection.Find
.ClearFormatting
.MatchWildcards = True
Do While (.Execute(Findtext:="#*#", Forward:=True) _
= True) = True
With Selection
' resize Selection to NOT include the start / end #
.MoveStart Unit:=wdCharacter, Count:=1
.MoveEnd Unit:=wdCharacter, Count:=-1

' if Selection does NOT have a hyperlink
If .Hyperlinks.Count = 0 Then
' add code to get the hyperlink string from Excel
' this string replaces "URL_orBookmark" below
.Hyperlinks.Add Anchor:=Selection.Range, Address:= _
"URL_orBookmark", SubAddress _
:="", ScreenTip:="", _
TextToDisplay:=Selection.Range.Text
' collapse the Selection, and move it
' past the ending #
.Collapse Direction:=wdCollapseEnd
.Move Unit:=wdCharacter, Count:=1
End If
End With
' continue the search
Loop
End With
End Sub

[dimmutal]

Thanks. Any recommendations on how to get the link from excel? Sorry for bothering but as i said i don't know a lot about macros in Word.

[dimmutal]

Ok i've tried to work on the code. I declare an array which contains the number of the titles (each book is saves as num.pdf). All i need to do is to get in each step a different value of the array. But it doesn't work. What is wrong with this code?

Sub MakeHyperlink()
Dim Files(4, 1) As Integer


Selection.HomeKey Unit:=wdStory

Files(0, 1) = 21
Files(1, 1) = 1231
Files(2, 1) = 1412
Files(3, 1) = 12

With Selection.Find
.ClearFormatting
.MatchWildcards = True

Do While (.Execute(Findtext:="#*#", Forward:=True) _
= True) = True

With Selection
' resize Selection to NOT include the start / end #
.MoveStart Unit:=wdCharacter, Count:=1
.MoveEnd Unit:=wdCharacter, Count:=-1
i = 0

' if Selection does NOT have a hyperlink
If .Hyperlinks.Count = 0 Then
' add code to get the hyperlink string from Excel
' this string replaces "URL_orBookmark" below
.Hyperlinks.Add Anchor:=Selection.Range, Address:= _
"C:/" & Files(i, 1) & ".pdf", SubAddress _
:="", ScreenTip:="", _
TextToDisplay:=Selection.Range.Text

i = i + 1

' collapse the Selection, and move it
' past the ending #
.Collapse Direction:=wdCollapseEnd
.Move Unit:=wdCharacter, Count:=1
End If
End With

' continue the search
Loop
End With
End Sub



Edited 10-Apr-06 by GeekGirlau. Reason: put code in VBA tags

[fumei]

1. Please use the VBA tags when you post code.

2. Walk through the code and keep a watch on the variable i.

You are making i = 0 each time! The Do loop finds the next #*#. This makes a Selection. You set i = 0. You use Files(i, 1) ...that's nice. You set i = i + 1. It continues on in the loop. The Do llop finds the next #*#....and you....set i = 0 again.

Unless you specifically have a need to do so, you probably do not need the i = 0. It should be 0 anyway to start.

[fumei]

Oh, and you may want to have some error trapping. As it stands, with the increment of i = i + 1, you are going to have a MAXIMUM of 4 iterations before you may get a run-time error.

If there are more than 4 instances of #*#, then you WILL get an error. Once i = 4, then Files(4,1) will be out of range.

You may need to enhance your logic here.