Need help with some VBA on Excel & Word

Let me post this again because I don't think I explained this well the
first go around:
The code below opens up a word doc, looks for the word "Primary Key:"
then gives me the corresponding answer. However, I require a tweak
since there are times when the word "Primary Key:" is followed by
unwanted spaces, no spaces, or tabs. It works great if there is one
space, but it doesn't work if there are no spaces after the colon, or
there are more than one space.
I have marked the problem area with UPPER CASE comments.

I have tried tweaking the code myself, but I'm new to this VBA.

' Get Word doc
'strDoc = "C:\MyDocName"
strDoc = "C:\Documents and Settings\edward.millis\Desktop\TestDoc.doc"
Set docWord = appWord.Documents.Open(strDoc)
' Set Word range
Set rngWord = docWord.Content
' Find first term
rngWord.Find.Execute _

'******PROBLEM STARTS HERE***********
FindText:="Primary Key:", MatchWildcards:=False, _
Wrap:=wdFindStop, Forward:=True
' Reset range to get number
rngWord.Collapse wdCollapseEnd
Do
rngWord.MoveStart Unit:=wdCharacter, Count:=1
numKey = Trim(rngWord.Text)
Loop Until Right(numKey, 1) <> " "
rngWord.MoveEnd Unit:=wdWord, Count:=1
' Put number into variable
numKey = Trim(rngWord.Text)
Edit/Delete Message

Ed
Fri Aug 11 17:37:32 CDT 2006

Try replacing
> Do
> rngWord.MoveStart Unit:=wdCharacter, Count:=1
> numKey = Trim(rngWord.Text)
> Loop Until Right(numKey, 1) <> " "
with
rngWord.MoveStartUntil Cset:="0123456789", Count:=wdForward
rngWord.MoveEnd Unit:=wdWord, Count:=1
numKey = Trim(rngWord.Text)

I didn't (and won't for a few days) have time to test it, but it should move
the collapse range until it hits a number, then extend the range to
encompass the whole number. The Trim will remove extra spaces, but I don't
know if it will take off tabs - I don't think so, but I don't think the
wdWord will include any tabs.

(I also wonder how you're managing to access a test doc on _my_ computer.
8>O )
> 'strDoc = "C:\MyDocName"
> strDoc = "C:\Documents and Settings\edward.millis\Desktop\TestDoc.doc"

Ed

"dumb and frustrated" <dumb and frustrated@discussions.microsoft.com> wrote
in message news:C7BE5BCE-3DC3-480B-821C-9369998386D7@microsoft.com...
> Let me post this again because I don't think I explained this well the
> first go around:
> The code below opens up a word doc, looks for the word "Primary Key:"
> then gives me the corresponding answer. However, I require a tweak
> since there are times when the word "Primary Key:" is followed by
> unwanted spaces, no spaces, or tabs. It works great if there is one
> space, but it doesn't work if there are no spaces after the colon, or
> there are more than one space.
> I have marked the problem area with UPPER CASE comments.
>
> I have tried tweaking the code myself, but I'm new to this VBA.
>
> ' Get Word doc
> 'strDoc = "C:\MyDocName"
> strDoc = "C:\Documents and Settings\edward.millis\Desktop\TestDoc.doc"
> Set docWord = appWord.Documents.Open(strDoc)
> ' Set Word range
> Set rngWord = docWord.Content
> ' Find first term
> rngWord.Find.Execute _
>
> '******PROBLEM STARTS HERE***********
> FindText:="Primary Key:", MatchWildcards:=False, _
> Wrap:=wdFindStop, Forward:=True
> ' Reset range to get number
> rngWord.Collapse wdCollapseEnd
> Do
> rngWord.MoveStart Unit:=wdCharacter, Count:=1
> numKey = Trim(rngWord.Text)
> Loop Until Right(numKey, 1) <> " "
> rngWord.MoveEnd Unit:=wdWord, Count:=1
> ' Put number into variable
> numKey = Trim(rngWord.Text)
> Edit/Delete Message

yves
Sun Aug 13 08:04:21 CDT 2006

Hi D&F,

I would change the

FindText:="Primary Key:", MatchWildcards:=False

into a wildcard search for "Primary Key:" plus * plus some number. That
would simplify the code.

FindText:="Primary Key:*[0-9]", MatchWildcards:=True

if the search is successful, then do the Collapse forward, move back
one character, move the selection's end one word forward and there you
are.

Cheers,
Yves Champollion

dumb and frustrated wrote:
> Let me post this again because I don't think I explained this well the
> first go around:
> The code below opens up a word doc, looks for the word "Primary Key:"
> then gives me the corresponding answer. However, I require a tweak
> since there are times when the word "Primary Key:" is followed by
> unwanted spaces, no spaces, or tabs. It works great if there is one
> space, but it doesn't work if there are no spaces after the colon, or
> there are more than one space.
> I have marked the problem area with UPPER CASE comments.
>
> I have tried tweaking the code myself, but I'm new to this VBA.
>
> ' Get Word doc
> 'strDoc = "C:\MyDocName"
> strDoc = "C:\Documents and Settings\edward.millis\Desktop\TestDoc.doc"
> Set docWord = appWord.Documents.Open(strDoc)
> ' Set Word range
> Set rngWord = docWord.Content
> ' Find first term
> rngWord.Find.Execute _
>
> '******PROBLEM STARTS HERE***********
> FindText:="Primary Key:", MatchWildcards:=False, _
> Wrap:=wdFindStop, Forward:=True
> ' Reset range to get number
> rngWord.Collapse wdCollapseEnd
> Do
> rngWord.MoveStart Unit:=wdCharacter, Count:=1
> numKey = Trim(rngWord.Text)
> Loop Until Right(numKey, 1) <> " "
> rngWord.MoveEnd Unit:=wdWord, Count:=1
> ' Put number into variable
> numKey = Trim(rngWord.Text)
> Edit/Delete Message

dumbandfrustrated
Tue Aug 15 14:29:02 CDT 2006

Thanks, yves...that works for all the docs I've tested so far. I will
continue testing, but thanks very much!

**NOTE for how I changed the code (in addition to Yves' suggestion): I've
changed
rngWord.MoveStart Unit:=wdCharacter, Count:=1
to
rngWord.MoveStart Unit:=wdCharacter, Count:=-1

and it worked great.
Ed,
I tried your code, and you seem a little busy, but the wdWord DOES pick up
the TAB. If you have a suggestion for others to see...
"yves@champollion.net" wrote:

> Hi D&F,
>
> I would change the
>
> FindText:="Primary Key:", MatchWildcards:=False
>
> into a wildcard search for "Primary Key:" plus * plus some number. That
> would simplify the code.
>
> FindText:="Primary Key:*[0-9]", MatchWildcards:=True
>
> if the search is successful, then do the Collapse forward, move back
> one character, move the selection's end one word forward and there you
> are.
>
> Cheers,
> Yves Champollion
>
> dumb and frustrated wrote:
> > Let me post this again because I don't think I explained this well the
> > first go around:
> > The code below opens up a word doc, looks for the word "Primary Key:"
> > then gives me the corresponding answer. However, I require a tweak
> > since there are times when the word "Primary Key:" is followed by
> > unwanted spaces, no spaces, or tabs. It works great if there is one
> > space, but it doesn't work if there are no spaces after the colon, or
> > there are more than one space.
> > I have marked the problem area with UPPER CASE comments.
> >
> > I have tried tweaking the code myself, but I'm new to this VBA.
> >
> > ' Get Word doc
> > 'strDoc = "C:\MyDocName"
> > strDoc = "C:\Documents and Settings\edward.millis\Desktop\TestDoc.doc"
> > Set docWord = appWord.Documents.Open(strDoc)
> > ' Set Word range
> > Set rngWord = docWord.Content
> > ' Find first term
> > rngWord.Find.Execute _
> >
> > '******PROBLEM STARTS HERE***********
> > FindText:="Primary Key:", MatchWildcards:=False, _
> > Wrap:=wdFindStop, Forward:=True
> > ' Reset range to get number
> > rngWord.Collapse wdCollapseEnd
> > Do
> > rngWord.MoveStart Unit:=wdCharacter, Count:=1
> > numKey = Trim(rngWord.Text)
> > Loop Until Right(numKey, 1) <> " "
> > rngWord.MoveEnd Unit:=wdWord, Count:=1
> > ' Put number into variable
> > numKey = Trim(rngWord.Text)
> > Edit/Delete Message
>
>

Ed
Tue Aug 15 16:34:51 CDT 2006

Not busy - you just caught me on the edge of a weekend when I wasn't going
to be near a computer.

I just tested

rngWord.Collapse wdCollapseEnd
rngWord.MoveStartUntil Cset:="0123456789", Count:=wdForward
rngWord.MoveEnd Unit:=wdWord, Count:=1
numkey = Trim(rngWord.Text)
MsgBox numkey

on

Primary Key:<sp><sp>12

Primary Key:<tab>34

Primary Key:<sp><tab><sp><tab>56

Primary Key:<sp><sp><tab><tab>78

Primary Key:<sp><tab><tab><sp>89

<sp> = space character

<tab> = tab character



and it picked up the number every time. Now if there's tabs or spaces in
between the individual digits of the number, all bets are off! <g>



But I do like Yves solution because it grabs the number in the Find. If
you've got something working, go for it!



Ed



"dumb and frustrated" <dumbandfrustrated@discussions.microsoft.com> wrote in
message news:43337597-7ECF-402D-81C4-C0896D21536F@microsoft.com...
> Thanks, yves...that works for all the docs I've tested so far. I will
> continue testing, but thanks very much!
>
> **NOTE for how I changed the code (in addition to Yves' suggestion): I've
> changed
> rngWord.MoveStart Unit:=wdCharacter, Count:=1
> to
> rngWord.MoveStart Unit:=wdCharacter, Count:=-1
>
> and it worked great.
> Ed,
> I tried your code, and you seem a little busy, but the wdWord DOES pick up
> the TAB. If you have a suggestion for others to see...
> "yves@champollion.net" wrote:
>
>> Hi D&F,
>>
>> I would change the
>>
>> FindText:="Primary Key:", MatchWildcards:=False
>>
>> into a wildcard search for "Primary Key:" plus * plus some number. That
>> would simplify the code.
>>
>> FindText:="Primary Key:*[0-9]", MatchWildcards:=True
>>
>> if the search is successful, then do the Collapse forward, move back
>> one character, move the selection's end one word forward and there you
>> are.
>>
>> Cheers,
>> Yves Champollion
>>
>> dumb and frustrated wrote:
>> > Let me post this again because I don't think I explained this well the
>> > first go around:
>> > The code below opens up a word doc, looks for the word "Primary Key:"
>> > then gives me the corresponding answer. However, I require a tweak
>> > since there are times when the word "Primary Key:" is followed by
>> > unwanted spaces, no spaces, or tabs. It works great if there is one
>> > space, but it doesn't work if there are no spaces after the colon, or
>> > there are more than one space.
>> > I have marked the problem area with UPPER CASE comments.
>> >
>> > I have tried tweaking the code myself, but I'm new to this VBA.
>> >
>> > ' Get Word doc
>> > 'strDoc = "C:\MyDocName"
>> > strDoc = "C:\Documents and Settings\edward.millis\Desktop\TestDoc.doc"
>> > Set docWord = appWord.Documents.Open(strDoc)
>> > ' Set Word range
>> > Set rngWord = docWord.Content
>> > ' Find first term
>> > rngWord.Find.Execute _
>> >
>> > '******PROBLEM STARTS HERE***********
>> > FindText:="Primary Key:", MatchWildcards:=False, _
>> > Wrap:=wdFindStop, Forward:=True
>> > ' Reset range to get number
>> > rngWord.Collapse wdCollapseEnd
>> > Do
>> > rngWord.MoveStart Unit:=wdCharacter, Count:=1
>> > numKey = Trim(rngWord.Text)
>> > Loop Until Right(numKey, 1) <> " "
>> > rngWord.MoveEnd Unit:=wdWord, Count:=1
>> > ' Put number into variable
>> > numKey = Trim(rngWord.Text)
>> > Edit/Delete Message
>>
>>