instr function in VBA

Kindly help need your assistance please,

Description:

I have designed a VBA code wh开发者_StackOverflow中文版ere I want to compare a string with FileName in a directory.In this case I have used Instr function, this helps me in 3 cases only but not dynamicaly.

Explaination:

if the str=4567 and compairing with filename, where filename can be:

1.xs1234567.pdf

2.4567.pdf

3.4567(1).pdf

4.updated 4567(2).pdf

so the code i have created help to find all the files, but this is not correct. It should exclude first file name ie:xs1234567.pdf

This is the following code:

Dirfname = finDir
fName = Mid((Dirfname), 1, (InStr(1, (Dirfname), ".") - 1))
fileExt = Mid((Dirfname), (InStr(1, (Dirfname), ".") + 1), 3)


**If (InStr(1, fName, wkvalue) > 0 And wkvalue <> "") Then ** 'checking lookup val in instr
        If (Trim(UCase(fileExt)) = "PDF" Or Trim(UCase(fileExt)) = "TIF") Then
                                Cells(recnum, 2).Value = "Yes"
                                'col = col + 1
                                ws.Hyperlinks.Add Anchor:=Cells(recnum, (col + 1)), _
                                Address:=SourceFolderName & "\" & Dirfname
                                col = col + 1
                                'Else: Cells(recnum, 2).Value = "No"
        End If
  End If

Please advice what can be done for this case.

---

You could use Regular Expressions to assist you. Im not very proficient with it yet but this is a relatively simple case. Here is a function adapted from tmehta.com/regexp that you could use in conjunction with an iteration of the filenames in a folder:

Function RegExpFind(FindIn, FindWhat As String, _
                    Optional IgnoreCase As Boolean = False) As Variant

    Dim i As Long
    Dim rslt() As String

    '// Using Late Binding here, use the commented types if you've added
    '// the "Microsoft VBScript Regular Expressions" reference
    Dim RE As Object 'RegExp
    Dim allMatches As Object 'MatchCollection
    Dim aMatch As Object 'Match

    '// Use "Set RE = New RegExp" if using the VBScript reference        
    Set RE = CreateObject("vbscript.regexp")

    RE.Pattern = FindWhat
    RE.IgnoreCase = IgnoreCase
    RE.Global = True
    Set allMatches = RE.Execute(FindIn)

    '// check if we've found anything, if not return a single element array
    '// containing an empty string. If we've found something return at least 
    '// at least a single element array containing the matched expressions
    If allMatches.Count = 0 Then
        ReDim rslt(0 To 0)
        rslt(0) = ""
    Else
        ReDim rslt(0 To allMatches.Count - 1)
        For i = 0 To allMatches.Count - 1
            rslt(i) = allMatches(i).Value
        Next i
    End If

    RegExpFind = rslt

End Function

You would need to pass in the file name as the FindIn parameter and the regexp pattern "^4567" in the FindWhat parameter. Using it this way will return 4567 (as the first element in the return array) only if it occurs at the start of the search string. This function could be easily recycled for use with other searches down the road if you need to.

---

Assuming that your criteria for a match is that the character preceeding 4567, if any, is a space

i = InStr(1, fName, wkvalue)
if i > 0 and wkvalue <> "" Then
    ch = " "
    if i > 1 then
        ch = mid(fName, i - 1, 1)
    end if
    if ch = " " then 
        ...

---

You don't describe why the first filename should be rejected, but I assume it's because it has a digit (0-9) before and/or after wkvalue, such that "4567" is not the entire number. In such a case, this will work:

    charBefore = ""
    charAfter = ""
    pos = InStr(fName(i), wkvalue)
    If pos = 1 Then
        ' It's at the beginning of the filename.
        ' Get character after the number.
        charAfter = Mid(fName(i), pos + Len(wkvalue), 1)
    ElseIf pos > 1 Then
        ' It's not at the beginning of the filename
        ' Get characters before and after the number.
        charBefore = Mid(fName(i), pos - 1, 1)
        charAfter = Mid(fName(i), pos + Len(wkvalue), 1)
    Else
        ' Number not found.
    End If
    ' Could add another ElseIf to check if it's at the end of the filename.

    If pos > 0 And wkvalue <> "" _
        And Not charBefore Like "#" And Not charAfter Like "#" Then
            ' Number found and not preceded or followed by a digit (0-9).
            ' Do your thing.
    End If