Opening web documents as "text"

[[email protected]]

Hi,

I have a simple request which is driving me nuts ! I wonder if anyone
can help ?

My request is that I want to open HTML files on remote Web servers and
read the contents as text. That's it ! If I use the Workbooks.Open
Filename function, then Excel loads the page into a worksheet - this is
what I want to avoid. I would rather write some code to parse the HTML
text which makes up the content of the document. The Open function
just doesn't work on anything other than local files.

Essentially, I want to do exactly the same as the code snippet below,
but where sSource is:

"http://someserver.somewhere.com/example.htm"

rather than

"c:\example.htm"

Any help or advice would be greatly appreciated !

Regards

Nick

::CODE SNIPPET STARTS HERE
Sub Example_Code

Const sSource$ = "c:\example.htm"

Dim hFile as Integer

hFile = Freefile

Open sSource for Input Access Read Shared as #hFile

'Perform various operations on data held within file
Close #hFile

End Sub
::CODE SNIPPET ENDS HERE

[Tom Ogilvy]

Jake Marx Posted this:

=======================================================
Hi Witek,

Here's an example that should get you started. It doesn't work real well
with active content (ASP, etc.), but it should work for most sites. If
you want the HTML source instead of the viewable text produced by the HTML,
you can change the InnerText property to InnerHTML.


Regards,
Jake Marx


Sub Demo()
Dim ie As Object
Dim nFile As Integer
Set ie = CreateObject("InternetExplorer.Application")

With ie
.Visible = False
.Silent = True
.Navigate "www.yahoo.com"
Do Until Not .Busy
DoEvents
Loop
nFile = FreeFile
Open "D:\yahoo.txt" For Output Shared As #nFile
Print #nFile, .Document.DocumentElement.InnerText
Close #nFile
.Quit
End With
Set ie = Nothing
End Sub
===========================================================




Alyda Gilmore added this additional advice:
===========================================
Witek,


Further to Jake's most excellent example, may I suggest that you set a
reference to Microsoft Internet Controls (Shdocvw.dll) and use 'Dim ie As
SHDocVw.InternetExplorer' to declare the ie object. The beauty of this
approach is that you have all the IntelliSense features of the VBA Editor at
your disposal, including auto list members, syntax checking, parameter info,
quick info, and code formatting.
===========================================


So this write the text of the page to a file, but you could get the text
from

.Document.DocumentElement.InnerText

or

.Document.DocumentElement.InnerTextHTML

And parse it.
--

Regards,
Tom Ogilvy
<[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I have a simple request which is driving me nuts ! I wonder if anyone
> can help ?
>
> My request is that I want to open HTML files on remote Web servers and
> read the contents as text. That's it ! If I use the Workbooks.Open
> Filename function, then Excel loads the page into a worksheet - this is
> what I want to avoid. I would rather write some code to parse the HTML
> text which makes up the content of the document. The Open function
> just doesn't work on anything other than local files.
>
> Essentially, I want to do exactly the same as the code snippet below,
> but where sSource is:
>
> "http://someserver.somewhere.com/example.htm"
>
> rather than
>
> "c:\example.htm"
>
> Any help or advice would be greatly appreciated !
>
> Regards
>
> Nick
>
> ::CODE SNIPPET STARTS HERE
> Sub Example_Code
>
> Const sSource$ = "c:\example.htm"
>
> Dim hFile as Integer
>
> hFile = Freefile
>
> Open sSource for Input Access Read Shared as #hFile
>
> 'Perform various operations on data held within file
> Close #hFile
>
> End Sub
> ::CODE SNIPPET ENDS HERE
>

[[email protected]]

Tom,

Thanks for this.

If I'm going to use SHDocVw, I will presumably need to write a "Declare
Function" statement ?

Would you happen to have an example ?

Regards

Nick
Tom Ogilvy wrote:
> Jake Marx Posted this:
>
> =======================================================
> Hi Witek,
>
> Here's an example that should get you started. It doesn't work real
well
> with active content (ASP, etc.), but it should work for most sites.
If
> you want the HTML source instead of the viewable text produced by
the HTML,
> you can change the InnerText property to InnerHTML.
>
>
> Regards,
> Jake Marx
>
>
> Sub Demo()
> Dim ie As Object
> Dim nFile As Integer
> Set ie = CreateObject("InternetExplorer.Application")
>
> With ie
> .Visible = False
> .Silent = True
> .Navigate "www.yahoo.com"
> Do Until Not .Busy
> DoEvents
> Loop
> nFile = FreeFile
> Open "D:\yahoo.txt" For Output Shared As #nFile
> Print #nFile, .Document.DocumentElement.InnerText
> Close #nFile
> .Quit
> End With
> Set ie = Nothing
> End Sub
> ===========================================================
>
>
>
>
> Alyda Gilmore added this additional advice:
> ===========================================
> Witek,
>
>
> Further to Jake's most excellent example, may I suggest that you set
a
> reference to Microsoft Internet Controls (Shdocvw.dll) and use 'Dim
ie As
> SHDocVw.InternetExplorer' to declare the ie object. The beauty of
this
> approach is that you have all the IntelliSense features of the VBA
Editor at
> your disposal, including auto list members, syntax checking,
parameter info,
> quick info, and code formatting.
> ===========================================
>
>
> So this write the text of the page to a file, but you could get the
text
> from
>
> .Document.DocumentElement.InnerText
>
> or
>
> .Document.DocumentElement.InnerTextHTML
>
> And parse it.
> --
>
> Regards,
> Tom Ogilvy
> <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > I have a simple request which is driving me nuts ! I wonder if
anyone
> > can help ?
> >
> > My request is that I want to open HTML files on remote Web servers
and
> > read the contents as text. That's it ! If I use the
Workbooks.Open
> > Filename function, then Excel loads the page into a worksheet -
this is
> > what I want to avoid. I would rather write some code to parse the
HTML
> > text which makes up the content of the document. The Open function
> > just doesn't work on anything other than local files.
> >
> > Essentially, I want to do exactly the same as the code snippet
below,
> > but where sSource is:
> >
> > "http://someserver.somewhere.com/example.htm"
> >
> > rather than
> >
> > "c:\example.htm"
> >
> > Any help or advice would be greatly appreciated !
> >
> > Regards
> >
> > Nick
> >
> > ::CODE SNIPPET STARTS HERE
> > Sub Example_Code
> >
> > Const sSource$ = "c:\example.htm"
> >
> > Dim hFile as Integer
> >
> > hFile = Freefile
> >
> > Open sSource for Input Access Read Shared as #hFile
> >
> > 'Perform various operations on data held within file
> > Close #hFile
> >
> > End Sub
> > ::CODE SNIPPET ENDS HERE
> >