希望有人能够指出我误入歧途的地方。
我创建了一个脚本来搜索目录中的所有文件,并返回包含已输入到单元格中的特定关键字的任何文件的文件路径和文件名。该脚本似乎适用于我在桌面上设置的测试文件夹,但是当我尝试搜索包含更多文件的另一个目录时,我收到“输入超过文件末尾”错误。
有人能看出我哪里出了问题吗?脚本如下:
Sub findfiles()
Dim startpath As String 'this is the starting directory path
startpath = Cells(1, 2).Value
Dim keyword, text As String
keyword = Cells(2, 2).Value
Dim returnpatharr() As Variant 'array containing the filepaths for files containing the keyword
Dim returnfilearr() As Variant 'array containing the filenames for files containing the keyword
Dim i As Integer
i = 0
Set fso = CreateObject("Scripting.FileSystemObject")
Set objFolder = fso.GetFolder(startpath)
Set objSubFolders = objFolder.Subfolders
For Each SubFolder In objSubFolders
Set objFolderFiles = SubFolder.Files
For Each File In objFolderFiles
Dim txt As Byte
txt = FreeFile
Open File For Input As #txt
text = Input(LOF(txt), txt)
If InStr(1, text, keyword, 1) > 0 Then
ReDim Preserve returnpatharr(i)
returnpatharr(i) = File.Path
ReDim Preserve returnfilearr(i)
returnfilearr(i) = File.Name
Debug.Print i
Debug.Print File.Name
Debug.Print File.Path
i = i + 1
End If
Close #txt
Next File
Next SubFolder
Dim last As Integer
last = 4 + i
On Error GoTo msg
Range("A5:A" & last).Value = Application.WorksheetFunction.Transpose(returnfilearr)
Range("B5:B" & last).Value = Application.WorksheetFunction.Transpose(returnpatharr)
Done:
Exit Sub
msg:
MsgBox ("No Results")
End Sub
我的猜测是文件可能包含 LOF() 计数的字符,例如空字符,但 Input() 忽略。因此它尝试读取文件末尾之后的内容,因为 LOF() 指定的总字节数包括这些字符。请尝试以下方法...
'
'
'
Dim fileNum As Long
Dim text As String
fileNum = FreeFile
Open File For Binary Access Read As #fileNum
text = Space$(LOF(fileNum))
Get #fileNum, , text
'etc
'
'
Close #fileNum
'
'
'
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)