How Do I Loop Through An Excel Spreadsheet With VBA, Paste A Value To A Website Form Then Extract The Result Back?

Question

I am trying to loop through a list of names in an Excel spreadsheet using VBA and I would like to paste a surname into a text box on a webpage and then extract the returned phone number into a cell after the page has been submitted.

I can only do this one name at a time so I would like to automate this process as there are several hundreds of them. I can do the Excel looping but I don't know how to interact with the webpage.

Can anybody help, or point me in the right direction please?

Regards, Ian.

Answer 1

Hi Ian, this should help.

As an example, I've used one of the millions of random name generators that exist on the 'Net. This code opens an IE instance, navigates to "http://www.behindthename.com/random/", inputs the surname to the form, submits the form, and returns the resulting HTML body.

I haven't parsed the result page HTML to return the actual name, as this will be different for your site, but this should help you get started.

You'll need a reference to "Microsoft Internet Controls" to access the SHDocVw.InternetExplorer object.

Private Function GetFromSubmittedForm(strSurname As String) As String
Dim strReturnBody As String

'Requires reference to "Microsoft Internet Controls"'
Dim IE As SHDocVw.InternetExplorer
Set IE = CreateObject("InternetExplorer.Application")


'Navigate to the URL'
IE.Navigate "http://www.behindthename.com/random/"

'No need to show the window'
IE.Visible = False

'Wait for IE to load the page'
While IE.Busy: DoEvents: Wend
Do While IE.ReadyState <> 4: DoEvents: Loop


'NOTE: I have refered to each element by name, but indexes _
       can also be used: Forms("random") could be Forms(0)'


'Select the form by name:'
With IE.Document.Forms("random")

    'Reference each Input by name'
    .Surname.Value = strSurname

    'Submit the form'
    .Submit

    'Sometimes you may need to submit using _
        the name of the submit button like this:'
    '.SubmitButtonName.Click'
End With


'Wait for IE to load the new page'
While IE.Busy: DoEvents: Wend
Do While IE.ReadyState <> 4: DoEvents: Loop


'Set the body of the new page to a string'
strReturnBody = IE.Document.Body.InnerHTML



'/CODE THAT ANALYSES THE BODY HTML GOES HERE'

GetFromSubmittedForm = strReturnBody

'\CODE THAT ANALYSES THE BODY HTML GOES HERE'



'Close the IE doc'
IE.Document.Close
IE.Quit
End Function

Hope this helps.