Trying to Loop through a search

[tla]

I have code that loops through a search, finds a word and then hyperlinks that word to another document. It starts by opening excel, reading in the list of words into an array and then searching the document for each word in the array.

Right now I have only three words in the array and a short test paragraph. It works flawlessly for the first word and replaces multiple instances of it in the document, but it never starts the loop for the second word (I have a msgbox to pop up each word as it . It pops the first word, does the replacements, pops the 2nd word but then gets stuck in an endless loop. I am sure my Do loop is wrong but I don't know how to tell it to search from the beginning to end of the document with Find (I don't think I can do a find repalce all and still add hyperlinks). Here is the code:

Sub Highlight()
Dim myTerm As Variant
Set FSO = CreateObject("Scripting.FileSystemObject")
Set xlApp = CreateObject("Excel.Application")
Set wdApp = CreateObject("Word.Application")
myGlossary = ActiveDocument.Path & "\Test Glossary.xls"
If Dir(myGlossary) = "" Then
MsgBox MyFile & " could not be found !", vbCritical
Exit Sub
End If
xlApp.Visible = True
Set wbOpen = xlApp.Workbooks.Open(myGlossary)
myTerm = xlApp.Range("A5:A10").Value
'xlApp.Application.Close
'xlApp = Nothing
Windows("Test Document.doc").Activate
wbOpen.Close
xlApp.Quit
For Each X In myTerm
Count = Count + 1
If myTerm(Count, 1) = "" Then Exit For
MsgBox myTerm(Count, 1) 'test box to display the word
Selection.HomeKey Unit:=wdStory 'sends cursor to beginning of document
Selection.Find.ClearFormatting
Do Until Selection.Text = ActiveDocument.Words.Last
With Selection.Find
.Forward = True
.ClearFormatting
.MatchWholeWord = True
.MatchCase = False
.Execute FindText:=myTerm(Count, 1)
End With
If Selection.Text = myTerm(Count, 1) Then
Selection.Font.Italic = True
Documents("Test Document.doc").Hyperlinks.Add Anchor:=Selection.Range, _
Address:="C:\Documents and Settings\a973193\Desktop\Current Work\Test Glossary.xls"
End If
Loop
Next X
End Sub

[fumei]

1. Please use the VBA tags to post code.

2. You are not using Option Explicit, are you? Your variables are not declared. I would strongly recommend you use Option Explicit.

3. What application are you using? Where is this running from? I notice you are making instances of Word and Excel.

4. You have: Set FSO = CreateObject("Scripting.FileSystemObject")...with FSO not being declared, but...you never USE it. What is this for?

5. You have: MsgBox MyFile & " could not be found !", vbCritical

but MyFile (a string I would assume) is never declared, nor set as anything.

6. For Each X In myTerm

What the heck is X? You seem to think myTerm is an array. It does not look like an array to me. You use:

myTerm(Count, 1)

but myTerm is not declared as an multidimensional array. I do not see any reason why it WOULD go to the second item.

[TonyJollans]

What application is this running in?

Your loop control ..
Do Until Selection.Text = ActiveDocument.Words.Last
.. will only ever be satisfied with one (at most) of the terms in the excel cells.
Generally Find loops are of the form ..
Do While .Find.Execute

[TonyJollans]

I see you beat me to it, Gerry.

.. myTerm is a Variant which gets set to a 2-dimensional array when assigned from an Excel range - but otherwise I agree with you: the code could use some work :)

[tla]

wow that's hostile. Yes the code is a little rough becuase I have been experimenting with and changing various things and havn't gone back through it. THe loop is the only part that isn't working so the comments on the rest of it aren't really relvant (yes I WAS using FSO and I changed the myFile vairable and never changed it in the error code but I'll clean that up before I finalize the code).

The code is being run from word (it's the word forum so I didn't think I needed to state that, sorry). but i am opening up an excel file and reading from it in the code.
My term IS an array. the line:
myTerm = xlApp.Range("A5:A10").Value
Defines it automatically as a multidemtional array (whenever you sent a variable = to a range in Excel it becomes a multidimentional array even though it really only has 1 dimension of data). And like I said it does work becuase the line:
MsgBox myTerm(Count, 1) 'test box to display the word
Does display the first word and teh 2nd word read but in the second iteration it goes into an endless loop.

As for X that is my last desperate try to get the loop to work, I have tried everything I know. THat is essentially the question here. What is the correct looping statment to get the find command to search the entire document for the current word (Myterm (Count,1)) and if it finds it, hyperlink it, and keep searching.

[TonyJollans]

The reason for asking about the app is that you instantiate a Word application as well as an Excel one, suggesting you are running fom an unspecified third app. I note, now, however, that you are not using the extra Word app and it does become clear that you are running in Word - so why create a second instance?

[TonyJollans]

> ... even though it really only has 1 dimension of data

Hmm .. Excel Ranges are 2-dimensional - the fact that, in this case, one of the dimensions occurs only once is incidental. Imagine how it would be if the shape of the array changed depending on the shape of the range.

Gerry, for info, if you don't know, this works in much the same way as the variant becoming an array when receiving the result of a Split.

[fumei]

Sorry if it seemed hostile. Not intended that way.

If you are running it from Word, why are you creating an instance of Word? Ah, but since:

"THe loop is the only part that isn't working so the comments on the rest of it aren't really relvant"

I guess that is irrelevant.

BTW: regarding your loop, Tony answered you.

It would work much better, and easier, if you used a Range find loop, but I take it that could also be an unwanted comment. If it is not too hostile to suggest, something like:Sub MakeHypers()
Dim myArray1()
Dim myArray2()
Dim var
Dim r As Range

myArray1 = Array("quick", "fox", "dog")
myArray2 = Array("c:\test\bob.doc", _
"c:\temp\temp2\ignore.doc", _
"c:\myfiles\11Review.doc")

Set r = ActiveDocument.Range
For var = 0 To UBound(myArray1)
With r.Find
.ClearFormatting
Do While .Execute(Findtext:=myArray1(var), Forward:=True) = True
ActiveDocument.Hyperlinks.Add _
Anchor:=r, Address:=myArray2(var)
r.Collapse Direction:=wdCollapseEnd
Loop
End With
Set r = ActiveDocument.Range
Next
End SubThis would make a a hyperlink to "bob.doc" for every instance of "quick"; make a hyperlink to "ignore.doc" for every instance of "fox"; make a hyperlink to "11Review.doc" for every instance of "dog"

The keys to making the loop work are bolded.
Good luck.

[tla]

Tony,

Wont

Do While .Find.Execute
only work within the with statement? I don't think I can set the hyperlink within the with statement so I need to loop outside of with. Perhaps i need some context for this statement. THe must be way to basically say:
Do until you reach the of the document I am just missing it.

[fumei]

"Do until you reach the of the document I am just missing it."

Clearly. It will work if you set a Range for the document.

"I don't think I can set the hyperlink within the with statement so I need to loop outside of with. "

Not true. Where did you get that idea?

[TonyJollans]

Although Find reaches the end of the document, the Selection doesn't - and even if it did, it wouldn't necessarily be a wholle word.

It is true that .Find(.Execute) needs qualification from a With - it was only meant to be exemplary. I see Gerry has given you an answer anyway - much better than my brief one.

[tla]

hmmm i'll have to tinker with that code gerry, that may just work.
To answer about the instance of word... is a leftover from trying to reactivate the word app after opening excel (that didn't work). I just couldn't get
Windows("Test Document.doc").Activate
to make Word the active window in the view. I ended up closing the Excel applicaiton instead and since the macro is run from word it sends them back.
So yes, LOTS of clean up to do, which is why I am only concerned about the loop right now.

[lucas]

tla,
You should read our faq before posting in the forums. I would suggest that since you are being given free help from true experts in the field that you should show some respect and gratitude instead of attitude.

You posted hard to follow code without using vba tags for your code which makes it even harder to follow and then when asked a few direct and to the point questions that in the end will be beneficial to your project you respond defensively. Please show respect and be courtious.

Experts here will give assistance but I think you will find that you are the one seeking help and should be especially considerate of answering pointed questions so that the folks who are trying to help you don't have to figure out everything you have been experimenting with.....that's really not too much to ask.

[fumei]

Just to follow that, we were not (I wasn't, and I have never seen Tony be hostile) trying to be hostile.

I mentioned Option Explicit because you SHOULD be using it. Period. It will make your code work better. Period.

You did indeed ask about how to make your loop work. True. However, if there is a better way, should we not mention it? yes, we could state how to make your given code work, but it would be against the idea of these forums to ignore telling someone that...gee, that could work, but THIS way works much better.

Which is, I think, one of the main point of having these forums in the first place.

[tla]

Gerry,

That code worked perfectly after I thinked with the variables a little to match my code (I am only using the one array, etc). I didnt' realize you could do a while loop within the with statement. with statements aren't something i have used much in my excel macros (this is my first word macro).

Thanks.

now i have one more side issue which isn't critcal but it's a nice to have. I'm now looking for a way to manipulate the excel file when it's opened from one of the hyperlinks. In excel this would be easy but I'm stubling on doing it from word.... apparently "word is different".

So I have a document with a bunch of words that are hyperlinks to an excel file. When one of them is clicked (Ctrl click) it opens the excel document. But i want it to find the word within the list in the excel document (could be hundreds of lines).

So:
can a hyperlink trigger an excel macro?
or can a hyperlink pass a variable to the excel file (like file///C:/myfolder/myfile.doc?myWord = word) - then i could program a startup macro in the excel file to look for the word coming in and do it's thing.

I tried these but they didn't even get to opening the file so boviously they don'e work the same way as on the web. This is a pipe dream i am sure, but figured i'd ask.

[TonyJollans]

As far as I know there isn't anything you can do. Word does not have a followHyperlink Event as Excel does and gives no indication anywhere that one is followed.

I guess there are APIs to hook key presses and mouse clicks but I don't know what they are or how to use them or what issues there might be.

I don't think Excel gives any indication of how it was invoked so I don't think you can do anything at that end either.

I think it may be your pipe dream.

[fumei]

Light 'em, if you have 'em.

Tony is right of course.

No, Word just uses the link. It can not trigger, by itself, anything. You can not pass a variable.

HOWEVER, you can pass a bookmark name (if the target is a Word document), OR a Named cell/range in an Excel file.

In a Word document, a hyperlink with the following address:

C:\Test\sample text.xls#NewPlace

will (when clicked), open the Excel file, AND select the named cell (NewPlace). In other words, it will put focus on that cell.

That is the best it can do. Again, it can not pass a variable.

In other words, you could not use (from Word):

C:\Test\sample text.xls#yadda

with Excel taking "yadda" as a variable, and running through looking for it.

I am not sure how Word instructs Excel to select the parameter by hyperlinking. It must somehow, but I fear it is not exposed to VBA. Someone may know in the Excel forum.

[fumei]

I guess I should sum that up.

Can you use a Word hyperlink with a parameter that Excel uses to find something? No.

Can you use a Word hyperlink with a parameter that Excel uses to go somewhere? Yes, but that somewhere has to already exist (named).

If it does not, the Excel still opens, but there is a reference not valid error. Now....hmmmm, smarter people than me may find a way to get the actual value of that.

In which case, you could trap it, and THEN use Excel to find something.

Otherwise, you need to actual name the cells in the Excel file.

[tla]

Ok,

Any tips on how to do it? You say you can pass a parameter that excel uses to go somewhere - How do you pass the parameter? As long as excel gets the info I think I may be able to work something. I just can't find anything in the help files on passing parameters from the hyperlink correctly.

[TonyJollans]

You can't pass a paremeter. Well, I guess, more correctly, Excel can't receive a parameter.

All you can use is a sub-address, a range name preceded by a # character after the main url, which will make Excel go somewhere.

[fumei]

tla, please read the posts.

"Any tips on how to do it? You say you can pass a parameter that excel uses to go somewhere - How do you pass the parameter? "

I posted precisely the answer to that question.

1. I stated clearly that you can NOT pass a parameter, or, more accurately, as Tony points out, Excel can not receive a parameter.

2. "You say you can pass a parameter that excel uses to go somewhere" Yes indeed, AND I posted an example of how to do that.

Please read the posts.

[tla]

I figured that out. I see how you can open the Excel and set a name equal to a cell range and then add the name in the link. I think i just have to add a macro to the excel file to add a "name" for each word in the glossary and this will work.

Thanks

[TonyJollans]

Just out of interest how do you propose to intercept the hyperlink at the Excel end?

[fumei]

tla, yes for sure, you could write a macro to go about naming your stuff in the Excel file. In fact, you would have to, because the names MUST exist for the hyperlink to work from Word.

As Tony points out, and I have stated twice, you can NOT intercept the hyperlink at the Excel end.

You must give a specific name to the hyperlink in Word.


I figured that out. I see how you can open the Excel and set a name equal to a cell range and then add the name in the link.

You are not opening Excel and setting anything. You are opening Excel and telling it, go THERE. At least, as the code stands.

The THERE (a named cell or range) used by a hyperlink, MUST exist, otherwise Excel says...."Huh???? I have no idea what you are talking about."

So, by all means, write a macro to go through the Excel file, naming your stuff. But you have to do that BEFORE you can use those names with a hyperlink in Word. You can not do it after.

In other words, you can not use:

c:\Test\yadda.xls#There

and have the hyperlink open yadda.xls and make something "There"....then go there. The There must already exist.

You can not have code that sets the name, THEN adds the name to the hyperlink. Well, actually....hmmmm, you sort of can. And that may be what you are thinking.

You could have code, in Excel, that goes to each word, sets the cell as a Name, then goes to Word and makes a hyperlink.

True.

Not sure that is very efficient. Shrug. It may be. So if you were to:

1. make an instance of Excel
2. open the file
3. find the word
4. make it a Name
5. go back to Word
6. make a hyperlink to the same file WITH the name

That would indeed work. The point though is you are making the hyperlink AFTER you have made the Name.

[tla]

I'm all set. I am doing exactly what you say here. If you think it through, the excel file being opened must ALREADY be created to create the hyperlinks (I am reading in the value of column A in the excel file to get my list of words to hyperlink). The solution is simply to add a macro to that excel file that runs on exit that makes a "Name" for any word in that list using the word as the Name. then the hylinks will always be right (at the time of creation). If something is later removed from that list older dcouments will still open excel, but then error on finding the word.

This also has the added benefit of allowing me to add in code to remove any blank lines so I know my list wont have blanks in them when it read it in so it's a win win.

I already Coded excel (that's like 5 lines of code) and it works pretty good. Now I am cleaning up this code and It should be perfect.