MSAccess using a wildcard in the REPLACE function

Question

I'm trying to do something simple and I don't understand why it's not working. I'm really new to MS Access VBA.

I have a string in a textbox :

\\p9990cdc\C$\Temp

I want to turn it into :
C:\Temp

I'm trying :

strSelectedFile = Replace(strSelectedFile, "\\*\C$", "C:")

and it's not working.

Not sure why RegEx doesn't work either :

strSelectedFile = Replace(strSelectedFile, "\\[\w]\C$", "C:")

Everything is set properly so the problem lies exactly in that replace code, because if I try for example :

strSelectedFile = Replace(strSelectedFile, "C$", "C:")

It works and sucessfully replaces the C$ with C:

\p9990cdc\C:\Temp

How can I make this work?

Thanks a lot for your time!

score 2 · Answer 1 · answered Aug 20 '18 at 14:26

Replace doesn't do wildcards. You can implement your own function that does, or use regex using VBScript.RegEx.

I've written a small function that does this for you. Performance is suboptimal, however, and I've only done a little testing. It works for your sample input.

Public Function LikeReplace(strInput As String, strPattern As String, strReplace As String, Optional start As Long = 1)
    Dim LenCompare As Long
    Do While start <= Len(strInput)
        For LenCompare = Len(strInput) - start + 1 To 1 Step -1
            If Mid(strInput, start, LenCompare) Like strPattern Then
                strInput = Left(strInput, start - 1) & strReplace & Right(strInput, Len(strInput) - (start + LenCompare - 1))
            End If
        Next
        start = start + 1
    Loop
    LikeReplace = strInput
End Function

Using your inputs and swapping Replace with this LikeReplace should just work.

Thank you for your input, very useful – Rakha Aug 21 '18 at 13:04 — Rakha, Aug 21 '18 at 13:04

score 2 · Answer 2 · answered Aug 21 '18 at 10:27

You can use just VBScript.RegEx and the correct pattern for this.

Public Function ReplacePattern(ByRef iString As String, iSearchPattern As String, iReplaceWith As Variant)
'---------------------------------------------------------------------------------------
' Procedure : ReplacePattern
' Purpose   : Searches a string by pattern and replaces the text with given value
' Returns   : Replaced string.
'---------------------------------------------------------------------------------------
'

    Dim RE As Object
    Set RE = CreateObject("VBScript.RegExp")

    RE.ignorecase  = True 
    RE.Global      = True

    RE.Pattern     = iSearchPattern
    iString        = RE.Replace(iString, iReplaceWith)
    ReplacePattern = iString

    On Error Resume Next
    Set RE = Nothing

End Function

Read more about patterns Here

pattern: "^\\\\.*C\$" => Begins with \\ + any number of any character except linebreak + C$

usage

??replacepattern("\\p9990cdc\C$\Temp","^\\\\.*C\$","C:") => C:\Temp

score 1 · Accepted Answer · answered Aug 20 '18 at 14:03

1

You could instead use Mid(Instr()) to find the index of $ and grab the string from there (minus 1 to keep the directory letter) onwards.

strSelectedFile = Replace(Mid(strSelectedFile, InStr(strSelectedFile, "$") - 1, Len(strSelectedFile)), "$", ":")

answered Aug 20 '18 at 14:03

Marcucciboy2

3,156
3
20
38

1

Great, it works, thanks a lot my friend! Reminds me why I never liked VBA ;) – Rakha Aug 20 '18 at 14:08
So should I understand that RegEx or Wildcards are NOT supported in the replace function then? – Rakha Aug 20 '18 at 14:09
@Rakha `Regex.Replace()` would work but takes [a little bit more legwork](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) to get working in VBA. For the built in `Replace()` honestly I'm not sure but basically no [examples](https://stackoverflow.com/questions/41618327/excel-vba-using-wildcard-to-replace-string-within-string) that [I've seen](https://stackoverflow.com/a/1438845/2727437) uses it in this way. – Marcucciboy2 Aug 20 '18 at 14:18
Thanks. Last question, how do I write an IF so that it only does that replacement IF the string contains \\*\C$ ? (* meaning anything) is it possible to do an IF with a RegEx like : "\\[\w]\C$" ? Much appreciated – Rakha Aug 20 '18 at 14:28
@Rakha would it cover all of your cases to just search `If Instr(strSelectedFile, "\C$") Then` – Marcucciboy2 Aug 20 '18 at 14:35
Thanks, just what I needed! – Rakha Aug 20 '18 at 18:07

MSAccess using a wildcard in the REPLACE function

3 Answers3

Linked