Retrieving phone numbers from Word documents using VBA

[Ray C]

Has anyone ever worked on VBA code that retrieves phone numbers from Word
documents?

 

[Jay Freedman]

Has anyone ever worked on VBA code that retrieves phone numbers from Word
documents?

I'm sure someone has, at one time or another. ;-)

The major difficulty is that there are so many ways a telephone number could be
written, with or without embedded spaces, hyphens, parentheses, nonbreaking
spaces.... In order to come up with code that works, you have to examine your
input documents and decide what formats you want to look for. Then you can write
one or more wildcard searches
(http://www.gmayor.com/replace_using_wildcards.htm) to find them in documents.

The next hurdle is, exactly what do you mean by "retrieve"? Do you want them
listed in a dialog, copied to another document, stored in a database... ?

When you have a better idea of what you want to do, start looking in Google for
likely search terms. For a start, you can just plug the subject line of this
post into the search box.

 

[Ray C]

Hi Jay,

Seeing that there are so many ways of entering a telephone number, my client
decided that we just look for all occurences of the area code and then taking
the area code plus the next 10 characters following the area code. I'm able
to find the occurences but can't get the 10 characters that follow.

Right now (after each find) I just pull out the entire sentence and I
Split() it into an array and simply pull out the element that contains the
area code. But the problem is that the Split() function sometimes places the
area code alone into one element because there can be a space after the area
code.

Here's my code:

For Each rngStory In objDocument.StoryRanges
With rngStory.Find
.ClearFormatting
.Text = "514"
.Wrap = wdFindStop
.Forward = True
End With
Do Until rngStory.Find.Execute = False
With rngStory.Duplicate
.Expand Unit:=wdSentence
myArray = Split(.Text, " ", -1, vbTextCompare)
For i = 0 To UBound(myArray)
If InStr(1, myArray(i), "514", vbTextCompare) <> 0
Then
If numTelFound < 3 Then
myWorksheet.Range("Y") &
intCurrentLine).Formula = myArray(i)
End If
End If
Next i
End With
Loop
Next rngStory


Now my client wants me to just find the occurences and pull out the 10
characters that follow, including the area code. How do I do that?

Thanks,

 

[Jay Freedman]

Hi Ray,

The client has just made things very simple for you.

I didn't quite follow what you're doing with the worksheet, so I just display
the phone number in a message box. Other than that, this should be complete:

Dim oRg As Range
Dim oRgStory As Range

For Each oRgStory In objDocument.StoryRanges
Set oRg = oRgStory.Duplicate

With oRg.Find
.ClearFormatting
.Text = "514"
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchWildcards = False

Do While .Execute
' include the next 10 characters
oRg.MoveEnd Unit:=wdCharacter, Count:=10
' do what is needed with the text
MsgBox oRg.Text
' prepare to search further
oRg.Collapse wdCollapseEnd
Loop

End With
Next oRgStory

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all
may benefit.

 

[Ray C]

Thanks Jay,

This seems to work for the client.
It's incredible how many different ways people write phone numbers.
With this simple logic, it guarantees that he gets everything. I even
extended it to 15 characters, because in some cases, there are extension
numbers.

Thanks!

Ray