CODEWITHSUNDEEP
vbaexcelinternet-explorer
JamieB
JamieB

Reputation: 247

Copying from Internet Explorer text area (box) but into more than a single cell

I'm currently trying to control/automate a postcode looking website from postcodes stored and updated in Excel, and my code works perfectly up to the point it has to copy the data once it's finished. For the life of me I can't figure out how to copy the data from the text box / area into Excel without it just putting it ALL into one cell (Text to Columns doesn't really work either).

The website is : http://www.doogal.co.uk/DrivingDistances.php 

Sub Geo2()
    Dim sht As Worksheet
    Dim IE As Object
    'Dim ieDoc As HTMLDocument
    Dim Item As Variant
    Dim objElement As Object
    Dim startLoc As String
    Dim endLoc As String
    Dim x As Integer
    Dim objNotes As Object
    Dim strNotes As String
    Dim str As String
    'Dim SignInButton As HTMLInputButtonElement
    Set sht = ThisWorkbook.Sheets("Postcode")
    Set IE = CreateObject("InternetExplorer.Application")

    'Open IE
    IE.Visible = True
    IE.Navigate "http://www.doogal.co.uk/DrivingDistances.php"

    'Wait until site is loaded
    Do While IE.ReadyState <> 4 'READYSTATE_COMPLETE
        DoEvents
    Loop

    IE.Document.getElementbyID("startLocs").Value = "dn1 5pq" 'random postcode       
    IE.Document.getElementbyID("endLocs").Value = "wf12 2fd"   'random postcode            
    IE.Document.getElementsByName("calculateFor").Item(1).Checked = True
    IE.Document.getElementsByName("units").Item(1).Checked = True   
    IE.Document.getElementsByClassName("btn btn-primary").Item(0).Click

------
'Ive tried without having it as a object and using .value but it either comes with only the first line or the entire thing rammed into a string and is unusable

----Code here is the problem-----
        ***Set objNotes = IE.Document.getElementbyID("distances")
        str = objNotes.Value***
---------        
            Do While IE.ReadyState <> 4 'READYSTATE_COMPLETE
            DoEvents
        Loop    

    End Sub

Upvotes: 0

Views: 105

Answers (2)

davidmneedham
davidmneedham

Reputation: 374

The following VBA function uses the Google Maps Directions API to calculate the driving distance in meters between two locations. The code is modified from a version submitted by barrowc on this similar question.

Make sure to add a reference in Excel to Microsoft XML, v6.0. 

Function getDistance(origin As String, destination As String) As String

Dim xhrRequest As XMLHTTP60
Dim domDoc As DOMDocument60
Dim ixnlDistanceNode As IXMLDOMNode
Dim RequestString As String
Dim API_Key As String

' Insert your own Google Maps Directions API key here
API_Key = "XXXXXX"

' Read the data from the website
Set xhrRequest = New XMLHTTP60
RequestString = "https://maps.googleapis.com/maps/api/directions/xml?origin=" _
 & origin & "&destination=" & destination & "&sensor=false&key=" & API_Key
xhrRequest.Open "GET", RequestString, False
xhrRequest.send

' Copy the results into a format we can manipulate with XPath
Set domDoc = New DOMDocument60
domDoc.LoadXML xhrRequest.responseText

' Select the node called value underneath the leg and distance parents.
' The distance returned is the driving distance in meters.
Set ixnlDistanceNode = domDoc.SelectSingleNode("//leg/distance/value")
getDistance = ixnlDistanceNode.Text

Set ixnlDistanceNode = Nothing
Set domDoc = Nothing
Set xhrRequest = Nothing

End Function

Please note that this code by itself violates the Terms of Use of Google's API. "The Google Maps Directions API may only be used in conjunction with displaying results on a Google map; using Directions data without displaying a map for which directions data was requested is prohibited."1

Upvotes: 1

Tim
Tim

Reputation: 2892

Instead of putting the data all in one string, Split the string into an array, then loop through the array like this:

Set objNotes = IE.Document.getElementbyID("distances")
Dim x as Integer
Dim aDist() as Variant
aDist = Split(objNotes.Value, vbNewLine) 'May need to be vbCr or vbLf or vbCrLf
For x = 0 to Ubound(aDist) - 1
    debug.print aDist(x)
Next x

Upvotes: 0

Related Questions