?
Solved

using * wildcard in vba access replace function

Posted on 2013-07-01
8
Medium Priority
?
7,985 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 1000 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 1000 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
 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

770 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