String pattern matching with the VBA Replace function?

[Lauren Wilson]

Hi folks,

We have a need to replace sub strings in certain message text. We use
the Office Assistant to display help and often use the imbedded
formatting commands. Those of you who have used them know they look
like this: "{cf 5}" or "{cf 0}" or "{ul 1}" or "{ul 0}", etc. The
commonality they have is that they are always 6 charters long and
always begin and end with curly brackets.

This all works great if the user is running the retail version of
Access. Unfortunately, some of our users run Access Runtime -- in
which case the code simply writes the same message text (including the
imbedded formatting strings) to a standard form window. Of course, we
do this because. for some inexplicable reason, Microsoft will not
include Office Assistant functionality when an Access app is running
with Access Runtime.

So, we need to pre-process the message text for Runtime users and
remove all of the formatting strings. Here is what we have tried:

lblTxt1 = Replace(lblTxt1, "{", "", , 6, vbBinaryCompare)

OR,

lblTxt1 = Replace(lblTxt1, "{", "", , 6, vbTextCompare)

OR,

lblTxt1 = Replace(lblTxt1, "{????}")

NONE of these approaches work. Is there any way to use pattern
matching in combination with the Replace function?

I am praying that I won't be forced to use brute force Instr functions
to remove these formatting strings at runtime.

Any suggestions?

Thanks,

-- LW

[Lyle Fairfield]

Lauren Wilson <no****@nospam.com> wrote in
news:48********************************@4ax.com:

Hi folks,

We have a need to replace sub strings in certain message text. We use
the Office Assistant to display help and often use the imbedded
formatting commands. Those of you who have used them know they look
like this: "{cf 5}" or "{cf 0}" or "{ul 1}" or "{ul 0}", etc. The
commonality they have is that they are always 6 charters long and
always begin and end with curly brackets.

This all works great if the user is running the retail version of
Access. Unfortunately, some of our users run Access Runtime -- in
which case the code simply writes the same message text (including the
imbedded formatting strings) to a standard form window. Of course, we
do this because. for some inexplicable reason, Microsoft will not
include Office Assistant functionality when an Access app is running
with Access Runtime.

So, we need to pre-process the message text for Runtime users and
remove all of the formatting strings. Here is what we have tried:

lblTxt1 = Replace(lblTxt1, "{", "", , 6, vbBinaryCompare)

OR,

lblTxt1 = Replace(lblTxt1, "{", "", , 6, vbTextCompare)

OR,

lblTxt1 = Replace(lblTxt1, "{????}")

NONE of these approaches work. Is there any way to use pattern
matching in combination with the Replace function?

I am praying that I won't be forced to use brute force Instr functions
to remove these formatting strings at runtime.

Any suggestions?

Thanks,

-- LW

I know you never use anything I suggest Lauren but, perhaps, this will
help someone else who comes upon this thread.

I have a function I use to strip HTML tags from text:

Public Function StripHTMLTags( _
ByVal HTML As String) As String
Dim a() As String
Dim v As Variant
a() = Split(HTML, "<")
For Each v In a
StripHTMLTags = StripHTMLTags & Mid$(v, InStr(v, ">") + 1)
Next v
End Function

It's easily revised to do what I !!!!think!!!! you want to do:
Public Function StripOAFormattingCommands( _
ByVal OAString As String) As String
Dim a() As String
Dim v As Variant
a() = Split(OAString, "{")
For Each v In a
StripOAFormattingCommands = StripOAFormattingCommands & Mid$(v,
InStr(v, "}") + 1)
Next v
End Function

When applied to your sentence Those of you who have used them know they look like this: "{cf 5}" or
"{cf 0}" or "{ul 1}" or "{ul 0}", etc.

StripOAFormattingCommands returns
Those of you who have used them know they look like this: "" or "" or ""
or "", etc.
Perhaps, one will want to add something to remove the [""]'s.

BTW, the fourth parameter of the Replace Function specifies the character
at which to begin the Replace, not the number of characters to be
replaced:

start
Optional. Position within expression where substring search is to begin.
If omitted, 1 is assumed.
--
Lyle Fairfield

[Lauren Wilson]

On Tue, 31 Jan 2006 19:39:02 GMT, Lyle Fairfield
<ly***********@aim.com> wrote:

Lauren Wilson <no****@nospam.com> wrote in
news:48********************************@4ax.com :

Hi folks,

We have a need to replace sub strings in certain message text. We use
the Office Assistant to display help and often use the imbedded
formatting commands. Those of you who have used them know they look
like this: "{cf 5}" or "{cf 0}" or "{ul 1}" or "{ul 0}", etc. The
commonality they have is that they are always 6 charters long and
always begin and end with curly brackets.

This all works great if the user is running the retail version of
Access. Unfortunately, some of our users run Access Runtime -- in
which case the code simply writes the same message text (including the
imbedded formatting strings) to a standard form window. Of course, we
do this because. for some inexplicable reason, Microsoft will not
include Office Assistant functionality when an Access app is running
with Access Runtime.

So, we need to pre-process the message text for Runtime users and
remove all of the formatting strings. Here is what we have tried:

lblTxt1 = Replace(lblTxt1, "{", "", , 6, vbBinaryCompare)

OR,

lblTxt1 = Replace(lblTxt1, "{", "", , 6, vbTextCompare)

OR,

lblTxt1 = Replace(lblTxt1, "{????}")

NONE of these approaches work. Is there any way to use pattern
matching in combination with the Replace function?

I am praying that I won't be forced to use brute force Instr functions
to remove these formatting strings at runtime.

Any suggestions?

Thanks,

-- LW

I know you never use anything I suggest Lauren

I don't? Lyle, I'm hurt that you think I would be so callous. At the
moment I don't KNOW if I have or have not used any of your
suggestions, but I ALWAYS appreciate the efforts of you and others who
post such helpful things here. The fact is that I learn new things
from many posts whether or not I end up using the exact technique
described in one. Please Lyle, never assume you are not appreciated.

I will DEFINITELY give the code below a try. Thanks very much.
but, perhaps, this will
help someone else who comes upon this thread.

I have a function I use to strip HTML tags from text:

Public Function StripHTMLTags( _
ByVal HTML As String) As String
Dim a() As String
Dim v As Variant
a() = Split(HTML, "<")
For Each v In a
StripHTMLTags = StripHTMLTags & Mid$(v, InStr(v, ">") + 1)
Next v
End Function

It's easily revised to do what I !!!!think!!!! you want to do:
Public Function StripOAFormattingCommands( _
ByVal OAString As String) As String
Dim a() As String
Dim v As Variant
a() = Split(OAString, "{")
For Each v In a
StripOAFormattingCommands = StripOAFormattingCommands & Mid$(v,
InStr(v, "}") + 1)
Next v
End Function

When applied to your sentence

Those of you who have used them know they look like this: "{cf 5}" or
"{cf 0}" or "{ul 1}" or "{ul 0}", etc.

StripOAFormattingCommands returns
Those of you who have used them know they look like this: "" or "" or ""
or "", etc.
Perhaps, one will want to add something to remove the [""]'s.

BTW, the fourth parameter of the Replace Function specifies the character
at which to begin the Replace, not the number of characters to be
replaced:

start
Optional. Position within expression where substring search is to begin.
If omitted, 1 is assumed.

[rkc]

Lyle Fairfield wrote:

BTW, the fourth parameter of the Replace Function specifies the character
at which to begin the Replace, not the number of characters to be
replaced:

start
Optional. Position within expression where substring search is to begin.
If omitted, 1 is assumed.

Not to mention any part of the string before the optional start position
is dropped.

[Lauren Wilson]

WOW Lyle! It worked perfectly with ZERO modification. You are
awesome. Thanks a bunch.

-- LW


On Tue, 31 Jan 2006 19:39:02 GMT, Lyle Fairfield
<ly***********@aim.com> wrote:

Lauren Wilson <no****@nospam.com> wrote in
news:48********************************@4ax.com :

Hi folks,

We have a need to replace sub strings in certain message text. We use
the Office Assistant to display help and often use the imbedded
formatting commands. Those of you who have used them know they look
like this: "{cf 5}" or "{cf 0}" or "{ul 1}" or "{ul 0}", etc. The
commonality they have is that they are always 6 charters long and
always begin and end with curly brackets.

This all works great if the user is running the retail version of
Access. Unfortunately, some of our users run Access Runtime -- in
which case the code simply writes the same message text (including the
imbedded formatting strings) to a standard form window. Of course, we
do this because. for some inexplicable reason, Microsoft will not
include Office Assistant functionality when an Access app is running
with Access Runtime.

So, we need to pre-process the message text for Runtime users and
remove all of the formatting strings. Here is what we have tried:

lblTxt1 = Replace(lblTxt1, "{", "", , 6, vbBinaryCompare)

OR,

lblTxt1 = Replace(lblTxt1, "{", "", , 6, vbTextCompare)

OR,

lblTxt1 = Replace(lblTxt1, "{????}")

NONE of these approaches work. Is there any way to use pattern
matching in combination with the Replace function?

I am praying that I won't be forced to use brute force Instr functions
to remove these formatting strings at runtime.

Any suggestions?

Thanks,

-- LW

I know you never use anything I suggest Lauren but, perhaps, this will
help someone else who comes upon this thread.

I have a function I use to strip HTML tags from text:

Public Function StripHTMLTags( _
ByVal HTML As String) As String
Dim a() As String
Dim v As Variant
a() = Split(HTML, "<")
For Each v In a
StripHTMLTags = StripHTMLTags & Mid$(v, InStr(v, ">") + 1)
Next v
End Function

It's easily revised to do what I !!!!think!!!! you want to do:
Public Function StripOAFormattingCommands( _
ByVal OAString As String) As String
Dim a() As String
Dim v As Variant
a() = Split(OAString, "{")
For Each v In a
StripOAFormattingCommands = StripOAFormattingCommands & Mid$(v,
InStr(v, "}") + 1)
Next v
End Function

When applied to your sentence

Those of you who have used them know they look like this: "{cf 5}" or
"{cf 0}" or "{ul 1}" or "{ul 0}", etc.

StripOAFormattingCommands returns
Those of you who have used them know they look like this: "" or "" or ""
or "", etc.
Perhaps, one will want to add something to remove the [""]'s.

BTW, the fourth parameter of the Replace Function specifies the character
at which to begin the Replace, not the number of characters to be
replaced:

start
Optional. Position within expression where substring search is to begin.
If omitted, 1 is assumed.