VBA - Saving a web page locally.

[BratFire]

Dim IE As Object 
Set IE = CreateObject("InternetExplorer.Application") 
IE.Visible = True 
IE.navigate "http://www.MrExcel.com" 
Do 
If IE.readyState = 4 Then 
IE.Visible = False 
Exit Do 
Else 
DoEvents 
End If 
Loop 
MsgBox "Done" 
IE.Visible = True

Using the above code to open a web page, is there a way to 'save as' the web page to a local drive using VB code?

 

[BratFire]

Would it be easier to copy the web page source and rewrite it to another file?

Actually, the above code was copied from a Joe Was post from the past.

 

[Zack Barresse]

Hello BatFire,

Why not just use a web query? ...


<font face=Tahoma New><SPAN style="color:#00007F">Sub</SPAN> GetInfo()
    <SPAN style="color:#00007F">Dim</SPAN> wb <SPAN style="color:#00007F">As</SPAN> Workbook, ws <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">Dim</SPAN> strURL <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> wb = Workbooks.Add(xlWBATWorksheet)
    <SPAN style="color:#00007F">Set</SPAN> ws = wb.Sheets(1)
    strURL = "http://www.MrExcel.Com"
    <SPAN style="color:#00007F">With</SPAN> ws.QueryTables.Add(Connection:="URL;" & strURL, Destination:=ws.Cells(1, 1))
        .Name = "MrExcel Query"
        .BackgroundQuery = <SPAN style="color:#00007F">True</SPAN>
        .TablesOnlyFromHTML = <SPAN style="color:#00007F">True</SPAN>
        .Refresh BackgroundQuery:=<SPAN style="color:#00007F">False</SPAN>
        .SaveData = <SPAN style="color:#00007F">True</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


This will bring the desired web page right into your desired worksheet.

 

[BratFire]

I had considered it, but it isn't going to work for the quick task that I was hoping to whip up. Otherwise I am very fond of queries and have used them extensively. Basically, I am using Excel as my VB compiler since I do not have Visual Basic. Not to mention, I'd like to learn the code for this anyway for any future reference.

I'm going to open a list of URLs and save the page to my local to manipulate at a later time with another utility. I'm stuck at the point of how to code VBA to save the opened IE instance to my local.

I've been searching the web for VB/VBA code that will save the active document, but it seems that several of the methods available in the word.application aren't available for the internetExplorer.application.

IE.ActiveDocument.SaveAs "C:\cpa\test.html"
doesn't seem to work.

 

[Joe Was]

In IE Save the URL as a html file.
InterNet Explorer Toolbar: File - Save As...

Then: In Excel open that same html file and it will open in the workbook, complete with pictures, links and everything. The only problem is that the proportion or organization may be different [more spread out!].

 

[BratFire]

In IE Save the URL as a html file.
InterNet Explorer Toolbar: File - Save As...

Then: In Excel open that same html file and it will open in the workbook, complete with pictures, links and everything. The only problem is that the proportion or organization may be different [more spread out!].

Would I be able to save the URL as a html file using VBA code rather than physically clicking File - Save As?

 

[Joe Was]

Sub pasteMyWebP()
Dim wb As Workbook, ws As Worksheet
Dim IE As Object

Const strURL As String = "http://www.MrExcel.Com"

Set wb = Workbooks.Add(xlWBATWorksheet)
Set ws = wb.Sheets(1)

Set IE = CreateObject("InternetExplorer.Application")

IE.Visible = True
IE.Navigate strURL

Do While IE.ReadyState <> 4
DoEvents
Loop

IE.ExecWB 17, 2
IE.ExecWB 12, 2

ActiveSheet.Paste Range("A1")
IE.Visible = False
End Sub



'Note: I got "IE.ExecWB 17, 2" & "IE.ExecWB 12, 2" from Juan Pablo Gonzales and do not know what it is?
'But, it is what copies the web page!

"ExceWB" is the OLE Object handler of:

The IOleCommandTarget interface, which enables objects and their containers to dispatch commands to each other. For example, an object's toolbars may contain buttons for commands such as Print, Print Preview, Save, New, and Zoom.

Because no efficient, standard mechanism has been available to dispatch them to the container. No efficient means to send commands such as Print, Page Setup, and Properties to an in-place active object. Such simple command routing could have been handled through existing OLE Automation standards and the IDispatch interface, but the overhead with IDispatch is more than is required in the case of document objects. The IOleCommandTarget interface provides a simpler means to achieve the same ends.

The interface allows a caller both to query for support of one or more commands within a group and to issue a supported command to the object.

 

[BratFire]

Not sure that this is quite what I was going for. I have had no luck searching the web so I may have to strip the html source code from each web page and output it to a new file. Problem is, this method may not save the images and charts that go along with the html file.

So, ultimately, I may just have to suck it up and open each link and save as each webpage by hand. I was hoping to automate the 'save as' with VB code.

 

[Joe Was]

Did you try my last code post Above?

It does copy everything to a Worksheet, pictures and all, all be it, it does this by serially coping objects to the Sheet, hence the change in the visible arangement of those objects. But, you still get it all.

 

[BratFire]

Did you try my last code post?

It does copy everything to a Worksheet, pictures and all, all be it, it does this by serially coping objects to the Sheet, hence the change in the visible arangement of those objects. But, you still get it all.

I tried it and I got the VBA code:

IE.ExecWB 17, 2 
IE.ExecWB 12, 2 

ActiveSheet.Paste Range("A1")

regurgitated into cells A1 through A3 or 5.
I was hoping there would be a way to save the webpage to my hard drive, effectively automating the File --> Save As that I'd perform in IE, with VBA code w/o having to 'bounce' the html file off of Excel first.

I'll keep toying with your ideas.