PDA

View Full Version : Populate a field on a webpage and submit and record data



SmokinAce
06-16-2010, 07:35 AM
Populate a field on a webpage and submit and record data

Site
epa.gov/cleanenergy/energy-and-you/how -clean.html

Data
In excel I have a list of about 100 zip codes I need to run (A1:A107)
Goal
Macro to open the site put in the zip code for every cell (A1:A107) then submit it. Then it needs to record the data that appears in the dropdown on the next page next to each zip code (most zips only have 1 but some have more so record them in different columns like b, c, d, etc…). Is this possible? I tried a few different codes but have been unable to get it to work. I have another site I need to do the same thing on for other data and when the next client comes I will have to be doing it over and over so I thought there has to be a way to do it. I appreciate all the help that you guys can give I have found many answers here in the past but this one is beyond me.

Crocus Crow
06-16-2010, 03:11 PM
There's no need to populate the text box and submit that page. Instead, you can specify the zip code directly in the results page URL, e.g. http://oaspub.epa.gov/powpro/ept_pack.utility?p_zip=32542

Therefore:

Option Explicit

Dim XMLhttp As Object

Sub Get_Zipcode_Data()

Dim cell As Range

For Each cell In Range("A1:A107")
Get_Electricity_Companies cell.Value, cell.Offset(0, 1)
DoEvents
Next

Set XMLhttp = Nothing

End Sub

Private Sub Get_Electricity_Companies(zipcode As String, destinationCell As Range)

Dim URL As String
Dim HTMLdoc As Object
Dim SelectCompany As Object
Dim i As Integer

URL = "http://oaspub.epa.gov/powpro/ept_pack.utility"

If XMLhttp Is Nothing Then
Set XMLhttp = CreateObject("Microsoft.XMLHTTP")
End If

Set HTMLdoc = CreateObject("HTMLFile")

With XMLhttp
.Open "GET", URL & "?p_zip=" & zipcode, False
.send

'Load response into a HTMLdocument object

HTMLdoc.body.innerHTML = .responseText
End With

'Get the Select element and loop through its option text

Set SelectCompany = HTMLdoc.all.Item("p_egcid")
If Not SelectCompany Is Nothing Then
For i = 0 To SelectCompany.Length - 1
destinationCell.Offset(0, i).Value = SelectCompany(i).innerText
Next
End If

End Sub

SmokinAce
06-16-2010, 04:29 PM
That is great! I was close but this makes more sense then the way I was going.

I am almost there I realized I need 1 more step though. When you hit Next after you choose the zip and the provider it shows you "eGRID Subregion: SERC South (which includes the ZIP code: 32542)" on that next page.

Would there be a way to pull that EGrid Subregion number? and put it next to the zip and provider?

Crocus Crow
06-16-2010, 04:55 PM
The subregion data is on http://oaspub.epa.gov/powpro/ept_pack.charts?p_zip=32542&p_egcid=7801, where the 7801 comes from the SelectCompany(i).Value element, so you could do something similar to my code to request that page and extract the subregion name from it.

However, instead of all this web scraping, have you looked at the downloads to see if the data you require is available there? http://www.epa.gov/cleanenergy/energy-resources/egrid/index.html

SmokinAce
06-16-2010, 04:57 PM
I have looked the data I need say it is forthcoming and I know how the EPA is about getting items up quick and my client cant wait.

SmokinAce
06-29-2010, 02:17 PM
I have been working this and can't get it correct. I reworded prev data to look like

Column A= Zip B=Provider and I want C to have the Subregion.
Anyone have quick thoughts on this I think it is something with the web address. I will try to post the code later when I can get to the file I am working on but I am really stuck.

Crocus Crow I wanted to say thanks again for helping with the first part.