VBA宏搜索关键字的文件夹 [英] VBA macro to search a folder for keyword

问题描述

我正在考虑构建一个VBA宏，它将搜索一个关键字术语燃料的文件夹，并将所有返回的文件中的所有信息拖回数据表。

例如，我有一个文件夹周数（1 - 52年，所以在新的一年，这只会包含一个文件夹，但会随着一年的建立和建立）我搜索这个文件夹所有.doc文件包含单词'燃料'。您可以通过Windows搜索进行此操作，只需在顶部的搜索功能中输入燃料即可显示所有文件名，所有文件中都包含燃料。

我目前有这个，但这只是搜索一个文件，其名称中包含燃料，而不是包含其中的内容。

  Sub LoopThroughFiles（）
 Dim MyObj As Object，MySource As Object，file As Variant 
 file = Dir（c：\testfolder\）
 While （file<"）
如果InStr（file，fuel）> 0然后
 MsgBoxfound&文件
退出Sub 
如果
文件= Dir 
 Wend 
 End Sub 
  

我已经为此做了一些搜索，但我只是无法得到任何工作:(我的VB技能需要一个复习我想。

谢谢高级。

JB

解决方案

它不是特别漂亮，但认为这样的事情应该可以工作：

  Sub loopThroughFiles（）
 Dim file As String 
 file = FindFiles（C：\TestFolder，fuel）
 If（file<>）然后MsgBox文件
 End Sub 
 
函数FindFiles（ByVal path As String，ByVal target As String）As String 
'运行Sheell命令并获取输出
 Dim files As String 
 Dim lines 
 files = CreateObject （Wscript.Shell）。Exec（FIND& target&& path&\ *。*）StdOut.ReadAll 
 lines = Split（files，vbCrLf）
 
'寻找匹配文件
 Dim curFile As String 
 Dim line 
对于每一行在行
 If（Left（line，11）=----- -----）然后
 curFile = Mid（line，12）
 End If 
 
 If（line = target）Then 
 FindFiles = curFile 
退出函数
结束如果
下一个
 
 FindFiles =
结束函数
  

使用FIND命令行，然后读取输出（因此需要使用Wscript.Shell）并返回第一个匹配，或者如果没有找到文件，则为空字符串

以下@ BLUEPIXY的命令FINDSTR / M，该函数可以替换为：

 函数FindFiles ByVal路径As String，ByVal target As String）As String 
'运行Shell命令并获取输出
 Dim files As String 
 files = CreateObject（Wscript.Shell）。Exec（ FINDSTR / M&目标& &路径& \ *。*）。StdOut.ReadAll 
 
 FindFiles =
如果（files<>）然后
 Dim idx As Integer 
 idx = InStr（files，vbCrLf）
 FindFiles = Left（files，idx  -  1）
如果
结束函数
  pre> 
I'm looking at building a VBA macro that will 'search' a folder for a keyword term 'fuel' and pull back all the information from all returned files and put them into a 'data' sheet.

For instance, I have a folder week numbers (1 - 52 cross the year so in the new year this will only contain one folder but will build and build as the year goes on) so I search this folder for all .doc files contains the word 'fuel'. You can do this via a windows search just by typing in "fuel" in the search function in the top corner and it will display all filenames and all files contains the words 'fuel' inside of them.

I currently have but this is only searching for a file that has 'fuel' within its name rather than containing insisde of it.
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 have done some searching around for this, but I just cant get anything to work :(. My VB skills need a refresher I think.

Thank you in advanced.

JB
解决方案
It isn't particularly pretty, but think something like 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 (hence needing to use Wscript.Shell) and returns first match, or empty string if no file is found

Following @BLUEPIXY's command FINDSTR /M the function can be replaced by:
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


                        
这篇关于VBA宏搜索关键字的文件夹的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！