VBA web query, cannot use URL

[ROMaster2]

Hello!

I've been working as a volunteer for a political group in Washington state and have been trying to write a script that would fetch the voting districts for each address in a table. Normally this would be fairly easy, but when you search for an address in here: http://www5.kingcounty.gov/kcgisreports/dd_report.aspx , the URL generates the parcel number, not the address I enter. I'm trying to figure out how to have excel enter an address on the table to search, copy and paste the page onto excel, paste the values of the voting district, delete the page, and loop onto the next address. If the address doesn't go straight to the page, skip it (so I will do it manually). I used http://www.mrexcel.com/forum/showthread.php?t=534042 to help me along for this. Here's what I have so far:

Sub FetchVotingDistricts()

    Const cURL = "http://www5.kingcounty.gov/kcgisreports/dd_report.aspx"
    Const cUsername = "226 12TH ST SE"
    
    Dim IE As InternetExplorer
    Dim doc As HTMLDocument
    Dim LoginForm As HTMLFormElement
    Dim UserNameInputBox As HTMLInputElement
    Dim PasswordInputBox As HTMLInputElement
    Dim SignInButton As HTMLInputButtonElement
    Dim HTMLelement As IHTMLElement
    Dim qt As QueryTable
        
    Set IE = New InternetExplorer
    
    IE.Visible = True
    IE.Navigate cURL
    
    'Wait for initial page to load
    
    Do While IE.ReadyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
    
    Set doc = IE.Document
    
    'Get the only form on the page
    
    Set LoginForm = doc.forms(0)
    
    'Get the User Name textbox and populate it
  
    Set UserNameInputBox = LoginForm.elements("DistrictsReportControl1$AddressSearch1$txbAddress")
    UserNameInputBox.Value = cUsername
    
    'Get the form input button and click it
    
    Set SignInButton = LoginForm.elements("DistrictsReportControl1$AddressSearch1$btnSearch")
    SignInButton.Click
            
    'Wait for the new page to load
    
    Do While IE.ReadyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
    
    IE.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
    IE.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT
    Range("A3").Select
    Selection.Paste

End Sub

I'm only using the "226 12TH ST SE" to get it started, which I would fix once it worked.
It debugs at the "UserNameInputBox.Value = cUsername".

Hope that makes any sense, I'd really appreciate any help.

 

[wigi]

Hi there

You can use:

Set UserNameInputBox = doc.getElementsByName("DistrictsReportControl1$AddressSearch1$txbAddress")(0)

 

[wigi]

Or, preferably:

Sub FetchVotingDistricts()

    Const cURL = "http://www5.kingcounty.gov/kcgisreports/dd_report.aspx"
    Const cUsername = "226 12TH ST SE"

    Dim PasswordInputBox As HTMLInputElement
    Dim SignInButton As HTMLInputButtonElement
    Dim HTMLelement As IHTMLElement
    Dim qt As QueryTable

    With New InternetExplorer

        .Visible = True
        .Navigate cURL

        'Wait for initial page to load
        Do While .ReadyState <> READYSTATE_COMPLETE Or .Busy: DoEvents: Loop

        With .Document
        
            'Get the User Name textbox and populate it
            .getElementsByName("DistrictsReportControl1$AddressSearch1$txbAddress")(0).Value = cUsername
            
        End With
    
    End With

    '...

End Sub

Once it's all working, I would not set .Visible to True.

 

[ROMaster2]

Thanks a lot for that. I've only recently have been working with VBA so searching to find what commands to use has been quite the grind.

Here's what I got so far, building of what you posted:

Sub FetchVotingDistricts3()

    Const cURL = "http://www5.kingcounty.gov/kcgisreports/dd_report.aspx"
    Const cStreet = "226 12TH ST SE"

    Dim HTMLelement As IHTMLElement
    Dim qt As QueryTable

    With New InternetExplorer

        .Visible = True
        .Navigate cURL

        'Wait for initial page to load
        Do While .ReadyState <> READYSTATE_COMPLETE Or .Busy: DoEvents: Loop

        With .Document
        
            'Get the Address textbox and populate it
            .getElementsByName("DistrictsReportControl1$AddressSearch1$txbAddress")(0).Value = cStreet
            
            'Hit the Go button
            .getElementsByName("DistrictsReportControl1$AddressSearch1$btnSearch")(0).Click
            
        End With
            
        Do While .ReadyState <> READYSTATE_COMPLETE Or .Busy: DoEvents: Loop
            
        .ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
        .ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT
        
        Selection.Paste
    
    End With

    '...

End Sub

When you hit the Go button, it takes it a moment to load the page. I'll need it to just wait for a bit until it stops loading, then select the page and paste the values onto excel, on a new blank sheet.

It's bugging on Selection.Paste, which I doubt is the correct one anyways.

 

[wigi]

EDIT: removed, comment was incorrect.

I will look at it ths evening, European time.

 

[ROMaster2]

Bumping

EDIT: removed, comment was incorrect.

I will look at it ths evening, European time.

Well this stinks, we're nearly at opposite ends of the world.