Solved

using * wildcard in vba access replace function

Posted on 2013-07-01
8
6,299 Views
Last Modified: 2016-11-20
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)
0
Comment
Question by:developingprogrammer
8 Comments
 
LVL 21
ID: 39292284
If you are wanting to strip off the file extension  then try this:

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

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

Open in new window


In a query you could use:

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

Open in new window

0
 

Author Comment

by:developingprogrammer
ID: 39292299
Yup yup definitely good but I'm trying to understand why the wildcard * isn't working and what corollaries it has = )
0
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 250 total points
ID: 39293517
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.
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 250 total points
ID: 39294273
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]+$")
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 21
ID: 39294404
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.
0
 

Author Comment

by:developingprogrammer
ID: 39295405
whao guys, super cool. ok let me read this more in depth to give a response befitting what yall have shared with me! = )
0
 

Author Closing Comment

by:developingprogrammer
ID: 39305524
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!! = ))
0
 

Expert Comment

by:gary franc
ID: 41895077
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
er:
Mess = "Please Check Parameters (SStrg, FStrg, LStrg, RStrg). Each Item Needs To Be Passed As A Character String."
MsgBox (Mess)
GoTo done
'
Cont:
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)))
'
done:
ReplaceWild = Trim(s)
End Function
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now