Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Lookup customer name on IE and return address using VBA

  1. #1
    Board Regular
    Join Date
    Apr 2015
    Posts
    168
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Lookup customer name on IE and return address using VBA

    Hi,

    I have a list of business names in Excel starting in cell A2 going down to A3, A4 etc. The record-set is pretty large as it contains over 100,000 rows. What I need to do is to retrieve the address for these business using IE and return the corresponding address in cell B2, B3 etc..

    I'm pretty new in the coding world and was wondering if there is any VBA code you may think of I could use to perform this task automatically.

    Thank you in advance for your valuable help.

  2. #2
    Board Regular
    Join Date
    Apr 2015
    Posts
    168
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup customer name on IE and return address using VBA

    Hi,

    I found a code that looks similar to what I'm attempting to do. I acquired a google API key, however, it keeps on returning the same address in column 15. I feel I'm close to the end result but I would need your help to further understand what is wrong in the below code.

    Code:
    Sub myTest()
        Dim xhrRequest As XMLHTTP60
        Dim domDoc As DOMDocument60
        Dim domDoc2 As DOMDocument60
        Dim placeID As String
        Dim query As String
        Dim nodes As IXMLDOMNodeList
        Dim node As IXMLDOMNode
    
    
        Dim rng As Range, cell As Range
    
    
        Set rng = Range("L2:L100")
    
    
        For Each cell In rng
    On Error Resume Next
    
    
        query = cell.Value
    
    
        Dim googleKey As String
        googleKey =
    
    
        Set xhrRequest = New XMLHTTP60
    
    
        xhrRequest.Open "GET", "https://maps.googleapis.com/maps/api/place/textsearch/xml?" & _
            "query=" & query & "&key=" & googleKey, False
        xhrRequest.send
    
    
        Set domDoc = New DOMDocument60
        domDoc.LoadXML xhrRequest.responseText
        
        placeID = domDoc.SelectSingleNode("//result/place_id").Text
    
    
        Set domDoc = Nothing
        Set xhrRequest = Nothing
    
    
        Set xhrRequest = New XMLHTTP60
        xhrRequest.Open "GET", "https://maps.googleapis.com/maps/api/place/details/xml?placeid=" & placeID & _
        "&key=" & googleKey, False
        xhrRequest.send
    
    
        Set domDoc = New DOMDocument60
        domDoc.LoadXML xhrRequest.responseText
    
    
        Dim output As String
        Dim s As String
    
    
        Set nodes = domDoc.SelectNodes("//result/address_component/type")
        For Each node In nodes
            s = node.Text
            If s = "street_number" Then
                cell.Offset(0, 1).Value = "Address: " & node.ParentNode.FirstChild.Text
            End If
    
    
            If s = "postal_code" Then
                cell.Offset(0, 2).Value = "Postal Code: " & node.ParentNode.FirstChild.Text
            End If
        Next node
        
        Cells(cell.Row, 15) = domDoc.SelectSingleNode("//result/formatted_address").Text & output
        
        Next cell
    
    
    End Sub
    Last edited by TG2812; Apr 20th, 2019 at 09:08 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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