Solved

using * wildcard in vba access replace function

Posted on 2013-07-01
8
7,063 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 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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
 
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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

726 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