Can Excel VBA check a file for a string before opening it?

[USAMax]

I have the file path (FilePath) and file name (FileName) for the downloaded file but I need to know if there is a way to check the file for the string, "Over 500 results" before opening it.

Can the file be tested for the string?

 

[chicagocomputerclasses]

It should look like this. The space in the folder name DuPage County may possibly cause an error. The space may need to be handled as well, but I would first test it on a different folder to make sure it works first.

s = CreateObject("Wscript.Shell").Exec("cmd /c find /c /i ""Over 500 results"" C:\Users\David\Downloads\Markets\IL\DuPage County\60540.csv").StdOut.ReadAll

 

[LockeGarmin]

If you want a purely VBA way to do it here is a function you could use:

Function TextIsInFile(FilePath As String, TextToFind As String, Optional Compare As VbCompareMethod = vbBinaryCompare) As Boolean

Dim File As Long
Dim Line As String

    TextIsInFile = False
    File = FreeFile()
    Open FilePath For Input As #File
    
    Do Until EOF(File)
        Line Input #File, Line
        
        If InStr(1, Line, TextToFind, Compare) > 0 Then
            TextIsInFile = True
            Exit Do
        End If
    Loop
    
    Close #File

End Function

I prefer using the Scripting Runtime FileSystem objects for reading files because the code is more readable. To do so you could use this function:

Function TextIsInFile(FilePath As String, TextToFind As String, Optional Compare As VbCompareMethod = vbBinaryCompare) As Boolean

Dim File As Object

    TextIsInFile = False
    Set File = CreateObject("Scripting.FileSystemObject").OpenTextFile(FilePath)
    
    Do Until File.AtEndOfStream
        If InStr(1, File.ReadLine, TextToFind, Compare) > 0 Then
            TextIsInFile = True
            Exit Do
        End If
    Loop
    
    File.Close

End Function

 

[USAMax]

Thank you LockeGarmin, it looks like your solution is still opening the file but I could be wrong. I am planning to test variations of chicagocomputerclasses's idea but I will try yours first.

Thank you so much for your approach.

 

[LockeGarmin]

You're welcome!

If you mean "does this solution open the file in Excel", then no, it does not. But if you mean "does this solution open the file, period" then yes, but something is going to have to open the file in some way to see if it has the content you're looking for whether that be a command prompt in chicagocomputerclasses's or a text stream in my case. The main advantage with the approaches I put out is that it will examine one line at a time in memory until it finds the content, instead of putting the whole file in memory and searching. It may be advantageous to put the whole file in memory if the text you're searching for is always at the end of the file and you've got a really big file, but I wouldn't worry about that until you visibly notice a significant slow down from either of these functions.

 

[chicagocomputerclasses]

Frankly the only reason I would use "Wscript.Shell" in this case would be if I was trying to do a search in many files at a time.
Find will allow you to pass multiple files at the same time and search the string in all of them returning one stream with results. You could search the entire folder with csv files and quickly figure out which ones have the string in them.

If you are checking one file at a time then it's not worth the headache.