Possible to Find and Replace from a Database

[thinksalot]

Okay, so I learned to write the macro for a lengthy find and replace; then one of you showed me a macro for an array to take that macro's place (which works beautifully by the way). While they all work in theory, none work in practice as I have to maintain the template every time a new term is added, which means constant updates and distribution.

I think I need to link my macro directly to a separate document housing the terms (i.e., database either in Word or Excel) rather than list the terms directly in the macro. This would mean I only have to maintain the list and would not have to update the macro as often.

My question: Is this even possible?

I've seen many posts and Help information on databases, so know it is possible to link to separate documents to perform a variety of tasks, but have seen nothing on using find and replace in this manner. Anybody have a clue? I did order the VBA Handbook (or Bible, forget what it's called), but won't arrive for a few more days.

Thanks.

[fumei]

If the macro is using an array (and I am assuming a string array), then it would be fairly easy to parse a file with those string elements. Something like (and I am just writing this here directly, rather than trying to see if this works in the VBE.For Each oWord In ActiveDocument.Words()
ReDim Preserve SearchArray(i)
SearchArray(i) = oWord.Text
i = i + 1
NextI will post the full code in a second. I just want to point something out.

Say you have adocument like this:

One
Two
Three
Four

That is it. OK, Do a word count using Tools > Word Count. You get 4. Four words. Say you run code like above, but also do a counter.Dim i As Interger
Dim SearchArray() As String
Dim oWord
For Each oWord In ActiveDocument.Words()
ReDim Preserve SearchArray(i)
SearchArray(i) = oWord.Text
i = i + 1
NextIt fills the array with every word in the document. And there are only four words, right? Right. No, not to VBA. Tools > Word Count still = 4. However, because oWord is declared as a Variant, the total number of oWords = 8. That is, i = 8. It counts the paragraph marks. So you need to be a little careful.

In any case, you would do something like that. Make a document with the listing, open it, parse through it, word for word, and build the array with those words.

[thinksalot]

Your code looks like it will absolutely work; makes it seem very easy to search through the other document, so will give it a try today. This was my original array, so I will just add your code to it and keep moving forward. Thank you very much.

By the way, where did you learn this stuff? From a book? I've looked for classes in So Cal, but found nothing--signed up for a VB.NET class by accident, but didn't help me much here. Thanks again.

Sub Test()
Dim MyText(2, 2)
MyText(0, 0) = "Windows"
MyText(0, 1) = "Windows?"
MyText(1, 0) = "Test"
MyText(1, 1) = "Test?"

Selection.HomeKey Unit:=wdStory
For i = 0 To 1
Selection.Find.ClearFormatting
With Selection.Find
.Text = MyText(i, 0)
End With
Selection.Find.Execute
Do
With Selection.Font
If Not .Name = "Courier" Then
'or
'If .Name = "Arial" Or .Name = "Times New Roman" Then
If Not .Italic = True Then
Selection.TypeText MyText(i, 1)
Exit Do
End If
End If
End With
Selection.Find.Execute
Loop
Selection.HomeKey Unit:=wdStory
Next i
End Sub

[fumei]

Hmmm. You can really just "add" the code to your code. You have to create the other file first, and then...well...you have to open that file....not that this is hard to do.

[thinksalot]

Please forgive my laymen's mentality...wouldn't say I can "just add" your code to my macro and it'll work, but I could just add your code to my macro and get a good idea of what I need to do next. I also have pieces of other code I wrote that I pieced at the end so it is looking pretty good about now. And, while it definitely won't work as-is, it is definitely putting me in a better mindset as to how to get where I need to be. So thanks again.

Still curious where you learned your stuff. You do seem to know it very well.

[fumei]

Where did I learn this?

1. Years and years of working with Word, and relentlessly trying stuff.

2. Serious study of the Word object model. (Highly recommended.)

3. A couple of books. Word 2000 Developer Handbook, and VBA Developer Handbook.

#1 is the most helpful. There is no substitute for slogging away. Believe me, there was hair pulling on more than one occasion.

[thinksalot]

I, too, have been working with Word on an advanced level for many years, but really haven't had much need to write extensive macros (our IT department usually did that for us). I've mostly just had to record a macro, then tweak it a bit to meet my needs.

I will definitely look into the object model and see what I can learn. I ordered the VBA Developer's Handbook the other day, so expect it to arrive maybe tomorrow (since not today). Once I get the book, I'll start writing the code the way it should be written and hopefully will have that one knocked out by sometime next week. All of this is pretty funny as I'll soon be heading into a FrameMaker environment, so will probably learn a whole lot of stuff just in time to leave Microsoft.

Thanks again.