using a macro to format currency and dates in a table cell

[Chris]

Hi,

I've been pondering this one for days now. I have text in a table cell that
I always have to select and bold. There are 3 dollar values and one date
that need to be formatted to bold. The date also needs to be changed from
##/##/#### to a full date format. Below is a sample of how the text looks in
the table cell. It is imported from an access database and can't be
formatted prior to being imported into the word document.

This box contains $4.00 three dollars amounts $457.00 and one date that need
changing $9990.85 in some way.

I've tried using Mid, FormatCurrency, Int and a few other VBA functions and
commands, but the closest I got was to bold one dollar figure with no control
over the size of the figure (it bolded 2 letters to the left of the dollar) -
then I forgot what I had done after trying other methods!

It's just a bit beyond my VBA skills to write it. Can anyone help?

 

[Helmut Weber]

Hi Chris,

This box contains $4.00 three dollars amounts $457.00 and one date that need
changing $9990.85 in some way.

Not too clear how this is arranged in the table cell?
Maybe an optical representation of a table cell would help,
like the following. You may use chr(182) ¶ [alt] 0182 to indicate
a paragraph mark.
Before ?
+------------+
! !
! !
! !
! !
+------------+
After ?
+------------+
! !
! !
! !
! !
+------------+
---
Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
http://word.mvps.org/

 

[Chris]

Thanks Helmt.

After trawling through blogs all night, I came across some inelegant
solutions to my own question. To search for the dollar values and replace
them I input this:

$*>.<*>

Then used the format option to bold them all. In the real document I have
multiple tables, so I also recorded a little macro to goto the table first.

Then I found another wonderful blog that showed how to select a whole date
like this:

[0-9]{2}/[0-9]{2}/[0-9]{4}

I had to fudge the next bit by manually typing the date, because try as I
might, I couldn't find any VBA code that would change the date format to the
long date. I know this is only due to my not knowing enough about VBA, so if
you know how to change the date format of a selected date, please share! Or,
if you know how to put in a string in the Replace box that would do it, that
would be great too!

Those two wonderful strings above are a bit of hidden treasure in the
Microsoft box of tricks.

Cheers from Queensland, Australia

Chris

Hi Chris,

This box contains $4.00 three dollars amounts $457.00 and one date that need
changing $9990.85 in some way.

Not too clear how this is arranged in the table cell?
Maybe an optical representation of a table cell would help,
like the following. You may use chr(182) ¶ [alt] 0182 to indicate
a paragraph mark.
Before ?
+------------+
! !
! !
! !
! !
+------------+
After ?
+------------+
! !
! !
! !
! !
+------------+
---
Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98

 

[Helmut Weber]

Hi Chris,
as there are lots of regional date formats,
describing what the format-funktion can do is difficult.
In principle, it looks like this:
Sub TestDate()
Resetsearch
Selection.ExtendMode = False
Selection.HomeKey unit:=wdStory
With Selection.find
.Text = "[0-9]{2}/[0-9]{2}/[0-9]{4}"
.MatchWildcards = True
While .Execute
Selection.Text = Format(Selection.Text, "DDDD d. MMMM YYYY")
Selection.Collapse direction:=wdCollapseEnd
Wend
End With
Resetsearch
End Sub
Sub Resetsearch()
With Selection.find
.Parent.Collapse
.ClearFormatting
.Replacement.ClearFormatting
.Text = ""
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
.Execute Replace:=wdReplaceAll
End With
End Sub
You have to shuffle the parts of "DDDD d. MMMM YYYY" a bit around,
probably, and restrict the selection to your tables.
---
Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
http://word.mvps.org/

 

[Chris]

Helmut, you're a bloody legend mate. I spent hours tinkering with the
Selection command to get it to format the text, but couldn't write it! I'll
show you my macro that I wrote. I only had to use one line from your text,
but it is like finding the golding fleece.

Sub DueDate()
'
' fielddate Macro
' Macro recorded 16/10/2004 by Chris Robson
'
Selection.Find.ClearFormatting
With Selection.Find
.Text = "[0-9]{2}/[0-9]{1,2}/[0-9]{4}"
.MatchWildcards = True
End With
Selection.Find.Execute
Selection.Font.Bold = wdToggle
Selection.Text = Format(Selection.Text, "DD MMMM YYYY")

End Sub

Many, many thanks again Helmut.

Cheers from sunny Queensland!

Chris