Populate a field on a webpage and submit and record data

[SmokinAce]

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]

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_pac...ty?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]

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]

The subregion data is on http://oaspub.epa.gov/powpro/ept_pac...2&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/energ...rid/index.html

[SmokinAce]

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]

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.