Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / August 2006

Tip: Looking for answers? Try searching our database.

Need help with some VBA on Excel & Word

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
dumb and frustrated - 11 Aug 2006 22:26 GMT
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 - 11 Aug 2006 23:37 GMT
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

> Let me post this again because I don't think I explained this well the
> first go around:
[quoted text clipped - 30 lines]
> numKey = Trim(rngWord.Text)
> Edit/Delete Message
yves@champollion.net - 13 Aug 2006 14:04 GMT
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:
[quoted text clipped - 30 lines]
> numKey = Trim(rngWord.Text)
> Edit/Delete Message
dumb and frustrated - 15 Aug 2006 20:29 GMT
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,
>
[quoted text clipped - 48 lines]
> > numKey = Trim(rngWord.Text)
> > Edit/Delete Message
Ed - 15 Aug 2006 22:34 GMT
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!
[quoted text clipped - 62 lines]
>> > numKey = Trim(rngWord.Text)
>> > Edit/Delete Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.