Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying web pages onto Excel Spreadsheets using VBA
Hi,
Attempts to code, in VBA, a means of copying a web page onto an Excel sheet have been frustrating and inconclusive. When I apply the "QueryTables.Add" approach using the URL, I get a partial result but it does not include the data I wish to capture - just static data. If I use the "FollowHyperlink" approach, Internet Explorer is called and the web page is displayed but I cannot see how to code in VBA, "SellectAll" and "Copy" so that I can then "Paste" to the Excel sheet. I have tried this approach in a manual manner using a list of Hyperlinks and calling each Hyperlink, copying the web page and pasting onto individual spreadsheets. Can this be achieved using VBA? TIA, Ken Rock |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying web pages onto Excel Spreadsheets using VBA
Below is a simple example of getting data grom a website. If I had the URL
address and the data yo want I can write a similar routine. There are lots of tricks in getting this data and the more experience you have the easier it is. I have helped a number of people get web data. they all have been succesful and happy with the resutls. The more knowledgable you are with VBA programming and HTML format the easier it is. Sub GetZipCodes() ZIPCODE = InputBox("Enter 5 digit zipcode : ") Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "http://zip4.usps.com/zip4/citytown_zip.jsp" 'get web page IE.Navigate2 URL Do While IE.readyState < 4 And _ IE.busy = True DoEvents Loop Set Form = IE.document.getElementsByTagname("Form") Set zip5 = IE.document.getElementById("zip5") zip5.Value = ZIPCODE Set ZipCodebutton = Form(0).onsubmit Form(0).submit Do While IE.busy = True DoEvents Loop Set Table = IE.document.getElementsByTagname("Table") Location = Table(0).Rows(2).innertext IE.Quit MsgBox ("Zip code = " & ZIPCODE & " City/State = " & Location) End Sub "kenrock" wrote: Hi, Attempts to code, in VBA, a means of copying a web page onto an Excel sheet have been frustrating and inconclusive. When I apply the "QueryTables.Add" approach using the URL, I get a partial result but it does not include the data I wish to capture - just static data. If I use the "FollowHyperlink" approach, Internet Explorer is called and the web page is displayed but I cannot see how to code in VBA, "SellectAll" and "Copy" so that I can then "Paste" to the Excel sheet. I have tried this approach in a manual manner using a list of Hyperlinks and calling each Hyperlink, copying the web page and pasting onto individual spreadsheets. Can this be achieved using VBA? TIA, Ken Rock |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying web pages onto Excel Spreadsheets using VBA
I have a very similar problem to kenrock, although it's mostly because I'm
new at VBA. I'm trying to retrieve home values from zillow. I have an excel sheet with addresses set up and want to automate this process and log the values. My problem comes when the VBA code tries to find the home values in the website HTML. Here is my code, any suggestions would be greatly appreciated. Option Explicit Sub Get_Quotes() Dim objIE As InternetExplorer Dim lRow As Long Dim abc As String Dim striEst As String Dim ele As Object Set objIE = CreateObject("InternetExplorer.Application") With objIE .Visible = True .navigate "http://www.zillow.com/" For lRow = 2 To Range("A" & Rows.Count).End(xlUp).Row Do While .Busy: DoEvents: Loop Do While .readyState < 4: DoEvents: Loop .document.getElementsByName("citystatezip").Item(0 ).Value = Sheets("MAIN").Range("D" & lRow) .document.getElementById("GOButton").Click Do While .Busy: DoEvents: Loop Do While .readyState < 4: DoEvents: Loop Do While .Busy: DoEvents: Loop Do While .readyState < 4: DoEvents: Loop Do While .Busy: DoEvents: Loop Do While .readyState < 4: DoEvents: Loop For Each ele In objIE.document.getElementsByTagName("span") If ele.class = "price" Then abc = ele.innerText: Exit For Next Sheets("MAIN").Range("E" & lRow) = abc Next lRow End With Set objIE = Nothing End Sub "Joel" wrote: Below is a simple example of getting data grom a website. If I had the URL address and the data yo want I can write a similar routine. There are lots of tricks in getting this data and the more experience you have the easier it is. I have helped a number of people get web data. they all have been succesful and happy with the resutls. The more knowledgable you are with VBA programming and HTML format the easier it is. Sub GetZipCodes() ZIPCODE = InputBox("Enter 5 digit zipcode : ") Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "http://zip4.usps.com/zip4/citytown_zip.jsp" 'get web page IE.Navigate2 URL Do While IE.readyState < 4 And _ IE.busy = True DoEvents Loop Set Form = IE.document.getElementsByTagname("Form") Set zip5 = IE.document.getElementById("zip5") zip5.Value = ZIPCODE Set ZipCodebutton = Form(0).onsubmit Form(0).submit Do While IE.busy = True DoEvents Loop Set Table = IE.document.getElementsByTagname("Table") Location = Table(0).Rows(2).innertext IE.Quit MsgBox ("Zip code = " & ZIPCODE & " City/State = " & Location) End Sub "kenrock" wrote: Hi, Attempts to code, in VBA, a means of copying a web page onto an Excel sheet have been frustrating and inconclusive. When I apply the "QueryTables.Add" approach using the URL, I get a partial result but it does not include the data I wish to capture - just static data. If I use the "FollowHyperlink" approach, Internet Explorer is called and the web page is displayed but I cannot see how to code in VBA, "SellectAll" and "Copy" so that I can then "Paste" to the Excel sheet. I have tried this approach in a manual manner using a list of Hyperlinks and calling each Hyperlink, copying the web page and pasting onto individual spreadsheets. Can this be achieved using VBA? TIA, Ken Rock |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying web pages onto Excel Spreadsheets using VBA
This will help you along. I did a dump on sheet 1 of all the properties on
the webstire. From this dump I was able to get some of the info. didn't have time to figure out how to get everything. If you need more help let me know. this willget you moving along Sub Get_Quotes() 'Dim objIE As InternetExplorer Dim lRow As Long Dim abc As String Dim striEst As String Dim ele As Object myzip = "10001" Set objIE = CreateObject("InternetExplorer.Application") With objIE .Visible = True .navigate "http://www.zillow.com/" For lRow = 2 To 2 Do While .Busy Or _ .readyState < 4 DoEvents Loop Set citystate = .document.getElementsByName("citystatezip") citystate.Item(0).Value = myzip .document.getElementById("GOButton").Click Do While .Busy Or _ .readyState < 4 DoEvents Loop Set sht = Sheets("Sheet1") RowCount = 1 For Each itm In .document.all sht.Range("A" & RowCount) = itm.tagname sht.Range("B" & RowCount) = itm.classname sht.Range("C" & RowCount) = itm.ID sht.Range("D" & RowCount) = Left(itm.innertext, 1024) RowCount = RowCount + 1 Next itm Set sht = Sheets("Sheet2") RowCount = 1 sht.Range("A" & RowCount) = "adr" sht.Range("B" & RowCount) = "listing" sht.Range("C" & RowCount) = "type type-forSale" sht.Range("D" & RowCount) = "type" sht.Range("E" & RowCount) = "price" RowCount = 2 For Each ele In .document.all Select Case ele.classname Case "property-info" RowCount = RowCount + 1 Case "adr" sht.Range("A" & RowCount) = ele.innertext Case "listing" sht.Range("B" & RowCount) = ele.innertext Case "type type-forSale" sht.Range("C" & RowCount) = ele.innertext Case "type" sht.Range("D" & RowCount) = ele.innertext Case "price" sht.Range("E" & RowCount) = ele.innertext End Select Next ele Next lRow End With Set objIE = Nothing End Sub "SokerGuy" wrote: I have a very similar problem to kenrock, although it's mostly because I'm new at VBA. I'm trying to retrieve home values from zillow. I have an excel sheet with addresses set up and want to automate this process and log the values. My problem comes when the VBA code tries to find the home values in the website HTML. Here is my code, any suggestions would be greatly appreciated. Option Explicit Sub Get_Quotes() Dim objIE As InternetExplorer Dim lRow As Long Dim abc As String Dim striEst As String Dim ele As Object Set objIE = CreateObject("InternetExplorer.Application") With objIE .Visible = True .navigate "http://www.zillow.com/" For lRow = 2 To Range("A" & Rows.Count).End(xlUp).Row Do While .Busy: DoEvents: Loop Do While .readyState < 4: DoEvents: Loop .document.getElementsByName("citystatezip").Item(0 ).Value = Sheets("MAIN").Range("D" & lRow) .document.getElementById("GOButton").Click Do While .Busy: DoEvents: Loop Do While .readyState < 4: DoEvents: Loop Do While .Busy: DoEvents: Loop Do While .readyState < 4: DoEvents: Loop Do While .Busy: DoEvents: Loop Do While .readyState < 4: DoEvents: Loop For Each ele In objIE.document.getElementsByTagName("span") If ele.class = "price" Then abc = ele.innerText: Exit For Next Sheets("MAIN").Range("E" & lRow) = abc Next lRow End With Set objIE = Nothing End Sub "Joel" wrote: Below is a simple example of getting data grom a website. If I had the URL address and the data yo want I can write a similar routine. There are lots of tricks in getting this data and the more experience you have the easier it is. I have helped a number of people get web data. they all have been succesful and happy with the resutls. The more knowledgable you are with VBA programming and HTML format the easier it is. Sub GetZipCodes() ZIPCODE = InputBox("Enter 5 digit zipcode : ") Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "http://zip4.usps.com/zip4/citytown_zip.jsp" 'get web page IE.Navigate2 URL Do While IE.readyState < 4 And _ IE.busy = True DoEvents Loop Set Form = IE.document.getElementsByTagname("Form") Set zip5 = IE.document.getElementById("zip5") zip5.Value = ZIPCODE Set ZipCodebutton = Form(0).onsubmit Form(0).submit Do While IE.busy = True DoEvents Loop Set Table = IE.document.getElementsByTagname("Table") Location = Table(0).Rows(2).innertext IE.Quit MsgBox ("Zip code = " & ZIPCODE & " City/State = " & Location) End Sub "kenrock" wrote: Hi, Attempts to code, in VBA, a means of copying a web page onto an Excel sheet have been frustrating and inconclusive. When I apply the "QueryTables.Add" approach using the URL, I get a partial result but it does not include the data I wish to capture - just static data. If I use the "FollowHyperlink" approach, Internet Explorer is called and the web page is displayed but I cannot see how to code in VBA, "SellectAll" and "Copy" so that I can then "Paste" to the Excel sheet. I have tried this approach in a manual manner using a list of Hyperlinks and calling each Hyperlink, copying the web page and pasting onto individual spreadsheets. Can this be achieved using VBA? TIA, Ken Rock |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying web pages onto Excel Spreadsheets using VBA
Great! This is very helpful. Thank you so much. I have one last question
if you have a minute. The info being copied over to Sheet2 from the data dump seems to write over itself each time a new address is entered. It doesn't write to row2, then row3, then row4,... as the process continues down the original address list (since I have an excel list of address I'm trying to automate). Thanks again. "Joel" wrote: This will help you along. I did a dump on sheet 1 of all the properties on the webstire. From this dump I was able to get some of the info. didn't have time to figure out how to get everything. If you need more help let me know. this willget you moving along Sub Get_Quotes() 'Dim objIE As InternetExplorer Dim lRow As Long Dim abc As String Dim striEst As String Dim ele As Object myzip = "10001" Set objIE = CreateObject("InternetExplorer.Application") With objIE .Visible = True .navigate "http://www.zillow.com/" For lRow = 2 To 2 Do While .Busy Or _ .readyState < 4 DoEvents Loop Set citystate = .document.getElementsByName("citystatezip") citystate.Item(0).Value = myzip .document.getElementById("GOButton").Click Do While .Busy Or _ .readyState < 4 DoEvents Loop Set sht = Sheets("Sheet1") RowCount = 1 For Each itm In .document.all sht.Range("A" & RowCount) = itm.tagname sht.Range("B" & RowCount) = itm.classname sht.Range("C" & RowCount) = itm.ID sht.Range("D" & RowCount) = Left(itm.innertext, 1024) RowCount = RowCount + 1 Next itm Set sht = Sheets("Sheet2") RowCount = 1 sht.Range("A" & RowCount) = "adr" sht.Range("B" & RowCount) = "listing" sht.Range("C" & RowCount) = "type type-forSale" sht.Range("D" & RowCount) = "type" sht.Range("E" & RowCount) = "price" RowCount = 2 For Each ele In .document.all Select Case ele.classname Case "property-info" RowCount = RowCount + 1 Case "adr" sht.Range("A" & RowCount) = ele.innertext Case "listing" sht.Range("B" & RowCount) = ele.innertext Case "type type-forSale" sht.Range("C" & RowCount) = ele.innertext Case "type" sht.Range("D" & RowCount) = ele.innertext Case "price" sht.Range("E" & RowCount) = ele.innertext End Select Next ele Next lRow End With Set objIE = Nothing End Sub "SokerGuy" wrote: I have a very similar problem to kenrock, although it's mostly because I'm new at VBA. I'm trying to retrieve home values from zillow. I have an excel sheet with addresses set up and want to automate this process and log the values. My problem comes when the VBA code tries to find the home values in the website HTML. Here is my code, any suggestions would be greatly appreciated. Option Explicit Sub Get_Quotes() Dim objIE As InternetExplorer Dim lRow As Long Dim abc As String Dim striEst As String Dim ele As Object Set objIE = CreateObject("InternetExplorer.Application") With objIE .Visible = True .navigate "http://www.zillow.com/" For lRow = 2 To Range("A" & Rows.Count).End(xlUp).Row Do While .Busy: DoEvents: Loop Do While .readyState < 4: DoEvents: Loop .document.getElementsByName("citystatezip").Item(0 ).Value = Sheets("MAIN").Range("D" & lRow) .document.getElementById("GOButton").Click Do While .Busy: DoEvents: Loop Do While .readyState < 4: DoEvents: Loop Do While .Busy: DoEvents: Loop Do While .readyState < 4: DoEvents: Loop Do While .Busy: DoEvents: Loop Do While .readyState < 4: DoEvents: Loop For Each ele In objIE.document.getElementsByTagName("span") If ele.class = "price" Then abc = ele.innerText: Exit For Next Sheets("MAIN").Range("E" & lRow) = abc Next lRow End With Set objIE = Nothing End Sub "Joel" wrote: Below is a simple example of getting data grom a website. If I had the URL address and the data yo want I can write a similar routine. There are lots of tricks in getting this data and the more experience you have the easier it is. I have helped a number of people get web data. they all have been succesful and happy with the resutls. The more knowledgable you are with VBA programming and HTML format the easier it is. Sub GetZipCodes() ZIPCODE = InputBox("Enter 5 digit zipcode : ") Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "http://zip4.usps.com/zip4/citytown_zip.jsp" 'get web page IE.Navigate2 URL Do While IE.readyState < 4 And _ IE.busy = True DoEvents Loop Set Form = IE.document.getElementsByTagname("Form") Set zip5 = IE.document.getElementById("zip5") zip5.Value = ZIPCODE Set ZipCodebutton = Form(0).onsubmit Form(0).submit Do While IE.busy = True DoEvents Loop Set Table = IE.document.getElementsByTagname("Table") Location = Table(0).Rows(2).innertext IE.Quit MsgBox ("Zip code = " & ZIPCODE & " City/State = " & Location) End Sub "kenrock" wrote: Hi, Attempts to code, in VBA, a means of copying a web page onto an Excel sheet have been frustrating and inconclusive. When I apply the "QueryTables.Add" approach using the URL, I get a partial result but it does not include the data I wish to capture - just static data. If I use the "FollowHyperlink" approach, Internet Explorer is called and the web page is displayed but I cannot see how to code in VBA, "SellectAll" and "Copy" so that I can then "Paste" to the Excel sheet. I have tried this approach in a manual manner using a list of Hyperlinks and calling each Hyperlink, copying the web page and pasting onto individual spreadsheets. Can this be achieved using VBA? TIA, Ken Rock |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying web pages onto Excel Spreadsheets using VBA
the code was only meant to get you on the right track. I didn't download
every description for each house. Only the ones I found with the first listing. You have to add the addional descriptions yourself. Here is the code so it wouldn't over-write with each new listing. the code isn't getting each house for a zip code. It seem the page is only getting the 1st 24 houses for each zip code. Sub Get_Quotes() 'Dim objIE As InternetExplorer Dim lRow As Long Dim abc As String Dim striEst As String Dim ele As Object Set sht = Sheets("Sheet2") RowCount = 1 sht.Range("A" & RowCount) = "adr" sht.Range("B" & RowCount) = "listing" sht.Range("C" & RowCount) = "type type-forSale" sht.Range("D" & RowCount) = "type" sht.Range("E" & RowCount) = "price" RowCount = 2 myzip = "10001" Set objIE = CreateObject("InternetExplorer.Application") With objIE .Visible = True .navigate "http://www.zillow.com/" For lRow = 2 To 2 Do While .Busy Or _ .readyState < 4 DoEvents Loop Set citystate = .document.getElementsByName("citystatezip") citystate.Item(0).Value = myzip .document.getElementById("GOButton").Click Do While .Busy Or _ .readyState < 4 DoEvents Loop For Each ele In .document.all Select Case ele.classname Case "property-info" RowCount = RowCount + 1 Case "adr" sht.Range("A" & RowCount) = ele.innertext Case "listing" sht.Range("B" & RowCount) = ele.innertext Case "type type-forSale" sht.Range("C" & RowCount) = ele.innertext Case "type" sht.Range("D" & RowCount) = ele.innertext Case "price" sht.Range("E" & RowCount) = ele.innertext End Select Next ele Next lRow End With Set objIE = Nothing End Sub "SokerGuy" wrote: Great! This is very helpful. Thank you so much. I have one last question if you have a minute. The info being copied over to Sheet2 from the data dump seems to write over itself each time a new address is entered. It doesn't write to row2, then row3, then row4,... as the process continues down the original address list (since I have an excel list of address I'm trying to automate). Thanks again. "Joel" wrote: This will help you along. I did a dump on sheet 1 of all the properties on the webstire. From this dump I was able to get some of the info. didn't have time to figure out how to get everything. If you need more help let me know. this willget you moving along Sub Get_Quotes() 'Dim objIE As InternetExplorer Dim lRow As Long Dim abc As String Dim striEst As String Dim ele As Object myzip = "10001" Set objIE = CreateObject("InternetExplorer.Application") With objIE .Visible = True .navigate "http://www.zillow.com/" For lRow = 2 To 2 Do While .Busy Or _ .readyState < 4 DoEvents Loop Set citystate = .document.getElementsByName("citystatezip") citystate.Item(0).Value = myzip .document.getElementById("GOButton").Click Do While .Busy Or _ .readyState < 4 DoEvents Loop Set sht = Sheets("Sheet1") RowCount = 1 For Each itm In .document.all sht.Range("A" & RowCount) = itm.tagname sht.Range("B" & RowCount) = itm.classname sht.Range("C" & RowCount) = itm.ID sht.Range("D" & RowCount) = Left(itm.innertext, 1024) RowCount = RowCount + 1 Next itm Set sht = Sheets("Sheet2") RowCount = 1 sht.Range("A" & RowCount) = "adr" sht.Range("B" & RowCount) = "listing" sht.Range("C" & RowCount) = "type type-forSale" sht.Range("D" & RowCount) = "type" sht.Range("E" & RowCount) = "price" RowCount = 2 For Each ele In .document.all Select Case ele.classname Case "property-info" RowCount = RowCount + 1 Case "adr" sht.Range("A" & RowCount) = ele.innertext Case "listing" sht.Range("B" & RowCount) = ele.innertext Case "type type-forSale" sht.Range("C" & RowCount) = ele.innertext Case "type" sht.Range("D" & RowCount) = ele.innertext Case "price" sht.Range("E" & RowCount) = ele.innertext End Select Next ele Next lRow End With Set objIE = Nothing End Sub "SokerGuy" wrote: I have a very similar problem to kenrock, although it's mostly because I'm new at VBA. I'm trying to retrieve home values from zillow. I have an excel sheet with addresses set up and want to automate this process and log the values. My problem comes when the VBA code tries to find the home values in the website HTML. Here is my code, any suggestions would be greatly appreciated. Option Explicit Sub Get_Quotes() Dim objIE As InternetExplorer Dim lRow As Long Dim abc As String Dim striEst As String Dim ele As Object Set objIE = CreateObject("InternetExplorer.Application") With objIE .Visible = True .navigate "http://www.zillow.com/" For lRow = 2 To Range("A" & Rows.Count).End(xlUp).Row Do While .Busy: DoEvents: Loop Do While .readyState < 4: DoEvents: Loop .document.getElementsByName("citystatezip").Item(0 ).Value = Sheets("MAIN").Range("D" & lRow) .document.getElementById("GOButton").Click Do While .Busy: DoEvents: Loop Do While .readyState < 4: DoEvents: Loop Do While .Busy: DoEvents: Loop Do While .readyState < 4: DoEvents: Loop Do While .Busy: DoEvents: Loop Do While .readyState < 4: DoEvents: Loop For Each ele In objIE.document.getElementsByTagName("span") If ele.class = "price" Then abc = ele.innerText: Exit For Next Sheets("MAIN").Range("E" & lRow) = abc Next lRow End With Set objIE = Nothing End Sub "Joel" wrote: Below is a simple example of getting data grom a website. If I had the URL address and the data yo want I can write a similar routine. There are lots of tricks in getting this data and the more experience you have the easier it is. I have helped a number of people get web data. they all have been succesful and happy with the resutls. The more knowledgable you are with VBA programming and HTML format the easier it is. Sub GetZipCodes() ZIPCODE = InputBox("Enter 5 digit zipcode : ") Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "http://zip4.usps.com/zip4/citytown_zip.jsp" 'get web page IE.Navigate2 URL Do While IE.readyState < 4 And _ IE.busy = True DoEvents Loop Set Form = IE.document.getElementsByTagname("Form") Set zip5 = IE.document.getElementById("zip5") zip5.Value = ZIPCODE Set ZipCodebutton = Form(0).onsubmit Form(0).submit Do While IE.busy = True DoEvents Loop Set Table = IE.document.getElementsByTagname("Table") Location = Table(0).Rows(2).innertext IE.Quit MsgBox ("Zip code = " & ZIPCODE & " City/State = " & Location) End Sub "kenrock" wrote: Hi, Attempts to code, in VBA, a means of copying a web page onto an Excel sheet have been frustrating and inconclusive. When I apply the "QueryTables.Add" approach using the URL, I get a partial result but it does not include the data I wish to capture - just static data. If I use the "FollowHyperlink" approach, Internet Explorer is called and the web page is displayed but I cannot see how to code in VBA, "SellectAll" and "Copy" so that I can then "Paste" to the Excel sheet. I have tried this approach in a manual manner using a list of Hyperlinks and calling each Hyperlink, copying the web page and pasting onto individual spreadsheets. Can this be achieved using VBA? TIA, Ken Rock |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto copying Excel spreadsheets | Excel Programming | |||
Adding Multiple Excel Spreadsheets To One Another From Saved HTML Pages | Excel Worksheet Functions | |||
How to publish multiple spreadsheets as interactive HTML pages? | Excel Discussion (Misc queries) | |||
copying excel spreadsheets | Excel Discussion (Misc queries) | |||
Copying Excel page formatting to other pages | New Users to Excel |