Searching Subfolders for .htm files
 

Hello

I have a parent folder called MySite. There are child folders within
it. I want to search through VBA the parent folder and all subfolders
for any .htm file. Then each .htm file is searched for any reference
of an 'href' tag. For some reason I can only get it to pull the one
file from the parent site C:\MySite\. My code is below

Any suggestions please?

Thanks
Mark

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

myPath = "C:\MySite\"
workfile = Dir(myPath & "*.htm")

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", 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()
Loop

Set fs = Application.FileSearch
With fs
.LookIn = "C:\MySite"
.Filename = "*.*"
.SearchSubFolders = True
'.FileType = mosFileTypeAllFiles
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
'MsgBox .FoundFiles(i)
Next i

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

End Sub

Searching Subfolders for .htm files
 

I've been told filesearch dows has bugs and there is a limit somewhere around
2000 files that it can return. Filesearch doesn't work in 2007. I have 2003
and the code seems to work. The sort order is not b y directory. The sort
is just the basic filename (shortname) without any directory. maybe you are
expecting the directory name to be part of the search.

"Mark" wrote:

Hello

I have a parent folder called MySite. There are child folders within
it. I want to search through VBA the parent folder and all subfolders
for any .htm file. Then each .htm file is searched for any reference
of an 'href' tag. For some reason I can only get it to pull the one
file from the parent site C:\MySite\. My code is below

Any suggestions please?

Thanks
Mark

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

myPath = "C:\MySite\"
workfile = Dir(myPath & "*.htm")

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", 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()
Loop

Set fs = Application.FileSearch
With fs
.LookIn = "C:\MySite"
.Filename = "*.*"
.SearchSubFolders = True
'.FileType = mosFileTypeAllFiles
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
'MsgBox .FoundFiles(i)
Next i

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

End Sub