Searching Subfolders for .htm files 1

[mlarsen]

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

 

[Bong]

I do something very similar to manage image files. As a first step I look for all my pages (.htm). I use ChDir and Dir

Sub imgCntl1()
    [red]ChDir (Sheet1.Cells(1, 2))[/red]
    r = 1
    Sheet1.Range(Columns(4), Columns(4).End(xlToRight)).Clear
    Sheet1.Range(Columns(4), Columns(4).End(xlToRight)).Cells.Interior.ColorIndex = xlColorIndexNone
    c = 4
    [red]a = Dir("*.htm*")[/red]
    Do While a <> ""
        Sheet1.Cells(r, c) = a
        c = c + 1
        [red]a = Dir[/red]
    Loop
End Sub

_________________
Bob Rashkin

 

[PHV]

I'd use the FileSearch object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mlarsen]

Hello PVS

One question. So I am using the fielsearch.application and it works fine, it finds all the .html files throughout all the subfolders and prints the amount found in a messagebox. What I want tho, is after each .html file is found, for it to be searched for any 'href' instance, and for that entire line to be printed in excel. I put sort of an open statement within the filesearch but it doesn't seem to work. any suggestions? the code is below.

Set fs = Application.FileSearch
With fs
.LookIn = "C:\Exelon"
.Filename = ".html"
.SearchSubFolders = True
'.FileType = mosFileTypeAllFiles
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
Open File 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

 

[SkipVought]

Hi,

        For i = 1 To .FoundFiles.Count
          Open [b].FoundFiles(i)[/v] 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
        Next i

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[mlarsen]

Thanks a lot Skip,

It seems to be actually computing now which is a relief. The only thing is when it computing it looks like it keeps replacing the next file with the one already printed, essentially ending the computing with only one line...Any ideas? I'm trying to fool around with it now.

 

[SkipVought]

I did not look at the write into Excel.

I'd recommend explicitly specifying the Sheet Object...

With Sheets(1) 'or whatever sheet you are referencing
  myR = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
  .Cells(myR, 1).Value = workfile
  .Cells(myR, 2).Value = WholeLine
end with

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[mlarsen]

awesome! great thanks soo much, I've been messing with this code for the past day, great to finally get it done. Thanks again!

Mark