Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save local copy of webpages
I wish to automate the saving of local copies of webpages using Excel,
and would appreciate any help. The main requirement I have, (and this is what makes it difficult), is to do it without having to import or open the webpage into Excel first as this seems to upset the formatting of the page. I intend to do this with a number of internet webpages for which the URLs will be contained in a number of cells in a worksheet. The webpages are mostly text and I am not bothered about saving a local copy of any graphics that might exist on the pages. Does anybody know if it is actually possible to do this, and if so can you give me any help with the VBA code? I am using Excel 2002 (SP3) on Windows XP (SP1) and have IE6 installed. Thanks, Graham |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save local copy of webpages
Perhaps information from this previous post will help:
-------------------------------------- In a previous post by Jake Marx, he showed 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 Using .Document.DocumentElement.InnerText This did work for me with an open page in IE Sub Tester1() Dim IE As Object Dim sHTML As String Set IE = GetObject(, "InternetExplorer.Application") IE.Visible = True sHTML = IE.Document.Documentelement.innerText Debug.Print sHTML Set IE = Nothing End Sub -- Regards, Tom Ogilvy "Graham Standring" wrote in message ... I wish to automate the saving of local copies of webpages using Excel, and would appreciate any help. The main requirement I have, (and this is what makes it difficult), is to do it without having to import or open the webpage into Excel first as this seems to upset the formatting of the page. I intend to do this with a number of internet webpages for which the URLs will be contained in a number of cells in a worksheet. The webpages are mostly text and I am not bothered about saving a local copy of any graphics that might exist on the pages. Does anybody know if it is actually possible to do this, and if so can you give me any help with the VBA code? I am using Excel 2002 (SP3) on Windows XP (SP1) and have IE6 installed. Thanks, Graham |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save local copy of webpages
Thanks for the help. I could not get this to work at first but by doing
a bit of research on the net I have adapted the code you gave me. It now uses '.Document.DocumentElement.innerHTML' instead of '.Document.DocumentElement.innerText' as follows: Sub Demo() Dim IE As Object Dim nFile As Integer Dim wpage As String Dim LastRow As Long LastRow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row Set IE = CreateObject("InternetExplorer.Application") For n = 1 To LastRow 'Get URLs of webpages from cells in Sheet1 webpage = Sheets(1).Range("A" & n).Value With IE .Visible = True .Silent = True .Navigate webpage Do Until Not .Busy DoEvents Loop nFile = FreeFile Open "D:\file" & n & ".htm" For Output Shared As #nFile Print #nFile, .Document.DocumentElement.innerHTML Close #nFile End With Next n IE.Quit Set IE = Nothing End Sub This uses a list of URLs already entered in Sheet1 Column A. It doesn't always save a perfect copy of the webpage but it is good enough for what I wanted. Thanks again, Graham Tom Ogilvy wrote: Perhaps information from this previous post will help: -------------------------------------- In a previous post by Jake Marx, he showed 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 Using .Document.DocumentElement.InnerText This did work for me with an open page in IE Sub Tester1() Dim IE As Object Dim sHTML As String Set IE = GetObject(, "InternetExplorer.Application") IE.Visible = True sHTML = IE.Document.Documentelement.innerText Debug.Print sHTML Set IE = Nothing End Sub -- Regards, Tom Ogilvy "Graham Standring" wrote in message ... I wish to automate the saving of local copies of webpages using Excel, and would appreciate any help. The main requirement I have, (and this is what makes it difficult), is to do it without having to import or open the webpage into Excel first as this seems to upset the formatting of the page. I intend to do this with a number of internet webpages for which the URLs will be contained in a number of cells in a worksheet. The webpages are mostly text and I am not bothered about saving a local copy of any graphics that might exist on the pages. Does anybody know if it is actually possible to do this, and if so can you give me any help with the VBA code? I am using Excel 2002 (SP3) on Windows XP (SP1) and have IE6 installed. Thanks, Graham |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can not copy the file from server to my local C:\ | Excel Discussion (Misc queries) | |||
Internet Explorer opens a local copy | Excel Discussion (Misc queries) | |||
VBA to save a web page to local disk | Excel Discussion (Misc queries) | |||
Reference styles and local/non-local formulae - international problems. | Excel Programming | |||
open/save excel on a local web page as an activex | Excel Programming |