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]

Are you asking to check the string inside the file or do you want to check for that string within the file name?

You can do something like this to check the file name.

If instr([COLOR=#333333][FONT=Verdana]FileName, [/FONT][/COLOR][COLOR=#333333][FONT=Verdana]"Over 500 results") > 0 then

end if[/FONT][/COLOR]

 

[Fennek]

Hi,

there is the old DOS command to search for a string in all files of a folder. To check it, open a cmd.exe window and navigate to the folder. Just type

find "search-string" *.txt

to identify files containing the string "search-string". By adding "< c:\temp\Find_list.txt" you can pass to fndings to a file.

If you need it in VBA, you can address it directly in a "wscript" command.

regards

 

[USAMax]

Are you asking to check the string inside the file or do you want to check for that string within the file name?

You can do something like this to check the file name.

If instr([COLOR=#333333][FONT=Verdana]FileName, [/FONT][/COLOR][COLOR=#333333][FONT=Verdana]"Over 500 results") > 0 then

end if[/FONT][/COLOR]

No I'm sorry, I did not mean the file name. I should have been more specific.

 

[USAMax]

Thank you Fennek, it has been years since I had thought of that command. Even after you have reminded me I still cannot think of a way of responding with a True/False if the text is within the CSV file. If the text is in the file I need to skip it and if it is not I can use it.

How can I use the, "find" command to return a True/False? If I pass the findings to another file then I have to open that file.

 

[chicagocomputerclasses]

This will run your dos command dir c: and store the output in a variable. Try to replace it with find "Over 500 results" filename.csv

s = CreateObject("Wscript.Shell").Exec("cmd /c dir c:").StdOut.ReadAll

 

[USAMax]

I am sorry chicagocomputerclasses but I am not sure about a few things.

1) Should I put the file path where the c: is like this?
s = CreateObject("Wscript.Shell").Exec("cmd /c dir " & FilePath).StdOut.ReadAll

2) I do not see where the, "find" command is within this so I am not sure where to enter the string into this command.

Currently I am opening the file, searching for the string and closing the file.

Your way I copy the contents of the file into another file then open the file, searching for the string and closing the file. This just added one more step, what am I missing?

 

[USAMax]

Hi chicagocomputerclasses

Here is the code I tried:

Sub Macro1()
    Dim S As Variant
    S = CreateObject("Wscript.Shell").Exec("cmd /c dir C:\Users\David\Downloads\Markets\IL\DuPage County\60540.csv").StdOut.ReadAll
    Stop
End Sub

after running this... S = ""

The file is 118 KB and the text will be in the last cell of this csv. If this is limited to 128 characters then it will truncate what I need to see.

 

[USAMax]

I finally got this to work but the information it provides has nothing to do with what I am looking for:

Volume in drive C has no label.
Volume Serial Number is 8C32-81F7

Directory of C:\Users\David\Downloads\Markets\IL

11/26/2016 04:02 PM 120,267 60540.csv
1 File(s) 120,267 bytes
0 Dir(s) 1,456,630,198,272 bytes free



It only tells me the drive information, again am I missing something?

 

[chicagocomputerclasses]

If you look at the documentation here
https://technet.microsoft.com/en-us/library/cc725655(v=ws.11).aspx

If you search with parameters /c /i, i will make it not case sensitive and /c will give you the count found.
find /c /i "text we are looking for" C:\Users\Me\Documents\filename.csv

If you search with parameters /c /i, i will make it not case sensitive and /c will give you the count found. You should get a return that looks like this
---------- C:\USERS\ME\DOCUMENTS\FILENAME.CSV: 50
The last number indicated the number of times it was able to find the string you are searching for. If it is 0 then it was not able to find it.