select last dollar figure in word document and copy to string

[Ryan Kassel]

My pseudo-code is the following:

Search backwards from the end of the document for the "$" char
Position cursor after the "$"
Select the number which immediately follows (like 621.25)
Save in text string then cast to single/currency

I am basically just trying to figure out how to move around efficiently,
find/select what I'm looking for and save it to a string.

I open the word doc and do:
wdDoc.Bookmarks("\EndOfDoc").Select
which is nice and tidy and takes me to the end of the document. But I still
need to slurp up the last number in the document. <grunting> Please Help.

Thanks,

Ryan

 

[Jay Freedman]

My pseudo-code is the following:

Search backwards from the end of the document for the "$" char
Position cursor after the "$"
Select the number which immediately follows (like 621.25)
Save in text string then cast to single/currency

I am basically just trying to figure out how to move around efficiently,
find/select what I'm looking for and save it to a string.

I open the word doc and do:
wdDoc.Bookmarks("\EndOfDoc").Select
which is nice and tidy and takes me to the end of the document. But I still
need to slurp up the last number in the document. <grunting> Please Help.

Thanks,

Ryan

Hi Ryan,

First, the way to find strings of unknown length consisting of
specific characters is usually to use a wildcard search
(http://www.word.mvps.org/FAQs/General/UsingWildcards.htm and
http://www.gmayor.com/replace_using_wildcards.htm).

Second, it's nearly always more efficient to use a Range object than
to physically move the cursor (the VBA Selection) through a document.
For one thing, users don't like to see the document flashing past
their eyes and then dumping them at some unknown spot; for another, in
large documents the scrolling can take a long time and slow your macro
to a crawl.

Try this macro, and post back if there's something that doesn't make
sense to you.

Sub FindLastAmount()
Dim oRg As Range
Dim strNumbers As String

' use a Range object instead of the Selection
Set oRg = ActiveDocument.Range
' start at end of document
oRg.Collapse direction:=wdCollapseEnd

With oRg.Find
.MatchWildcards = True
.Text = "$[0-9,.]@"
.Forward = False ' search backward
.Format = False
.Wrap = wdFindStop
If .Execute Then
' when .Execute returns True,
' the Range object covers the
' found text
strNumbers = oRg.Text

' if comma is last char, remove it
Do While Right(strNumbers, 1) = ","
strNumbers = Left( _
strNumbers, Len(strNumbers) - 1)
Loop

' remove $
strNumbers = Right( _
strNumbers, Len(strNumbers) - 1)

MsgBox strNumbers
End If
End With
End Sub

 

[Ryan Kassel]

Hey, thank a lot! You code works as advertised.

One thing is that it finds the value but most of the time. Sometimes it
doesn't get the value or overlooks the value. Might be because there are
several spaces in between the dollar sign and the dollar value in some cases,
so it could be like:
$ 225.00

The problem is that some (most) of these values are getting picked up
(found) while some others are not. Could it have to do with the document
formatting? Any ideas? Here's the function as of right now:

Function FindLastAmount(WordDoc As Word.Document) As String
Dim strNumbers As String
Dim oRg As Word.Range
Set oRg = WordDoc.Application.ActiveDocument.Range

'trying some stuff here
oRg.Select
WordDoc.ActiveWindow.Selection.ClearFormatting

' start at end of document
oRg.Collapse Direction:=wdCollapseEnd

With oRg.Find
.ClearFormatting
.MatchWildcards = True
.Text = "$[0-9,.]@"
.Forward = False ' search backward
.Format = False
.Wrap = wdFindStop
If .Execute Then
strNumbers = oRg.Text
Do While Right(strNumbers, 1) = ","
strNumbers = Left( _
strNumbers, Len(strNumbers) - 1)
Loop

' remove $
strNumbers = Right( _
strNumbers, Len(strNumbers) - 1)

'return value
FindLastAmount = strNumbers
End If
End With
End Function
'Courtesty of Jay Freedman Microsoft, Word MVP -

Thanks again!
- Ryan

 

[Jay Freedman]

Hi Ryan,

An unfortunate problem with Word's wildcard feature is that there's no way
to say "zero or more of this character"; the best you can do is "one or
more". That makes things a bit more complicated, because you can't search in
one step for a $ followed by zero or more spaces followed by a number.

Here's what's necessary to deal with spaces between the $ and the numbers:

- Add a space character to the list of characters in square brackets in the
..Text expression:

.Text = "$[0-9,. ]@"

This will find a $ followed by *any* sequence of digits, commas, periods,
and spaces. The spaces could be before the numbers, after the numbers, or
even between the numbers.

- Add the Trim function in the "remove $" statement:

strNumbers = Trim(Right( _
strNumbers, Len(strNumbers) - 1))

This removes any spaces to the left of the first digit or to the right of
the last digit/comma/period

- This part is optional... With the new search expression, your code could
find two numbers separated by one or more spaces, like $ 225.00 234 .
If you're worried about that, add the following lines between the "remove $"
and the "return value":

Dim SpacePos As Long
SpacePos = InStr(strNumbers, " ")
If SpacePos > 0 Then
strNumbers = Left(strNumbers, SpacePos - 1)
End If

This will chop off the first remaining space and anything that follows it.
--
Regards,
Jay Freedman
Microsoft Word MVP

Hey, thank a lot! You code works as advertised.

One thing is that it finds the value but most of the time. Sometimes
it doesn't get the value or overlooks the value. Might be because
there are several spaces in between the dollar sign and the dollar
value in some cases, so it could be like:
$ 225.00

The problem is that some (most) of these values are getting picked up
(found) while some others are not. Could it have to do with the
document formatting? Any ideas? Here's the function as of right now:

Function FindLastAmount(WordDoc As Word.Document) As String
Dim strNumbers As String
Dim oRg As Word.Range
Set oRg = WordDoc.Application.ActiveDocument.Range

'trying some stuff here
oRg.Select
WordDoc.ActiveWindow.Selection.ClearFormatting

' start at end of document
oRg.Collapse Direction:=wdCollapseEnd

With oRg.Find
.ClearFormatting
.MatchWildcards = True
.Text = "$[0-9,.]@"
.Forward = False ' search backward
.Format = False
.Wrap = wdFindStop
If .Execute Then
strNumbers = oRg.Text
Do While Right(strNumbers, 1) = ","
strNumbers = Left( _
strNumbers, Len(strNumbers) - 1)
Loop

' remove $
strNumbers = Right( _
strNumbers, Len(strNumbers) - 1)

'return value
FindLastAmount = strNumbers
End If
End With
End Function
'Courtesty of Jay Freedman Microsoft, Word MVP - FAQ:
http://word.mvps.org

Thanks again!
- Ryan

 

[Ryan Kassel]

Fantastic, Jay!

This seems to work great now! So, now I'll write a few more functions to
throw the contents of my list box into a excel spreadsheet, pop up a msgbox
with the total, etc. Wheee, we're off to the races!

.... Yes, on the one hand, I agree that not being able to search for "zero of
more" of something is annoying, and I find it to be a major weakness in VBA
regular expression search theory - although, as you've shown, there are other
ways to skin the cat. On the other hand, I find the lazy search (instead of
the usual greedy search) easier to predict on an intuitive level. I'm sure
there are reasons why perl/fooNix developers chose greedy search, I'll have
to check that out.

Thank you again for all of your help.

- Ryan
.-.
/ \ .-.
/ \ / \ .-. . Ryan Eric Kassel
---------\--------------\----------\------ ryan<0x40>kassel.cc
\ / \ / `-'
\ / `-'
`-'

Hi Ryan,

An unfortunate problem with Word's wildcard feature is that there's no way
to say "zero or more of this character"; the best you can do is "one or
more". That makes things a bit more complicated, because you can't search in
one step for a $ followed by zero or more spaces followed by a number.

Here's what's necessary to deal with spaces between the $ and the numbers:

- Add a space character to the list of characters in square brackets in the
..Text expression:

.Text = "$[0-9,. ]@"

This will find a $ followed by *any* sequence of digits, commas, periods,
and spaces. The spaces could be before the numbers, after the numbers, or
even between the numbers.

- Add the Trim function in the "remove $" statement:

strNumbers = Trim(Right( _
strNumbers, Len(strNumbers) - 1))

This removes any spaces to the left of the first digit or to the right of
the last digit/comma/period

- This part is optional... With the new search expression, your code could
find two numbers separated by one or more spaces, like $ 225.00 234 .
If you're worried about that, add the following lines between the "remove $"
and the "return value":

Dim SpacePos As Long
SpacePos = InStr(strNumbers, " ")
If SpacePos > 0 Then
strNumbers = Left(strNumbers, SpacePos - 1)
End If

This will chop off the first remaining space and anything that follows it.
--
Regards,
Jay Freedman
Microsoft Word MVP

Hey, thank a lot! You code works as advertised.

One thing is that it finds the value but most of the time. Sometimes
it doesn't get the value or overlooks the value. Might be because
there are several spaces in between the dollar sign and the dollar
value in some cases, so it could be like:
$ 225.00

The problem is that some (most) of these values are getting picked up
(found) while some others are not. Could it have to do with the
document formatting? Any ideas? Here's the function as of right now:

Function FindLastAmount(WordDoc As Word.Document) As String
Dim strNumbers As String
Dim oRg As Word.Range
Set oRg = WordDoc.Application.ActiveDocument.Range

'trying some stuff here
oRg.Select
WordDoc.ActiveWindow.Selection.ClearFormatting

' start at end of document
oRg.Collapse Direction:=wdCollapseEnd

With oRg.Find
.ClearFormatting
.MatchWildcards = True
.Text = "$[0-9,.]@"
.Forward = False ' search backward
.Format = False
.Wrap = wdFindStop
If .Execute Then
strNumbers = oRg.Text
Do While Right(strNumbers, 1) = ","
strNumbers = Left( _
strNumbers, Len(strNumbers) - 1)
Loop

' remove $
strNumbers = Right( _
strNumbers, Len(strNumbers) - 1)

'return value
FindLastAmount = strNumbers
End If
End With
End Function
'Courtesty of Jay Freedman Microsoft, Word MVP - FAQ:
http://word.mvps.org

Thanks again!
- Ryan