PDA

View Full Version : Need more robust "find text" VBA



jdooley3
11-07-2013, 12:27 PM
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
11-08-2013, 08:52 AM
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



Instead of having two Excel lists, why not just highlight the item in FindList, if it is found in the Word Doc?
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.
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
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
11-11-2013, 06:42 AM
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
11-11-2013, 08:37 AM
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.