How to do a Pattern Replace in VBA?

[prabha_friend]

Why the following is not giving the desired result:

msgbox replace("The Date is 05-02-2015","??-??-????",format(Date,"dd-mm-yyyy"))

How to achieve that in VBA?

 

[pr2-eugin]

What do you want the MsgBox to actually show? Replace function will try to find "??-??-????" in the String "The Date is 05-02-2015", if found will replace it with 05-02-2015

In the example you have given the ??-??-???? is not found, so no replacement takes place.

 

[prabha_friend]

The date is 09-02-2015

 

[prabha_friend]

Paul. I want to use a Pattern Matching here. Not possible?

 

[pr2-eugin]

Here is a link with RegEx that could be helpful.

http://stackoverflow.com/questions/7929205/vba-regular-expression-to-match-date

 

[jdraw]

Here's a good start if you want to pursue regular expressions with Access vba.

Good luck.

 

[prabha_friend]

Hi Paul and jd,
Thank you for referring that. I believe it will be a new thing that I (must) learn. Anyway just want to confirm: Actually the pattern matching is working in the VBE Editor manually when you search and replace but not through code. Can I take it as a confirmation that when we want to use pattern search and replace (using regEx) we can achieve it only through using the VB Script reference. Right?

Thanks in Advance...

Regards,
Prabhakaran

 

[jdraw]

You need a reference as in the attached.

 

[prabha_friend]

Why am I getting the following error?
Run-time error '5018':
Method 'Test' of object 'IRegExp2' failed

in the following code:

Sub RegEx()
Dim myregex As New RegExp
With myregex
.Pattern = "??-???-????"
.IgnoreCase = False
.Global = False
End With
MsgBox myregex.Test("Date is: 15-Jan-2015")
End Sub

How to replace that 15-Jan-2015 with current date 09-Feb-2015 using a pattern match?

 

[pr2-eugin]

Your patter is wrong. You have to specify the pattern.

Sub RegEx()
    Dim myregex [COLOR=Red][B]As RegExp[/B][/COLOR]
    Set myregex = New RegExp
    With myregex
        .Pattern = "((0[1-9]|[12][0-9]|3[01])[-][A-Z][a-z][a-z])[-](19|20)[0-9]{2}"
        [COLOR=Green]'Use the Following if it is Numeric month.
        '.Pattern = "((0[1-9]|[12][0-9]|3[01])[-]0[1-9]|1[012])[-](19|20)[0-9]{2}"[/COLOR]
        .Global = False
    End With
    MsgBox myregex.Replace("Date is: 15-Jan-2015", Format(Date, "dd-mmm-yyyy"))
End Sub

 

[BlueIshDan]

MsgBox "The Date is " & CStr(format(CDate(Right("The Date is 05-02-2015", 10)), "dd-mm-yyyy"))

Something like this?

 

[prabha_friend]

Hi Paul. Thanks for your pattern. How to modify this pattern to pick for both:

02-Jan-2015
and
2-Jan-2015

I know we have to add/modify this: (0[1-9]|[12][0-9]|3[01]) but how? Thanks in advance.

 

[prabha_friend]

[1-9]|0[1-9]

Solved my purpose.