列出文件夹中的所有文本文件并将信息写入 Excel 工作表

Question

文件夹中有某些文本文件，即 file1.txt ；文件2.txt； ...此外，还有一个字符串数组，可以在 VBScript 中存储为studs(i)。我的目标是验证每个字符串 (studs(i)) 是否存在于每个文本文件 (fileN.txt) 中，并将信息写入 Excel 工作表，其中行和文件名中包含字符串元素 (file1, file2, file3) ,....) 在列中。我需要一个 Vbscript 来自动执行此过程。非常感谢任何帮助

Answer 1

看看这是否有助于

• 执行findstr命令
• 捕获System.Collections.ArrayList中的结果
• 然后可以将结果存储在excel中

代码

Function findFilesThatContain(searchText, filePath)
    Set DataList = CreateObject _
    ("System.Collections.ArrayList")
    Set objShell = WScript.CreateObject("WScript.Shell")
    Set objExecObject = objShell.Exec("findstr /M """ & searchText & """ " & filePath)
    Do While Not objExecObject.StdOut.AtEndOfStream
        fileLoc = objExecObject.StdOut.ReadLine()
        'Wscript.Echo searchText&","&fileLoc      
        DataList.Add fileLoc
    Loop
    Set findFilesThatContain = DataList
End Function

Sub saveToExcel(searchText, searchPath, strExcelPath)
    Set objExcel = CreateObject("Excel.Application")
    If (Err.Number <> 0) Then
        On Error GoTo 0
        Wscript.Echo "Excel application not found."
        Wscript.Quit
    End If
    objExcel.Workbooks.Add
    Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
    objSheet.Name = "Search Result"
    Dim i,j
    j = 1
    For Each searchText in searchTexts
        Dim files
        Set files = findFilesThatContain (searchText, searchPath)
        i = 1
        objSheet.Cells(i, j).Value = searchText
        For Each path in files
            Wscript.Echo searchText&","&path
            i = i + 1
            objSheet.Cells(i, j).Value = path
        Next
        j = j + 1
    Next
    objSheet.Range("1:1").Font.Bold = True
    objExcel.ActiveWorkbook.SaveAs strExcelPath, 56
    objExcel.ActiveWorkbook.Close
    objExcel.Application.Quit
End Sub
Dim strExcelPath
strExcelPath = "c:\test.xls"
Dim searchPath 
searchPath = "E:\bin\bat\*.bat"
Dim searchTexts(2)
searchTexts(0)="pushd"
searchTexts(1)="if"
saveToExcel searchText, searchPath, strExcelPath

输出