VBA macro for finding a folder for a keyword

Question

I look at creating a VBA macro that will “search” the folder for the keyword “fuel” and discard all information from all returned files and put them in the data sheet.

For example, I have the week numbers in the folder (1 - 52 cross the year, so in the new year it will contain only one folder, but it will build and build as the year continues), so I look for this folder for all .doc files contain the word "fuel". You can do this through a search in the windows, simply by typing “fuel” in the search function in the upper corner, and it will display all the file names, and all files contain the words “fuel” inside them.

I currently have it, but it is only looking for a file with "fuel" in its name, and not with its insisde.

Sub LoopThroughFiles() Dim MyObj As Object, MySource As Object, file As Variant file = Dir("c:\testfolder\") While (file <> "") If InStr(file, "fuel") > 0 Then MsgBox "found " & file Exit Sub End If file = Dir Wend End Sub 

I did a few searches for this, but I just can't get anything to work :( My VB skills need to be updated, I think.

Thank you for the advanced.

Jb

Answer 1

This is not particularly beautiful, but think this should work:

 Sub loopThroughFiles() Dim file As String file = FindFiles("C:\TestFolder", "fuel") If (file <> "") Then MsgBox file End Sub Function FindFiles(ByVal path As String, ByVal target As String) As String ' Run The Sheell Command And Get Output Dim files As String Dim lines files = CreateObject("Wscript.Shell").Exec("FIND """ & target & """ """ & path & "\*.*""").StdOut.ReadAll lines = Split(files, vbCrLf) ' Look for matching files Dim curFile As String Dim line For Each line In lines If (Left(line, 11) = "---------- ") Then curFile = Mid(line, 12) End If If (line = target) Then FindFiles = curFile Exit Function End If Next FindFiles = "" End Function 

Uses the FIND command line and then reads the output (therefore, it is necessary to use Wscript.Shell) and returns the first match or an empty line if the file is not found

Following the @BLUEPIXY FINDSTR / M command, the function can be replaced with:

 Function FindFiles(ByVal path As String, ByVal target As String) As String ' Run The Shell Command And Get Output Dim files As String files = CreateObject("Wscript.Shell").Exec("FINDSTR /M """ & target & """ """ & path & "\*.*""").StdOut.ReadAll FindFiles = "" If (files <> "") Then Dim idx As Integer idx = InStr(files, vbCrLf) FindFiles = Left(files, idx - 1) End If End Function