Reads entire *.txt file into string opposed to a desired line by line input.

[ej_user]

I have a text file of html source code (acquired using openurl) that I
want to read into Excel through VBA. My problem in the entire file is
read into a string; I want to evaulate the file line by line.

The text file contains a character that resembles a rectangle standing
on end that is understood in Wordpad as a carraige return - i think.
If I open the source code text file in Wordpad and save it as a DOS
text file, I can read the file just fine in Excel using VBA. How can
I programmatically resolve this issue?

thx.
Edward

 

[Jake Marx]

Hi Edward,

I have a text file of html source code (acquired using openurl) that I
want to read into Excel through VBA. My problem in the entire file is
read into a string; I want to evaulate the file line by line.

The text file contains a character that resembles a rectangle standing
on end that is understood in Wordpad as a carraige return - i think.
If I open the source code text file in Wordpad and save it as a DOS
text file, I can read the file just fine in Excel using VBA. How can
I programmatically resolve this issue?

You should be able to use the Split function with a vbCrLf delimeter.
Here's an example using IE automation:

Sub Demo()
Dim ie As Object
Dim sWholeFile As String
Dim asLineByLine() As String
Dim lLine As Long

Set ie = CreateObject("InternetExplorer.Application")

ie.Navigate "http://www.longhead.com/"

Do While ie.Busy And Not ie.ReadyState = 4
DoEvents
Loop

sWholeFile = ie.Document.body.innerhtml
ie.Quit
Set ie = Nothing
asLineByLine = Split(sWholeFile, vbCrLf)

For lLine = LBound(asLineByLine) To UBound(asLineByLine)
Sheet1.Cells(lLine + 1, 1).Value = asLineByLine(lLine)
Next lLine
End Sub


--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Edward Glover]

Thanks Jake for providing me with a solution. The sample code is also
greatly appreciated.

regards,
Edward

 

[Jake Marx]

Thanks Jake for providing me with a solution. The sample code is also
greatly appreciated.

No problem, Edward - glad to help out!

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]