Need help with some VBA on Excel & Word

[dumb and frustrated]

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]

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

 

[yves]

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]

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...

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

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]

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

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...

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

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