Error62: Input Past End of File on VBA Script to Search Directory for Files Containing Keyword

Question

Hoping someone might be able to point out where I've gone astray.

I created a script to search through all files within a directory and return the file path and file name of any that contain a particular keyword, which has been entered into a cell. The script seems to work on a test folder I set up on my desktop, but when I try to search another directory that contains far more files, then I get the "Input Past End of File" error.

Can anyone see where I've gone wrong here? Script is below:

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

Maybe try FSO approach instead: stackoverflow.com/a/44914999/478884 — Tim Williams
– Tim Williams, Commented Apr 20, 2023 at 23:15
@TimWilliams, thanks for responding. It’s failing when the File is a .exe file. Maybe I need an if statement conditioned on certain file types or rather just create another error handling process here. My script is only intended to apply to files that can be meaningfully deciphered with a text editor. — Possdawgers
– Possdawgers, Commented Apr 21, 2023 at 15:35
Yes - check File.Type or fso.getextensionname(File.Name) to see if it's a file you're interested in reading. — Tim Williams
– Tim Williams, Commented Apr 21, 2023 at 16:27

score 1 · Accepted Answer · 2023-04-21 03:10:14Z

1

My guess is that a file may contain characters, such as Null characters, that LOF() counts, but Input() ignores. And so it tries to read past the end of the file, since the number of total bytes specified by LOF() include those characters. Try the following instead...

    '
    '
    '

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

edited Apr 21, 2023 at 3:10

answered Apr 21, 2023 at 2:46

user6585761

Sign up to request clarification or add additional context in comments.

5 Comments

Possdawgers Over a year ago

Good thoughts here. To add context, I’m searching through our production files in our version control directories. My intent was to quickly identify pascal files to dissect the logic behind our business -specific applications. I think I may just need to raise an exception when the files cannot be opened with a text editor.

user6585761 Over a year ago

Curious, though, did you try the code?

Possdawgers Over a year ago

Working through it right now. I’ll keep you posted. Thanks again for the suggestion !

Possdawgers Over a year ago

Sorry to be getting back to you so late, but you nailed it! My error handling approach didn’t work out. I tried your suggestion, and found that input must have been missing certain characters. Thanks again! That was such a helpful thought

user6585761 Over a year ago

That's great, I'm glad I was able to help. And thanks for the feedback. Cheers!

Collectives™ on Stack Overflow

Error62: Input Past End of File on VBA Script to Search Directory for Files Containing Keyword

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related