Saving a Web Page

riggs · October 23rd, 2006, 06:33 PM

Hi, 1st post so bare with me....

I have opened a web page in vba from Excel as an object, I now wantw want to save the page as a text file on my C:, what do I do help?????

M

maccas · October 24th, 2006, 02:51 AM

The following should do you. You'll need project references to Microsoft Scripting Runtime (C:\WINDOWS\system32\scrrun.dll), Microsoft HTML Object Library (C:\WINDOWS\system32\MSHTML.TLB) & Mcirosoft Internet Controls (C:\WINDOWS\system32\shdocvw.dll).

Code:

Sub ExplorerCont********************()

Dim IExp As SHDocVw.InternetExplorer
Dim hDoc As MSHTML.HTMLDocument
Dim fsoFSO As Scripting.FileSystemObject
Dim fsoFile As Scripting.TextStream

    Set IExp = New SHDocVw.InternetExplorer
    ' You don't have to show the app window
    'IExp.Visible = True
    IExp.Navigate "http://www.google.com"

    ' Wait for the page to load
    Do Until IExp.Busy = False
        DoEvents
    Loop

    ' Set the HTML Document
    Set hDoc = IExp.Document

    ' Set up the FileSystemObject and create a text file
    Set fsoFSO = New Scripting.FileSystemObject
    Set fsoFile = fsoFSO.CreateTextFile(Filename:="C:\Webpage source code.txt")

    ' Write the source code of the webpage to the text file
    fsoFile.WriteLine Text:=hDoc.Body.innerHTML
    fsoFile.Close

    ' Quit Internet Explorer
    IExp.Quit

    ' De-reference variables
    Set IExp = Nothing
    Set hDoc = Nothing
    Set fsoFSO = Nothing
    Set fsoFile = Nothing

End Sub

riggs · October 24th, 2006, 02:28 PM

I'll give that a go cheers matey....

riggs · October 25th, 2006, 05:57 PM

That worked nicely but I have another question.

Can you save a web page as a text file as if you were literally going File/SaveAs ??????.txt

So far i've got

Public Sub Retrieve()
Dim MyPage As Object
Dim IeApp As InternetExplorer

Set IeApp = New InternetExplorer

IeApp.Visible = True
IeApp.Navigate "http://www.google.com"
Do: Loop Until IeApp.readyState = READYSTATE_COMPLETE

End Sub

What do i need to add to save the site as a text file on my C:

Thanks

M

maccas · October 26th, 2006, 02:35 AM

That's a pretty simple amendment from my original code. You just need to use the InnerText property rather than the InnerHTML property. Example as follows:

Code:

Sub ExplorerCont********************()

Dim IExp As SHDocVw.InternetExplorer
Dim hDoc As MSHTML.HTMLDocument
Dim fsoFSO As Scripting.FileSystemObject
Dim fsoFile As Scripting.TextStream

    Set IExp = New SHDocVw.InternetExplorer
    ' You don't have to show the app window
    'IExp.Visible = True
    IExp.Navigate "http://www.google.com"

    ' Wait for the page to load
    Do Until IExp.Busy = False
        DoEvents
    Loop

    ' Set the HTML Document
    Set hDoc = IExp.Document

    ' Set up the FileSystemObject and create a text file
    Set fsoFSO = New Scripting.FileSystemObject
    Set fsoFile = fsoFSO.CreateTextFile(Filename:="C:\Webpage text.txt")

    ' Write the source code of the webpage to the text file
    fsoFile.WriteLine Text:=hDoc.Body.innerText
    fsoFile.Close

    ' Quit Internet Explorer
    IExp.Quit

    ' De-reference variables
    Set IExp = Nothing
    Set hDoc = Nothing
    Set fsoFSO = Nothing
    Set fsoFile = Nothing

End Sub

maccas · October 26th, 2006, 02:39 AM

NB you can also use the OuterHTML and OuterText properties although in my experience they basically do the same things.

Maccas

tappenchris · November 29th, 2006, 10:23 PM

how do I get project references to microsoft scripting runtime?:)

maccas · November 30th, 2006, 04:59 AM

IN the VBE select Tools -> References... From the ensuing dialogue box, scroll down the list of check box options till you get to "Microsoft Scripting Runtime," check it and click ok. You should be done.

If for some reason "Microsoft Scripting Runtime" is not in the list of options you could also browse for the file - it should be at roughly this sort of location "C:\WINDOWS\system32\scrrun.dll"

Maccas