Importing Html Source Part 2

[tx12345]

A poster came up with this nice bit of code, but it seems if the source
code goes past 300 lines, it loops the first 300 lines several times:


Code:
--------------------


Sub Tester()
Range("a1").Value = GetSource("http://thewebpageyou wanthere")
End Sub


Function GetSource(sURL As String) As String

Dim oXHTTP As Object

Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
oXHTTP.Open "GET", sURL, False
oXHTTP.send
GetSource = oXHTTP.responsetext
Set oXHTTP = Nothing

End Function

Code:
--------------------


Also, note that A1 is the cell designated to receive the text. The code above attempts to place all the source into a single cell, which I think creates the problem. A smaller web page is manageable, but if the code goes too far past 300 lines then you run into this strange 'looping' thing mentioned above.

Is there some way to adjust that code to flow the source into the entire worksheet instead of stopping inside of one cell?

Thx

 

[Tim Williams]

Try this:

Sub Tester()

Dim s As String
Dim arr
Dim i As Long
Dim sht As Worksheet

s = GetSource("http://www.google.com")

arr = Split(s, vbLf)
Set sht = ThisWorkbook.Sheets("Sheet1")

For i = LBound(arr) To UBound(arr)
sht.Range("A1").Offset(i, 0).Value = arr(i)
Next i

End Sub


Function GetSource(sURL As String) As String

Dim oXHTTP As Object

Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
oXHTTP.Open "GET", sURL, False
oXHTTP.send
GetSource = oXHTTP.responseText
Set oXHTTP = Nothing

End Function


Tim

 

[tx12345]

that is some really sweet code. Works perfectly. I managed to find a
more compact version of the web page I am pulling data from (market
prices), but your code will save a few steps.

Where did you learn this stuff? the more I work with Excel the more
convinced I am that VBA is the way to go, otherwise the files get too
big and too slow. I'll send you the link to the file I am working on
via PM when I am done. You'll have a good laugh.

Thanks again

Tx

 

[Tim Williams]

Tx,

Glad it was useful. I just picked up VBA from having projects I needed to
get done: takes a bit of time to get started but hanging out on this
newsgroup has been a great help. If you're interested in learning then I'd
recommend getting a good book (eg. John Walkenbach): there is a lot of help
available from the web (by some of the same authors) but a good book really
pulls it all together.

Cheers
Tim