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
instr function in VBA
提问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 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.
您需要在文件名作为传递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