Consulting

Results 1 to 5 of 5

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

  1. #1
    VBAX Newbie
    Joined
    Nov 2020
    Posts
    3
    Location

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

    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
    Last edited by 98aallen; 11-12-2020 at 03:06 PM.

  2. #2
    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
    -------------------------------------------------
    The more details you give, the easier it is to understand your question. Don't save the effort, tell us twice rather than not at all. The amount of info you give strongly influences the quality of answer, and also how fast you get it.

  3. #3
    VBAX Newbie
    Joined
    Nov 2020
    Posts
    3
    Location
    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?

  4. #4
    VBAX Newbie
    Joined
    Nov 2020
    Posts
    3
    Location
    oh ive noticed its only returning the results for the first to postcodes and returning the same result for all of the below postcodes.

  5. #5
    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.)
    -------------------------------------------------
    The more details you give, the easier it is to understand your question. Don't save the effort, tell us twice rather than not at all. The amount of info you give strongly influences the quality of answer, and also how fast you get it.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •