• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9576
  • Last Modified:

using * wildcard in vba access replace function

hey guys, i've got this

Replace(working.xls, ".xl*", "")

but it doesn't work. could yall help me out? i read somewhere the people use regexp for this but i wanna try and stay away from it at the moment (cause will need to add references etc)
2 Solutions
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
If you are wanting to strip off the file extension  then try this:

? Left("working.xls", Instr("working.xls",".")-1)

? Left("working.xlsx", Instr("working.xlsx",".")-1)

Open in new window

In a query you could use:

Left([FileNameField], Instr([FileNameField],".")-1)

Open in new window

developingprogrammerAuthor Commented:
Yup yup definitely good but I'm trying to understand why the wildcard * isn't working and what corollaries it has = )
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
The Replace() function in VBA does not support wildcards or pattern matching in the string for the  Find parameter.

Unfortunately, you will need to use regexp for pattern matching.
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Patrick MatthewsCommented:
I would make one very small change to TheHiTechCoach's approach:

Left([FileName], InStrRev([FileName], ".") - 1)

That way your expression is successful even if the file name itself has periods in it (besides the period that delimits file name and extension).

As to a RegExp approach:

1) Go to my article http://www.experts-exchange.com/Programming/Languages/Visual_Basic/A_1336-Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html

2) Add this function to a regular VBA module:

Function RegExpReplace(LookIn As String, PatternStr As String, Optional ReplaceWith As String = "", _
    Optional ReplaceAll As Boolean = True, Optional MatchCase As Boolean = True, _
    Optional MultiLine As Boolean = False)
    ' Function written by Patrick G. Matthews.  You may use and distribute this code freely,
    ' as long as you properly credit and attribute authorship and the URL of where you
    ' found the code
    ' This function relies on the VBScript version of Regular Expressions, and thus some of
    ' the functionality available in Perl and/or .Net may not be available.  The full extent
    ' of what functionality will be available on any given computer is based on which version
    ' of the VBScript runtime is installed on that computer
    ' This function uses Regular Expressions to parse a string, and replace parts of the string
    ' matching the specified pattern with another string.  The optional argument ReplaceAll
    ' controls whether all instances of the matched string are replaced (True) or just the first
    ' instance (False)
    ' If you need to replace the Nth match, or a range of matches, then use RegExpReplaceRange
    ' instead
    ' By default, RegExp is case-sensitive in pattern-matching.  To keep this, omit MatchCase or
    ' set it to True
    ' If you use this function from Excel, you may substitute range references for all the arguments
    ' Normally as an object variable I would set the RegX variable to Nothing; however, in cases
    ' where a large number of calls to this function are made, making RegX a static variable that
    ' preserves its state in between calls significantly improves performance
    Static RegX As Object
    If RegX Is Nothing Then Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = ReplaceAll
        .IgnoreCase = Not MatchCase
        .MultiLine = MultiLine
    End With
    RegExpReplace = RegX.Replace(LookIn, ReplaceWith)
End Function

Open in new window

3) Use it in an expression like this:

FileNameNoExt = RegExpReplace(FileName, "\.[A-Za-z0-9]+$")
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
This appeared to be a hypothetical question and the Replace function  not a specific question about working with File Names.   I agree with  matthewspatrick that the change suggested would be good is you were actually processing file names and/or wanting to only strip something off the end of a string.

@ matthewspatrick,  thanks for jumping in and posting the sample code.
developingprogrammerAuthor Commented:
whao guys, super cool. ok let me read this more in depth to give a response befitting what yall have shared with me! = )
developingprogrammerAuthor Commented:
whao guys, fantastic! matthewspatrick i have yet to go through your regexp article in full but definitely lots of stuff i gotta learn from there.

TheHiTechCoach thanks for picking out what i was looking for! thanks guys!! yall are the best!! = ))
gary francCommented:
Below is a function I created that performs wild card replacement of characters.
The TestReplaceWild subroutine can be used to test various replacement strings.
In the example below, the for the given string "test this out (12.4445555%$$$ wgfw///)(ft))" , The returned string is "test this out RR)" for removing characters starting with the first "(" found and ending with the first "ft)" found after the "(". Note that the replace string RR is added for the characters removed.

Sub TestReplaceWild()
Dim s, SStrg, FStrg, LStrg, RStrg
SStrg = "test this out (12.4445555%$$$ wgfw///)(ft))"
FStrg = "("
LStrg = "ft)"
RStrg = "RR"
s = ReplaceWild(SStrg, FStrg, LStrg, RStrg)
Debug.Print SStrg, FStrg, LStrg, RStrg, s
End Sub

Function ReplaceWild(SStrg, FStrg, LStrg, RStrg)
'replace SStrg by removing chars FStrg and LStrg, including all chars between them and adding chars RStrg.
Dim Mess As String, s As String, Strg As String, i As Integer, j As Integer, c As String
' check paramters
s = "ReplaceWild Function Errors"
On Error GoTo er
c = CStr(SStrg)
c = CStr(FStrg)
c = CStr(LStrg)
c = CStr(RStrg)
GoTo Cont
Mess = "Please Check Parameters (SStrg, FStrg, LStrg, RStrg). Each Item Needs To Be Passed As A Character String."
MsgBox (Mess)
GoTo done
On Error GoTo done
Strg = Trim(SStrg)
s = Strg
i = InStr(1, Strg, FStrg, 1)
If i <= 0 Then GoTo done
' if FStrg not found, exit without making changes
j = InStr(i + 1, Strg, LStrg, 1)
' remove and insert chars
s = Left(Strg, i - 1) & RStrg
If (j > 0) Then s = s + Mid(Strg, (j + Len(LStrg)))
ReplaceWild = Trim(s)
End Function

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now