opening and searching files in filesearch
 

Hello

I have a filesearch.application running in my code that works fine. I
need to somehow within that code, open each file that it finds, search
it for a reference, and then if found print the enite line into
excel. I have the following code that give me errors on the open
part.

myPath = "C:\Exelon\"
workfile = Dir(myPath & "*.html")

Set fs = Application.FileSearch
With fs
.LookIn = "C:\Exelon"
.Filename = ".html"
.SearchSubFolders = True
'.FileType = mosFileTypeAllFiles
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) 0 Then
Open myPath & workfile For Input Access Read As #1
While Not EOF(1)
Line Input #1, WholeLine
If InStr(1, WholeLine, "href", vbTextCompare) 0 Then
myR = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(myR, 1).Value = workfile
Cells(myR, 2).Value = WholeLine
End If
Wend
Close #1
workfile = Dir()

MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count

Next i

Else
MsgBox "There were no files found."
End If
End With

opening and searching files in filesearch
 

You don't have any looping - the code that I posted will work

Sub CheckTextFilesForHREFs()
Dim WholeLine As String
Dim myPath As String
Dim workfile As String
Dim myR As Long

myPath = "C:\Excel\Text Files\"
workfile = Dir(myPath & "*.html")

Do While workfile < ""
Open myPath & workfile For Input Access Read As #1
While Not EOF(1)
Line Input #1, WholeLine
If InStr(1, WholeLine, "href") 0 Then
myR = Cells(Rows.Count,1).End(xlUp).Row + 1
Cells(myR,1).Value = workfile
Cells(myR,2).Value = WholeLine
End If
Wend
Close #1
workfile = Dir()
Loop

End Sub



--
HTH,
Bernie
MS Excel MVP


"Mark" wrote in message
...
Hello

I have a filesearch.application running in my code that works fine. I
need to somehow within that code, open each file that it finds, search
it for a reference, and then if found print the enite line into
excel. I have the following code that give me errors on the open
part.

myPath = "C:\Exelon\"
workfile = Dir(myPath & "*.html")

Set fs = Application.FileSearch
With fs
.LookIn = "C:\Exelon"
.Filename = ".html"
.SearchSubFolders = True
'.FileType = mosFileTypeAllFiles
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) 0 Then
Open myPath & workfile For Input Access Read As #1
While Not EOF(1)
Line Input #1, WholeLine
If InStr(1, WholeLine, "href", vbTextCompare) 0 Then
myR = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(myR, 1).Value = workfile
Cells(myR, 2).Value = WholeLine
End If
Wend
Close #1
workfile = Dir()

MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count

Next i

Else
MsgBox "There were no files found."
End If
End With