VBA code - input data to website and retrieve result in excel

[98aallen]

Hope someone can help. I'm very new to VBA so will ideally need a copy and paste job if possible please.

I have loads of data in excel with postcodes to and from and want to input them into a website and return the distance the website gives in the results.

Column A - has from postcodes (UK postcodes) - example B10 0AX (zip codes for my US friends)
Column B - has to postcodes (UK Postcodes) - example B70 7LB (zip codes for my US friends)

I need to input these into the relevant boxes on https://www.theaa.com/driving/mileage-calculator.jsp

Once the "get route" button has been pressed it returns the distance in miles and kilometres and also the expense in £ pounds

Using my examples it returns 9.02 miles - 14.524 kilometres and £1.26

I have thousands of postcodes to enter for the to and from and need this data to be pulled into an excel sheet somehow.

Please please can anyone help

EDIT : trying to be clearer.

i have an excel spreadsheet containing 2 columns of data and thousands of rows. i need each row to be input on the AA website above and then the get route button to be pressed so that it returns the results, and i need those results in excel in columns next to the data in columns A and B.

I have tried for hours, searched videos and forums and have got no where but i do believe its possible from what i am reading, i just cant figure it out

[JimmyTheHand]

This code might work for you.
Change the references in the red lines to fit your case.


Sub GetDistances()
Dim IE As Object
Dim wsSource As Worksheet, rngSource As Range, c As Range
Dim fldFrom As Object, fldTo As Object, fldDist As Object, fldCost As Object, Cost As String, arrDist

Set wsSource = ThisWorkbook.Worksheets("Sheet1")
Set rngSource = wsSource.Range("A1:A15")

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.Navigate "https://www.theaa.com/driving/mileage-calculator.jsp"
While (IE.Busy) Or (IE.ReadyState <> 4)
DoEvents 'pass control to other applications
Wend

For Each c In rngSource
Set fldFrom = IE.Document.all.Item("routeFrom")
fldFrom.Value = c.Value
While (IE.Busy) Or (IE.ReadyState <> 4)
DoEvents 'pass control to other applications
Wend

Set fldTo = IE.Document.all.Item("routeTo")
fldTo.Value = c.Offset(, 1).Value
While (IE.Busy) Or (IE.ReadyState <> 4)
DoEvents 'pass control to other applications
Wend

Call IE.Document.parentWindow.execScript("aaMC.fn.onGetRouteClicked()", "JavaScript")
Set fldDist = Nothing
While fldDist Is Nothing
Set fldDist = IE.Document.all.Item("routeDistanceTotal")
Wend
arrDist = Split(fldDist.innertext, vbCrLf)
c.Offset(, 2) = arrDist(0)
c.Offset(, 3) = arrDist(1)

Set fldCost = IE.Document.all.Item("routeExpenseTotalRow")
Cost = fldCost.innertext
Cost = Trim(Mid(Cost, 14))
Cost = Replace(Cost, vbCrLf, "")
Cost = Replace(Cost, vbCr, "")
c.Offset(, 4) = Cost

IE.Document.all.Item("mcDeleteRoutesLink").Click
While (IE.Busy) Or (IE.ReadyState <> 4)
DoEvents 'pass control to other applications
Wend

Application.Wait (Now + 1 / 24 / 60 / 60)
Next
IE.Quit
End Sub

[98aallen]

it doesnt seem to work - it returns the same result for all the rows - i need it to enter say cell a1 and b1 and return the results and then do a2 and b2 and return those results and so on. I think the range data may be the issue wrote as a range? A1:B30?

[98aallen]

oh ive noticed its only returning the results for the first to postcodes and returning the same result for all of the below postcodes.

[JimmyTheHand]

The macro needs only the FROM data range. Try A1:A30.
Or else, please give me a few valid postcode examples so that I can test the macro with them. (I'm not from the UK, and have no idea how these postcodes are structured.)