Need more robust "find text" VBA

[jdooley3]

I am looking to scan within a document and highlight certain words. There will be many words based inside an excel worksheet, and the list may change so I cannot just copy and paste all the words now in the list. I have included my code thus far (early stages), please post anything you may know. In this example, I am looking for a change in the top part where I search for the word "Olympics". This needs to be changed to idealy a link to an excel document and then recognize the list and highlight when those show up in the word doc. I hope this makes sense, please ask if you need clarification.

Sub Highlight_SP_Tower()
 
    Dim sFindText As String
 
    sFindText = "Olympics"
 
    Selection.ClearFormatting
    Selection.HomeKey wdStory, wdMove
    Selection.Find.ClearFormatting
    Selection.Find.Execute sFindText
 
 
Do Until Selection.Find.Found = False
    Selection.Range.HighlightColorIndex = wdYellow
    Selection.MoveRight
    Selection.Find.Execute
Loop
 
'The following code will now extract highlighted words into a specified excel worksheet, populating A1,A2,A3...etc.
    Selection.ClearFormatting
    Selection.HomeKey wdStory, wdMove
    Selection.Find.ClearFormatting
 
'set searching for highlighted words
    Selection.Find.Highlight = True
    Selection.Find.Execute
 
'open workbook within new Excel application
    Dim EXL As Object
    Set EXL = CreateObject("Excel.Application")
    Dim xlsWB As Object
   
    Dim xlsPath As String
   
'put path to file here
    xlsPath = "C:\Users\DooleyJ\Desktop\VBA Test\Book1"
 
    Set xlsWB = EXL.workbooks.Open(xlsPath)
 
    Dim xlsRow As Long
    Do Until Selection.Find.Found = False
'we will write found words to first sheet in your Workbook, consecutive rows in column A
 
        xlsRow = xlsRow + 1
        xlsWB.sheets(1).Cells(xlsRow, "A") = Selection.Text
        Selection.Find.Execute
 
Loop
 
'show excel application
    EXL.Visible = True
 
End Sub

[SamT]

Is this what you want to do?

If a word is in an Excel List, highlight that word in a Word Document, then add the word to another Excel list?

Call them FindList, Doc and FoundList

1. Instead of having two Excel lists, why not just highlight the item in FindList, if it is found in the Word Doc?
2. Is the name and location of the Excel FindList a constant? Or, do you need to use a File Dialog to open a new Excel file each time.
3. Will you always use C:\Users\DooleyJ\Desktop\VBA Test\Book1 as the Excel FoundList? Or will it be treated the same as the FindList Workbook
4. Will the Macro only be used on one Ms Word Document? Or will it need to be used one many Docs? If used on Many Docs, Can the Macro be kept in and Run from the Excel FindList Workbook?

I belive the following code is functionally identical to the code in your post. That is; It will produce the same results (and errors.)

Option Explicit

Sub Highlight_SP_Tower()
     
  Dim sFindText As String
  Dim EXL As Object
  Dim xlsWB As Object
  Dim xlsPath As String
  Dim xlsRow As Long
  Dim FoundList As Object
       
    'open workbook within new Excel application
  Set EXL = CreateObject("Excel.Application")
   'put path to file here
  xlsPath = "C:\Users\DooleyJ\Desktop\VBA Test\Book1"
  Set xlsWB = EXL.Workbooks.Open(xlsPath)
  Set FoundList = xlsWB.Sheets(1).Range("A:A")
  
  sFindText = "Olympics"
    
  '? Clear all formats from Doc? ISamT
  With Selection
    .ClearFormatting
    .HomeKey wdStory, wdMove
    .Find.ClearFormatting
    .Find.Execute sFindText
  End With
     
  With Selection
    Do Until .Find.Found = False
        .Range.HighlightColorIndex = wdYellow
         xlsRow = xlsRow + 1
         FoundList.Cells(xlsRow) = .Text
        .MoveRight
        .Find.Execute
    Loop
  End With
     'show excel application
    EXL.Visible = True
     
End Sub

[jdooley3]

Thank you for the response.

1. There does need to be a findlist and foundlist excel file, it cannot be combined into one.

2. The name and location will not be a constant, so a dialogue box would actually be the most beneficial.

3. The foundlist should be a new excel file being created each time the macro is ran.

4. The macro can be kept in the findlist workbook. The MS word document may also change so a dialogue box may be what I need.

In what you wrote above, I see the findtext "olympics", how can this be findtext "anything in the excel file". Thanks again for responding and helping out.

[SamT]

In what you wrote above, I see the findtext "olympics", how can this be findtext "anything in the excel file". Thanks again for responding and helping out.

That was in your code and I kept my version functionally identical to your version.

Any time you are developing code, it helps to have an outline of what you need the code to do. this is called an algorithm. a simple outline is one from of an algorithm. may algorithms use words from the coding language, especially in VBA because VBA uses a human language dictionary.

Your possible Algorithm:
WorkFlow

Open FindList.xls
Edit word list As needed

(Code algorithm)
Start process

Click Commandbutton

Create new xls workbook

(FoundList)

Open desired Word Doc

Open FileDialog

'Process word list against Word Doc
Begin Process loop

Get Word from word list
Begin Find Loop

Find all instances of word in Doc
If Found

Highlight Instances in Doc

copy word from FindList to new xls workbook (AKA FoundList)

End Find Loop

End Process Loop


'Save foundlist
Get new Name of FoundList

User enter name in InputBox
OR
Create Name from Doc Name, date, folder, etc

"Words Found in MyDoc.doc 11-13-2013.xls"

Open SaveAs File Dialog

FoundList.SaveAs New Name

Close Word Doc
Close FindList.xls

(END Algorithm)

As you study that, with the idea of customizing it for your particular needs, note that the uppermost level of the outline merely states, in the broadest terms, what to do. The next lower levels provide more details about what to do. Some outline levels may hold suggestions.

Note that there is no code in that very simple algorithm. For example you might always keep your Word Documents in a master folder "C:\Documents and Settings\DoolyJr\My Documents\Word Docs\." When you write your own algorithm, you can include in the algorithm a constant set to that value and use it in the Open File Dialog box section of the algorithm.

The purpose of an algorithm is to let you think about what the code is supposed to do without thinking about the difficulties of making the code work. However, you can and should put in as much details as you happen to think of.