Text Maniplation in VBA Before Being Put Into a Document

[Shane]

Hi.

I'm into an area that has me stumped. I have a template that reads
database info via DDE. A field that holds fax numbers is displayed to
the user as (07) 6666 6666. Due to the nature of the database the
text that is passed to Word is formated 1-07)-6666-6666. I cannot
change the way the data is passed over due to the relational nature of
the records and table structure so I'd like to reformat it via VBA.

What I don't know how to do is reformat the info before it hits the
document i.e replace and remove characters. What I would like to do
is take the data when a form loads (no problem as I can do that),
manipulate it into the format required (this I can't do) then display
it in the form for the user (no problems here either).

Any help would be appreciated.

Regards
Shane

 

[Helmut Weber]

Hi Shane,

I think this is not enough information
for providing a good anwer.

1-07)-6666-6666 --> (07) 6666 6666

It is hardly possible to find out a pattern (!)
from only one Example.

 

[Rob]

Well, you could always do something naughty like:

FaxString = Replace(FaxString, "1-", "(")

But, like Helmut said, more info would be good. I'd be interested in
knowing why it's happening and would like to know how you're getting the
value and what data type you're putting it into and how.

 

[Ed]

Hi.

I'm into an area that has me stumped. I have a template that reads
database info via DDE. A field that holds fax numbers is displayed to
the user as (07) 6666 6666. Due to the nature of the database the
text that is passed to Word is formated 1-07)-6666-6666. I cannot
change the way the data is passed over due to the relational nature of
the records and table structure so I'd like to reformat it via VBA.

What I don't know how to do is reformat the info before it hits the
document i.e replace and remove characters. What I would like to do
is take the data when a form loads (no problem as I can do that),
manipulate it into the format required (this I can't do) then display
it in the form for the user (no problems here either).

Any help would be appreciated.

Regards
Shane

Hi, Shane. Try looking through these references to see if any of them
can help.
Ed

http://word.mvps.org/faqs/macrosvba/replacecharinstringcontent.htm
http://word.mvps.org/FAQS/MacrosVBA.htm#Find&Replace

http://msdn2.microsoft.com/en-us/library/aa201315(office.11).aspx
http://msdn2.microsoft.com/en-us/library/aa189291(office.10).aspx
http://www.microsoft.com/technet/prodtechnol/office/office2000/solution/part2/part2.mspx?mfr=true

 

[Greg Maxey]

Here is one way you might consider:

Sub ScratchMacor()
Dim myString As String
myString = "1-07)-6666-6666"
myString = Replace(Replace(myString, "1-", "("), "-", " ")
MsgBox myString
End Sub

 

[Shane]

I'm using a CRM product called Goldmine. In it there is primary
contact page that holds contact info for suppliers, clients, etc. On
that page is a fax number. "Attached" to each contact record is a
sub-record for additional contacts. This is another table with a one
to many relationship with the primary contact table. The info here
may be staff that work for the supplier listed in the primary contact
page. This table also stores a fax number.

Due to the nature of the table structure and the relationship issues
you launch the document from within Goldmine and tell Goldmine which
record you want to deal with. Goldmine uses an internal macro (not
the same as a Word macro) to extract the relevant fax info for the
record identified when you launched the template. The template uses
VBA and a DDE request to extract the fax number. The DDE request is
along the lines of "DDEAUTO Goldmine Data &Fax" where &Fax pulls the
fax field for the appropriate table (primary or secondary). Sorry if
this isn't clear but usenet isn't a good medium for explaining table
relationships such as Goldmine uses.

I have the fax info entered in Goldmine in the format (07) 1234 5678.
When GM passes the data for a &FAX request it changes it as 1-07)-1234
5678. (one less - that I originally mentioned (sorry)). I cannot
change this behaviour. Changing the format of the fax number to 07
1234 5678 just ends up removing one of the digits in the first group
of four and still stuffs up the formatting.

I can access the fax number field on the main page directly and the
formatting doesn't get changed, it's only when I use the macro to
access the attached secondary contact details.

The Fax number fields are string type.

As far as patterns go...I'm storing data as (xx) yyyy zzzz and is
passed to an external app as 1-xx)-yyyy zzzz. That's the pattern.
(xx) is the area code. If the pattern changes then the user hasn't
followed internal procedure and it's not my problem.

Again, there is nothing in Goldmine that can be manipulated to change
this behaviour so I figured the easiest way was to use VBA to
manipulate the text string as it comes across in a standard,
consistent format. If it doesn't the user hasn't entered the info
correctly.

Cheers

 

[Shane]

Thanks Ed and Greg.

Greg, your code did it. I'll pass this on to other people as it's an
issue that's been around for a while.

Cheers