vba VBA中的instr函数

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/6368942/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 13:29:16  来源:igfitidea点击:

instr function in VBA

excel-vbavbaexcel

提问by Hardik Mistry

Kindly help need your assistance please,

请帮助需要您的帮助,

Description:

描述:

I have designed a VBA code where 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.

我设计了一个 VBA 代码,我想将一个字符串与目录中的 FileName 进行比较。在这种情况下,我使用了 Instr 函数,这仅在 3 种情况下对我有帮助,但不是动态的。

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

如果 str=4567 并与文件名进行比较,则文件名可以是:
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

所以我创建的代码有助于找到所有文件,但这不正确。它应该排除第一个文件名,即: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.

请建议可以为这种情况做些什么。

回答by Hari Seldon

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/regexpthat you could use in conjunction with an iteration of the filenames in a folder:

您可以使用正则表达式来帮助您。我还不是很熟练,但这是一个相对简单的案例。这是一个改编自tmehta.com/regexp的函数,您可以将它与文件夹中文件名的迭代结合使用:

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 FindInparameter and the regexp pattern "^4567"in the FindWhatparameter. 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.

您需要在文件名作为传递FindIn参数和正则表达式模式"^4567"FindWhat参数。4567仅当它出现在搜索字符串的开头时,以这种方式使用它才会返回(作为返回数组中的第一个元素)。如果您需要,可以轻松地回收此功能以用于其他搜索。

回答by chris neilsen

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

假设您的匹配条件是 4567 之前的字符(如果有)是空格

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

回答by Jean-Fran?ois Corbett

You don't describe whythe 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:

你没有描述为什么第一个文件名应该被拒绝,但我认为这是因为它之前和/或之后有一个数字(0-9)wkvalue,这样“4567”不是整个数字。在这种情况下,这将起作用:

    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