Importing Web Page Source Code

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tx12345 - 21 Jan 2006 21:30 GMT

Hi

I know you can open a web page into Excel by simply entering a web
address. But is there a way to tell Excel to import the source code
instead?

Thx

Signature

tx12345

Reply to this Message

Tim Williams - 21 Jan 2006 22:20 GMT

You can use an xmlhttp object instead if what you want is the source

Sub Tester()
MsgBox GetSource("http://www.google.com")
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

> Hi
>
[quoted text clipped - 3 lines]
>
> Thx

Reply to this Message

tx12345 - 21 Jan 2006 23:10 GMT

That is a very nice bit of code. But what happens is a dialogue box pops
up with the source, and then after I click OK it goes away and never
gets into the worksheet.

Is there a way for the data to flow into the worksheet?

Many thanks

tx

Signature

tx12345

Reply to this Message

Gary Keramidas - 21 Jan 2006 23:27 GMT

tim was just giving you an example using the message box, but you didn't
state what you were doing with it or where you wanted it,

if you want it in a1
change
MsgBox GetSource("http://www.google.com")

to
Range("a1").Value = GetSource("http://www.google.com")

Signature

Gary

> That is a very nice bit of code. But what happens is a dialogue box pops
> up with the source, and then after I click OK it goes away and never
[quoted text clipped - 5 lines]
>
> tx

Reply to this Message

tx12345 - 22 Jan 2006 00:00 GMT

Hi Gary. Yes, that works, thank you.

What happens, though, is as much source as can be dumped into one cell
gets stuffed into A1, but somewhere down the middle of the source the
rest of it gets cut off.

Is there a way to get the import of the source to come in with the
carriage returns/line breaks?

If you import a web page, it fills up a1 to a1000 (or whatever), and
from there you can process the data, but if everything is in one cell
makes it more of a challenge.

Many Thx!

tx

Signature

tx12345

Reply to this Message

tx12345 - 22 Jan 2006 00:16 GMT

I came up with a solution (a hack)

open the source in a new file, then save it as a text file, then
re-open it and that shows the entire page. Apparently ithe page
contents are not cut off, just what excel can display inside of one
cell.

I did learn one thing: if you have text encapsulated with " " you can
fit a lotta text into one cell! The source comes in "<html> ...
</html>" If there is a way to get rid of the incoming quotes, I could
skp the hack I came up with, but if not i am still very apprecitive of
the tip.

As usual, everyone that helps me out gets a free copy of the file. -
you might not want it though, lol

Thanks again

tx

Signature

tx12345

Reply to this Message