0

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
Possdawgers
  • 93
  • 6
  • Does it fail on a specific file? – Tim Williams Apr 20 '23 at 23:10
  • Maybe try FSO approach instead: https://stackoverflow.com/a/44914999/478884 – Tim Williams Apr 20 '23 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 Apr 21 '23 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 Apr 21 '23 at 16:27

1 Answers1

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
    
    '
    '
    '
Domenic
  • 7,844
  • 2
  • 9
  • 17
  • 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. – Possdawgers Apr 21 '23 at 15:32
  • Curious, though, did you try the code? – Domenic Apr 21 '23 at 15:46
  • Working through it right now. I’ll keep you posted. Thanks again for the suggestion ! – Possdawgers Apr 21 '23 at 15:56
  • 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 – Possdawgers Apr 27 '23 at 03:08
  • That's great, I'm glad I was able to help. And thanks for the feedback. Cheers! – Domenic Apr 27 '23 at 12:26