Output html file to text file

[jimleon]

Hi People,

I have a link in my vba code:

Application.FollowHyperlink "http://www.xxxxxx.co.uk/"

but I dont want to open a browser (or show it minimised) but need to dump the resultant html page to a text file to search for a string.

I have tried:
Application.FollowHyperlink "http://www.xxxxxx.co.uk/" >c:\temp.txt

but that doesnt work.

Any ideas?

[NeoPa]

I cannot help much, other than to point you towards the Microsoft Web Browser OLE class. It needs a Reference set up to the Microsoft HTML Reference Library.

Hope this helps.

I'm pretty sure using the .FollowHyperlink won't get you what you want.

[Delerna]

Here is my attempt. First time I have done this and I like it and I will use it.
Its something I have contemplated for a quite while but never got around to it.
I have kept the code as simple as possible so its workings are obvious.
You will need to add error checking etc etc.


Step 1
add a reference to the "Microsoft Internet Controlls" type library

Step 2
add the code and modify for your situation

Private ieBrowser As InternetExplorer
 
Private Sub Form_Load()
   'Create a browser object
   Set ieBrowser = CreateObject("internetexplorer.application")
   ieBrowser.Navigate "http://www.delerna.com/Index.asp"
 
   'Wait for the page to load
   dtStartTime = Now
   Do While ieBrowser.readyState <> READYSTATE_COMPLETE
      If DateDiff("s", dtStartTime, Now) > 240 Then Exit Sub
   Loop
 
   'Get the page contents
   sDocHTML = ieBrowser.Document.documentElement.innerHTML
 
   'And save it
   Open "c:\Test.txt" For Output As 1
   Print #1, sDocHTML
   Close #1
 
   'destroy the browser object
   Set ieBrowser = Nothing
End Sub
 

Good luck and thanks for influencing me to actually sit down and finally do it.

[NeoPa]

@Delerna
I found a Microsoft HTML Object Library reference Delerna, but nothing similar to that :S

[NeoPa]

Like you I think I'm ready to start playing in this area. I'd be interested in following your code more closely, but I find there are items which are not declared (sDocHTML, etc). Is this because it's declared elsewhere or do you not have Option Explicit set? I ask this not to criticise you understand, but if you don't use this as standard then may I suggest you reconsider that approach. I have a short article on the matter here (Require Variable Declaration).

Again, I expect you may be doing this already and just omitted a couple of lines of your code. In that case please just ignore this (but I'm interested in the full code anyway ;)).

[Delerna]

Hi Neopa
Yes I normally use option explict, but the code is something that I threw together in about 15 minutes, for the sole purpose of answering the posted question. So to answer your question I did not use Option explicit and the posted code is complete.

I hope I can be forgiven for the bad programming practice because, I can try and excuse it with the simplicity of the program but there is no excuse in reality :{
No offence is taken, you are just....right!

As to the "Microsoft Internet Controls" library, that is definitely the one.
I also have a reference to the "Microsoft HTML Object" Library, so I tried removing the reference to the "Microsoft Internet Controls" and then checked intellisense by typing a space immediately after "As" in

Private ieBrowser As InternetExplorer

and there was no InternetExplorer in the list.
so its definitely "Microsoft Internet Controls"

The code is actually written from ideas presented on a few sites I found with google. Much of the code was copy paste as evidenced by the variable you point out sDocHTML. My normal practice would have it as strDocHTML
Anyway, here is a cleaned up version of the code

Option Compare Database
Option Explicit
 
Private ieBrowser As InternetExplorer
 
Private Sub Form_Load()
   Dim strDocHTML As String, dteStartTime As Date
   'Create a browser object
   Set ieBrowser = CreateObject("internetexplorer.application")
   ieBrowser.Navigate "http://www.delerna.com/Index.asp"
 
   'Wait for the page to load. Exit Form_load sub, doing nothing, if loading the page takes too long
   dteStartTime = Now
   Do While ieBrowser.readyState <> READYSTATE_COMPLETE
      If DateDiff("s", dteStartTime, Now) > 240 Then Exit Sub
   Loop
 
   'Get the page contents
   strDocHTML = ieBrowser.Document.documentElement.innerHTML
 
   'And save it
   Open "c:\Test.txt" For Output As 1
   Print #1, strDocHTML
   Close #1
 
   'destroy the browser object
   Set ieBrowser = Nothing
End Sub
 

[NeoPa]

Perfectly timed Delerna :)

I was just answering a thread where the OP wanted information about the public facing IP address they were published as (I know. Don't even ask). I tried to explain why this was less straightforward than they imagined but ...

Anyway, I wanted to post a link to this thread but I was struggling to find it again. At this point you posted. Nice one!

FYI: The other thread is Anyone out there have a clean Get_External_IP_Address function?.

[Delerna]

Where to get "Microsoft Internet Controls"
I had the impression from the web sites that I visited that it was included with access. I have Access 2003.
I rarely use Access these days, we use asp and html as front ends to SQL server. Thats part of the reason I come here to answer questions...it maintains my access skillset....Access is a great tool and it also happens to be where I learned much of what I know.

Anyway, if it's not part of access then I also have installed on my computer.
Dot Net Framework SDK v2.0
Visual Web Dexeloper 2008 Express
and Visual Studio Pro 2005

Maybe it came from one of those?

[NeoPa]

@Delerna
I did some searching and it seems that the Internet Client SDK is required for that reference.

Microsoft Internet Controls Life Saver
The Internet Client SDK can be downloaded from http://www.microsoft.com/ie/ie50

Thanks for your clearly explained answer. That has helped me find what I needed to proceed on this :) It should also help the OP of the linked thread. Bonus!

[Delerna]

I had a need to download the content of a web page on a regular basis so that I could monitor the current state of some facts and figures. So I wrote a vbscript version of the above so I could schedule it. I don't know if anyone is interested but I thought I would post it here.
The conversion was not very difficult, so you probably could have figured it out anyway.

const READYSTATE_COMPLETE=4
 
Dim ieBrowser  
   'Create a browser object 
   Set ieBrowser = CreateObject("internetexplorer.application") 
   SavePageContent("c:\scripts\Test.txt","page URL")
   Set ieBrowser = Nothing   'destroy the browser object 
   MsgBox "Done"
 
 
sub SavePageContent(Pth,Pge)
   Dim strDocHTML, dteStartTime
 
   ieBrowser.Navigate Pge 
 
   'Wait for the page to load. Exit sub, doing nothing, if loading the page takes too long 
   dteStartTime = Now 
   Do While ieBrowser.readyState <> READYSTATE_COMPLETE 
      If DateDiff("s", dteStartTime, Now) > 1000 Then Exit Sub 
   Loop 
 
   'And save it 
   Dim fso, MyFile
   Set fso = CreateObject("Scripting.FileSystemObject")
   Set MyFile = fso.CreateTextFile(Pth)
   MyFile.WriteLine(ieBrowser.Document.documentElement.innerHTML )
   MyFile.Close
   set MyFile=nothing
   set fso=nothing
end sub