Need help searching through text files and exporting into Excel 1

[mlarsen]

I am trying to loop through multiple text files within a specified folder. Essentially I am searching for any 3rd party websites so I have a search for href tags. I think want the url for these to be exported into Excel. I have the following code but it does nothing...

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


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


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

 

[PHV]

but it does nothing
Did you step your code with the F8 key to discover what happens ?

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

 

[mlarsen]

when I do that its saying that the value of:

If InStr(1, WholeLine, "href") > 0 Then - is '0' so thats probably why nothing is happening. But...the file DOES contain that, I brought it up in a notepad and did a search.

 

[Swi]

Try:

If InStr(1, [red]Ucase(WholeLine)[/red], "HREF") > 0 Then

Swi

 

[Swi]

Or:

If InStr(1, WholeLine, "href", [red]vbTextCompare[/red]) > 0 Then

Swi

 

[mlarsen]

Thanks so much Swi, it ended up being adding the vbTextCompare!!

Works like a charm now!

 

[Swi]

Glad to help.

Swi