Selection.Find Function 3

[Mike555]

I'm working with the Selection.Find function in MS Word VBA. Is it possible to implement an If THEN ELSE statement that performs one thing if data is found, or another thing if no data is not found? My code is below.


Sub FindCustID
With Selection.Find
.Text = "Cust ID: ????"
'???? are 4 wildcards representing the customer ID
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = True
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute

IF DATA IS FOUND.........
Do something

IF DATA IS NOT FOUND.....
Do something else

End Sub


Thanks.

--
Mike

Why make it simple and efficient when it can be complex and wonderful?

 

[SkipVought]

Mike,

If (expression) Then
    Do something
Else
    Do something else
End If

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[SkipVought]

Try this...

If (Selection.Find.Found) Then
    Do something
Else
    Do something else
End If

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[TonyJollans]

Hi Mike,

To add a bit to Skip's post ..

The (expression) should be Selection.Find.Found`

Enjoy,
Tony

-------------------------------------------------------------------
Wishing all Tek-Tippers a Happy and Prosperous New Year

 

[Mike555]

Skip,

But is it possible to tie in the search results of the Selection.Find function to the IF statement? (Possibly through a variable which equals zero if there is nothing found during the search?) The logic here depends on if anything was found during the search.

--
Mike

Why make it simple and efficient when it can be complex and wonderful?

 

[TonyJollans]

Hi Skip,

You beat me to it

Also answered Mike - who was just far too sloooooo

http://wwwww

Enjoy,
Tony

-------------------------------------------------------------------
Wishing all Tek-Tippers a Happy and Prosperous New Year

 

[xlhelp]

Sub FindCustID
With Selection.Find
.Text = "Cust ID: ????"
'???? are 4 wildcards representing the customer ID
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = True
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
tmpstr = Selection.Text


If tmpstr = "Cust ID: ????" Then

(or whatever you want to test against.)
......


End if

If the string is not found, Word generates an error and at the moment I can't recall or find the error that is generated.

http://www.geniusmedia.ca

 

[xlhelp]

Wow, all you folks have fast fingers...

May be time to take up a typing course. then again, at my age......

http://www.geniusmedia.ca

 

[Mike555]

Thank you all for info. I have my logic working.

Since you all helped - you all get stars!

--
Mike

Why make it simple and efficient when it can be complex and wonderful?

 

[Mike555]

I might as well ask this question here, since it is in relation to this post.

In my code above I'm searching for "Cust ID: ????", which is the word Cust ID: and 4 wildcards. I have customer IDs ranging in size from 4 - 7 characters, and right now I have 4 different sets of code. One set of code uses 4 wildcards, another uses 5 wild cards, etc etc. so that I can pull up each variance that may exist.

Is it possible to Find and select all lines within a document that start with the word Cust ID:? As opposed to running various types of searches to look for all length variances?

Tony, I'm using this along with the code you provided in thread68-734214. Basically I'm selecting the entire page and then running the search I've described in this post.

Is it possible to select each line within one page that begins with the word Cust ID:?

Thanks.

--
Mike

Why make it simple and efficient when it can be complex and wonderful?

 

[SkipVought]

You can use the asterisk wildcard

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[TonyJollans]

Hi Mike,

Searching for your string *ending* in 4 wildcards will find "Cust ID: " followed by *any* four characters followed by anything else within the scope of the search. So unless your CustID is the last thing in the page you will find it regardless of how long it is. What sort of testing have you done - does your search exclude, for example,

CustIDline feed)
ProdID: 1234

There are various ways of doing pattern matching but can you tell us how the END of your customer id is delimited (comma, end-of-line, whatever) because without that it's nigh impossible to say how best to proceed. Also will there be, say, 3-character IDs you want to exclude from your search?


Enjoy,
Tony

-------------------------------------------------------------------
Wishing all Tek-Tippers a Happy and Prosperous New Year

 

[Mike555]

In the report I'm working with with, each line representing one kind of data. Therefore my guess is that it would be end-of-line delimited. Here's an example of what one looks like:

Product Line: ABC1234
Warehouse: ZZZZZZ
Cust ID: 1234
Cust ID: 12345
Cust ID: 123456
Cust ID: 1233
Review: HS


I'm trying to create one macro that goes through the entire document, selects each line of information, and pastes that line into a new Excel cell. This works fine if there is only one instance of each type of data, but for Cust ID there could be several. So my theory is to select one at a time, paste it into Excel, and then delete it from the Word document. Then select again looking for a second instance and, if found, copy/paste it into Excel and delete from the Word document. This loop should occur until no more are Cust IDs are found. At that time it should move on to look for the Review. Here's my current code. I've just found out that in any sale there could be anywhere from 1-8 Cust IDs and from 3-5 numbers long

Sub CreateNewExcelWB()
'This module found at -

http://www.exceltip.com/st/ Control_Excel_from_Word_using_VBA_in_Microsoft_Exc<br />el/463.html

' to test this code, paste it into a Word module
' add a reference to the Excel-library

Application.ScreenUpdating = False

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook

Dim i As Integer
Set xlApp = CreateObject(&quot;Excel.Application&quot
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Add ' create a new workbook

With xlWB.Worksheets(1)


'******************FIND THE PRODUCT LINE
Call CallCurrentPage
With Selection.Find
.Text = &quot;Product Line:&quot;
.Replacement.Text = &quot;&quot;
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = True
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute

'Select the value
Selection.MoveRight Unit:=wdCharacter, Count:=2
Selection.EndKey Unit:=wdLine, Extend:=wdExtend

'Copy the value
Selection.Copy

' paste the value into Excel
With xlWB.Worksheets(1)
ActiveSheet.Paste
ActiveCell.offset(, 1).Select
End With


'******************FIND THE WAREHOUSE
Call CallCurrentPage
With Selection.Find
.Text = &quot;Warehouse:&quot;
.Replacement.Text = &quot;&quot;
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = True
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute

'Select the value
Selection.MoveRight Unit:=wdCharacter, Count:=2
Selection.EndKey Unit:=wdLine, Extend:=wdExtend

'Copy the value
Selection.Copy

' paste the value into Excel
With xlWB.Worksheets(1)
ActiveSheet.Paste
ActiveCell.offset(, 1).Select
End With



'******************FIND THE FIRST CUSTOMER ID
Call CallCurrentPage
With Selection.Find
.Text = &quot;Cust ID:*&quot;
.Replacement.Text = &quot;&quot;
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = True
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute

If Selection.Find.Found Then

'Select the value
Selection.MoveRight Unit:=wdCharacter, Count:=2
Selection.EndKey Unit:=wdLine, Extend:=wdExtend

'Copy the value
Selection.Copy

'Paste the value into Excel
With xlWB.Worksheets(1)
ActiveSheet.Paste
ActiveCell.offset(, 1).Select
End With

'Delete the value
Selection.Delete Unit:=wdCharacter, Count:=1

Else
MsgBox &quot;There is something wrong with this sale because there is no customer listed. Review this ASAP!&quot;, vbCritical, &quot;Error&quot;
End If


'******************FIND THE SECOND CUSTOMER ID
Call CallCurrentPage
With Selection.Find
.Text = &quot;Cust ID:*&quot;
.Replacement.Text = &quot;&quot;
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = True
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute

If Selection.Find.Found Then

'Select the value
Selection.MoveRight Unit:=wdCharacter, Count:=2
Selection.EndKey Unit:=wdLine, Extend:=wdExtend

'Copy the value
Selection.Copy

'Paste the value into Excel
With xlWB.Worksheets(1)
ActiveSheet.Paste
ActiveCell.offset(, 1).Select
End With

'Delete the value
Selection.Delete Unit:=wdCharacter, Count:=1

Else
With xlWB.Worksheets(1)
ActiveCell.offset(, 8).Select
End With
GoTo SelectReview
End If


'******************FIND THE THIRD CUSTOMER ID
Call CallCurrentPage
With Selection.Find
.Text = &quot;Cust ID:*&quot;
.Replacement.Text = &quot;&quot;
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = True
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute

If Selection.Find.Found Then

'Select the value
Selection.MoveRight Unit:=wdCharacter, Count:=2
Selection.EndKey Unit:=wdLine, Extend:=wdExtend

'Copy the value
Selection.Copy

'Paste the value into Excel
With xlWB.Worksheets(1)
ActiveSheet.Paste
ActiveCell.offset(, 1).Select
End With

'Delete the value
Selection.Delete Unit:=wdCharacter, Count:=1

Else
With xlWB.Worksheets(1)
ActiveCell.offset(, 8).Select
End With
GoTo SelectReview
End If


'******************FIND THE FOURTH CUSTOMER ID
Call CallCurrentPage
With Selection.Find
.Text = &quot;Cust ID:*&quot;
.Replacement.Text = &quot;&quot;
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = True
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute

If Selection.Find.Found Then

'Select the value
Selection.MoveRight Unit:=wdCharacter, Count:=2
Selection.EndKey Unit:=wdLine, Extend:=wdExtend

'Copy the value
Selection.Copy

'Paste the value into Excel
With xlWB.Worksheets(1)
ActiveSheet.Paste
ActiveCell.offset(, 1).Select
End With

Else
With xlWB.Worksheets(1)
ActiveCell.offset(, 8).Select
End With
MsgBox &quot;Done Here!&quot;
'GoTo SelectReview
End If


SelectReview:

'******************FIND THE REVIEW TYPE
Call CallCurrentPage
With Selection.Find
.Text = &quot;Review:&quot;
.Replacement.Text = &quot;&quot;
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = True
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute

'Select the value
Selection.MoveRight Unit:=wdCharacter, Count:=2
Selection.EndKey Unit:=wdLine, Extend:=wdExtend

'Copy the value
Selection.Copy

' paste the value into Excel
With xlWB.Worksheets(1)
ActiveSheet.Paste
ActiveCell.offset(, 1).Select
End With

End Sub




Sub CallCurrentPage()
Call CurrentPage
End Sub


Function CurrentPage() As Range

'Thread68-734214 This function selects an entire page

Dim StartPos As Long
Dim NextPageStart As Long

StartPos = Selection.Start
Selection.GoTo What:=wdGoToPage, Which:=wdGoToNext, Count:=1

NextPageStart = Selection.Start
If Selection.Start > StartPos Then
Selection.GoTo What:=wdGoToPage, Which:=wdGoToPrevious, Count:=1
Set CurrentPage = ActiveDocument.Range(Selection.Start, NextPageStart)
Else
Set CurrentPage = ActiveDocument.Range(Selection.Start, ActiveDocument.Range.End)
End If

CurrentPage.Select

End Function


Thanks.

--
Mike

Why make it simple and efficient when it can be complex and wonderful?

 

[TonyJollans]

Hi Mike,

It's late here now; I'll have a proper look at your code in the morning but one quick question.

How much of your Word document are you trying to get into Excel, and in what order? I ask because I just copied your sample text above and pasted it into Excel and it put each line in a new cell which is all that your code is doing (I think). If that's all you want then there shouldn't be a need for a lot of searching. I guess it's not going to be that simple but there's no harm in asking

Enjoy,
Tony

-------------------------------------------------------------------
Wishing all Tek-Tippers a Happy and Prosperous New Year

 

[ClaudioFaria]

What you want is property Found of Selection.Find.

Use thus:

IF Selection.Find.Found

or, if inside With Selection.Find

IF .Found

 

[Mike555]

Tony, Yes all I am doing is copying values from the Word document into Excel. Then, once everything has been copied from one page, it moves to the next page in Word and copies that info to a new row in Excel. One Excel row = one page of Word data.

Thanks!

--
Mike

Why make it simple and efficient when it can be complex and wonderful?

 

[Mike555]

Tony,

I forgot to answer your 'how much' question. There are about 8 other fields which I'm pulling from, but I didn't include all of that in the posted code. Those fields are selected after the Review Type.

Thanks.

--
Mike

Why make it simple and efficient when it can be complex and wonderful?

 

[TonyJollans]

Hi Mike,

I'm going to have a bit more of a play with your code because I think it is a bit unwieldy and likely to get more so but the pattern I think you want to search for is:

Cust ID: [0-9]{4,7}>

which means 4 to 7 occurrences of any of the characters 0 to 9 followed by an end-of-word delimiter. If you look for this in a loop until it's not found you should have all your customer IDs. With this search you do not want the line ..

Selection.MoveRight Unit:=wdCharacter, Count:=2

Enjoy,
Tony

-------------------------------------------------------------------
Wishing all Tek-Tippers a Happy and Prosperous New Year

 

[TonyJollans]

Hi Mike,

My apologies; I shouldn't have been so sloppy. If you use the search pattern in my previous post you should replace the lines ..

Selection.MoveRight Unit:=wdCharacter, Count:=2
Selection.EndKey Unit:=wdLine, Extend:=wdExtend

.. with ..

Selection.MoveStart Unit:=wdCharacter, Count:=9


.. instead of just deleting the line I said.

Enjoy,
Tony

-------------------------------------------------------------------
Wishing all Tek-Tippers a Happy and Prosperous New Year

 

[Mike555]

Tony,
Your updated code is working great! I'm happy with how it is working now.

You had mentioned about the code being unwieldy. I doubt that the majority of this code could be made any more manageable, due to the complexity of the task it is trying to perform.

Seems like this task just requires a lot of code

Thanks much for your help!

--
Mike

Why make it simple and efficient when it can be complex and wonderful?