Can I import raw HTML source code into Excel?

I want to run a web query from Excel that retreives the raw HTML code, not
the parsed HTML page.

Specifically, instead of retreiving text from the page, I want to return the
page's HTML title (read: the text that falls between the <title> tags).

Unfortunately, the web query interface only lets you pick page content. I
recorded the web query process and tried hacking the VBA by hand, but it
doesn't appear that there are any objects/parameters/commands that allow me
to import the page as pure source code.

Does anyone know how to trick the web query into loading the source code
into worksheet? If so, I can then write some VBA to strip the <title> value
out for me.

Any gurus out there have an answer?

Thanks in advance-
Jon

Hi Jon,

Here's a function that should return the page title of an HTML document (or
an empty string if URL is invalid or can't be found):

Public Function gsGetURLTitle(rsURL As String) As String
Dim ie As Object

Set ie = CreateObject("InternetExplorer.Application")

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

gsGetURLTitle = IIf(StrComp(.Document.Title, _
"Cannot find server", vbTextCompare) = 0, _
vbNullString, .Document.Title)
.Quit
End With

Set ie = Nothing
End Function

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

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


J Diorio wrote:
> I want to run a web query from Excel that retreives the raw HTML
> code, not the parsed HTML page.
>
> Specifically, instead of retreiving text from the page, I want to
> return the page's HTML title (read: the text that falls between the
> <title> tags).
>
> Unfortunately, the web query interface only lets you pick page
> content. I recorded the web query process and tried hacking the VBA
> by hand, but it doesn't appear that there are any
> objects/parameters/commands that allow me to import the page as pure
> source code.
>
> Does anyone know how to trick the web query into loading the source
> code into worksheet? If so, I can then write some VBA to strip the
> <title> value out for me.
>
> Any gurus out there have an answer?
>
> Thanks in advance-
> Jon

THANKS JAKE! I can't believe you did that in 4 minutes.

-Jon


"Jake Marx" wrote:

> Hi Jon,
>
> Here's a function that should return the page title of an HTML document (or
> an empty string if URL is invalid or can't be found):
>
> Public Function gsGetURLTitle(rsURL As String) As String
> Dim ie As Object
>
> Set ie = CreateObject("InternetExplorer.Application")
>
> With ie
> .Navigate rsURL
> Do While .Busy And Not .ReadyState = 4
> DoEvents
> Loop
>
> gsGetURLTitle = IIf(StrComp(.Document.Title, _
> "Cannot find server", vbTextCompare) = 0, _
> vbNullString, .Document.Title)
> .Quit
> End With
>
> Set ie = Nothing
> End Function
>
> --
> Regards,
>
> Jake Marx
> MS MVP - Excel
> www.longhead.com
>
> [please keep replies in the newsgroup - email address unmonitored]
>
>
> J Diorio wrote:
> > I want to run a web query from Excel that retreives the raw HTML
> > code, not the parsed HTML page.
> >
> > Specifically, instead of retreiving text from the page, I want to
> > return the page's HTML title (read: the text that falls between the
> > <title> tags).
> >
> > Unfortunately, the web query interface only lets you pick page
> > content. I recorded the web query process and tried hacking the VBA
> > by hand, but it doesn't appear that there are any
> > objects/parameters/commands that allow me to import the page as pure
> > source code.
> >
> > Does anyone know how to trick the web query into loading the source
> > code into worksheet? If so, I can then write some VBA to strip the
> > <title> value out for me.
> >
> > Any gurus out there have an answer?
> >
> > Thanks in advance-
> > Jon
>
>

Jon d wrote:
> THANKS JAKE! I can't believe you did that in 4 minutes.

No problem - glad to help! And actually, it was more like 15 minutes. But
who's counting? <g>

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

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