Search text of external files and import
Thank you very much, Tom. I made some adjustments to accomodate the desired
output, and the code works well.
"Tom Ogilvy" wrote:
Sub ReadStraightTextFile()
Dim sStr as String
Dim LineofText As String
Dim fName as String
dim i as Long
fname = dir("C:\Myfiles\*.txt)
i = 0
Redim list(1 to 1)
do while fname<""
i = i + 1
if i < 1 then
Redim Preserve list(1 to i)
end if
List(i) = fname
fName = dir()
Loop
Open "C:\Results.txt" For Output As #2
for i = 1 to ubound(list)
Open "C:\MyFiles\" & List(i) For Input As #1
sStr = ""
Do While Not EOF(1)
Line Input #1, LineofText
if(instr(1,LineofText,"=Failed",vbTextCompare) then
sStr = sStr & Left(LineofText,12) & ", "
End if
Loop
if len(str) = "" then
Print #2, List(i) & " | None"
else
Print #2, List(i) & " | " & Left(sStr,len(sStr)-2)
end if
'Close the file
Close #1
Next
Close #2
End Sub
Code is untested and may contain typos, but should get you started.
--
Regards,
Tom Ogilvy
"flaterp" wrote in message
...
I have about 80 text files (.txt) that contain results of PC vulnerability
scans. Each file represents a different machine, and each file contains
multiple "Passed" and/or "Failed" results depending on what
vulnerabilities
are present. All files are in a common networked folder - separate from
the
Excel spreadsheet.
I wish to query each file for any instances of "=Failed" and output the
preceeding 12 alphanumeric characters into a cell. "=Passed" can be
ignored.
For instance, if "testcase1.txt" contains the following data:
2005_292_004=Failed
2005_292_005=Failed
2005_315_001=Passed
Then the reference cell should return "2005_292_004, 2005_292_005"; if all
were "Passed" then the cell can read "none".
So if multiple files were queried, the xls may look like below (the '|'
indicates separate columns:
testcase1.txt | 2005_292_004, 2005_292_005
testcase2.txt | None
testcase3.txt | 2005_292_004
testcase4.txt | 2005_315_005
testcase5.txt | None
Thank you for any help.
|