VBA + IE - Copy from IE class or tag

[30136353]

Hi Guys,

I have the below VBA which opens a postcode finder website and enters in an address, I then need to copy the returned postcode from the site to my excel workbook. This the part I am struggling with...

    Sub PostCode()

Dim URL As String
Dim IE As Object
Dim objElement As Object
Dim objCollection As Object
Dim HWNDSrc As Long

Set IE = CreateObject("InternetExplorer.Application")

    IE.Visible = True

    URL = "https://postcodefinder.net"

IE.Navigate URL

IE.document.getElementsByName("val")(0).Value = Range("P2")

IE.document.forms(0).submit

    Range("A1") = IE.document.getElementsByClassName("postcode")(1).innerText

    End Sub

HTML CODE

<div class="postcode">Postcode: <b>EH4 4HF</b></div>

 

[John_w]

Try this after the submit:

    Dim postcodeDiv As Object
    Dim parts As Variant, postcode As String

    While IE.Busy Or IE.readyState <> 4: DoEvents: Wend
    Do
        Set postcodeDiv = IE.document.getElementsByClassName("postcode")(0)
        DoEvents
    Loop While postcodeDiv Is Nothing
    parts = Split(postcodeDiv.innerText, "Postcode: ")
    postcode = Split(parts(1), vbCrLf)(0)
    Debug.Print postcode

 

[30136353]

Try this after the submit:

    Dim postcodeDiv As Object
    Dim parts As Variant, postcode As String

    While IE.Busy Or IE.readyState <> 4: DoEvents: Wend
    Do
        Set postcodeDiv = IE.document.getElementsByClassName("postcode")(0)
        DoEvents
    Loop While postcodeDiv Is Nothing
    parts = Split(postcodeDiv.innerText, "Postcode: ")
    postcode = Split(parts(1), vbCrLf)(0)
    Debug.Print postcode

Thanks, that worked a Treat! What would be the best way to now write within the code to loop through every cell in column P starting from P2, down to the last cell with text? My current VBA code looks like this:

    Sub postcode()
    
    Dim URL As String
    Dim IE As Object
    Dim objElement As Object
    Dim objCollection As Object
    Dim HWNDSrc As Long
    
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
    URL = "https://postcodefinder.net"
    IE.Navigate URL
    Sleep 300
    
    IE.document.getElementsByName("val")(0).Value = Range("P2")
    IE.document.forms(0).submit
    Sleep 250
    Dim postcodeDiv As Object
    Dim parts As Variant, postcode As String
        Do
        Set postcodeDiv = IE.document.getElementsByClassName("postcode")(0)
        DoEvents
    Loop While postcodeDiv Is Nothing
    parts = Split(postcodeDiv.innerText, "Postcode: ")
    postcode = Split(parts(1), vbCrLf)(0)
    Range("Q2").Value = postcode
    
    End Sub

 

[John_w]

To loop through the column P and put the result in column Q incorporate this into your code:

    Dim lastRow As Long, r As Long

    With ActiveSheet
        lastRow = .Cells(.Rows.Count, "P").End(xlUp).Row
        For r = 2 To lastRow
            'IE navigate here, then:
            theSearchInput.Value = .Cells(r, "P").Value
            'Submit form here
            'Get postcodeDiv here, then:
            .Cells(r, "Q").Value = Split(postcodeDiv.innerText, "Postcode: ")(1)
        Next       
    End With

 

[30136353]

To loop through the column P and put the result in column Q incorporate this into your code:

    Dim lastRow As Long, r As Long

    With ActiveSheet
        lastRow = .Cells(.Rows.Count, "P").End(xlUp).Row
        For r = 2 To lastRow
            'IE navigate here, then:
            theSearchInput.Value = .Cells(r, "P").Value
            'Submit form here
            'Get postcodeDiv here, then:
            .Cells(r, "Q").Value = Split(postcodeDiv.innerText, "Postcode: ")(1)
        Next      
    End With

Hi john, Sorry for the late reply as I am just back at work now. I had to change the search now from a single cell to now include multiple cells etc. The code works perfect for the first cell but I ams treuggling to get this to loop through at all?

    Sub postcode()

Dim URL As String
Dim IE As Object
Dim objElement As Object
Dim objCollection As Object
    Dim HWNDSrc As Long

    Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
URL = "https://postcodefinder.net"

IE.Navigate URL
    Sleep 300

    IE.document.getElementsByName("val")(0).Value = Range("D2") & " " & Range("E2") & "," & "Edinburgh"
IE.document.forms(0).submit
Sleep 250
Dim postcodeDiv As Object
Dim parts As Variant, postcode As String
Do
Set postcodeDiv = IE.document.getElementsByClassName("postcode")(0)
DoEvents
Loop While postcodeDiv Is Nothing
parts = Split(postcodeDiv.innerText, "Postcode: ")
postcode = Split(parts(1), vbCrLf)(0)
Range("F2").Value = postcode

    End Sub

Thanks for the help.

 

[John_w]

Try this:

Public Sub IE_Postcode_Search()

    Dim IE As Object
    Dim HTMLdoc As Object
    Dim searchInput As Object
    Dim postcodeDiv As Object
    Dim lastRow As Long, r As Long
   
    Set IE = CreateObject("InternetExplorer.Application")
       
    With ActiveSheet
   
        lastRow = .Cells(.Rows.Count, "P").End(xlUp).Row
       
        For r = 2 To lastRow
       
            With IE
                .Visible = True
                .navigate "https://postcodefinder.net/"
                While .Busy Or .readyState <> 4: DoEvents: Wend
                Set HTMLdoc = .document
            End With
   
            Do
                Set searchInput = HTMLdoc.getElementsByName("val")(0)
                DoEvents
            Loop While searchInput Is Nothing
           
            searchInput.Value = .Cells(r, "P").Value
   
            HTMLdoc.forms(0).submit
       
            While IE.Busy Or IE.readyState <> 4: DoEvents: Wend
            While HTMLdoc.readyState <> "complete": DoEvents: Wend
            Do
                Set postcodeDiv = IE.document.getElementsByClassName("postcode")(0)
                DoEvents
            Loop While postcodeDiv Is Nothing
           
            .Cells(r, "Q").Value = Split(postcodeDiv.innerText, "Postcode: ")(1)
           
        Next
       
    End With
   
End Sub

 

[30136353]

Try this:

Public Sub IE_Postcode_Search()

    Dim IE As Object
    Dim HTMLdoc As Object
    Dim searchInput As Object
    Dim postcodeDiv As Object
    Dim lastRow As Long, r As Long
  
    Set IE = CreateObject("InternetExplorer.Application")
      
    With ActiveSheet
  
        lastRow = .Cells(.Rows.Count, "P").End(xlUp).Row
      
        For r = 2 To lastRow
      
            With IE
                .Visible = True
                .navigate "https://postcodefinder.net/"
                While .Busy Or .readyState <> 4: DoEvents: Wend
                Set HTMLdoc = .document
            End With
  
            Do
                Set searchInput = HTMLdoc.getElementsByName("val")(0)
                DoEvents
            Loop While searchInput Is Nothing
          
            searchInput.Value = .Cells(r, "P").Value
  
            HTMLdoc.forms(0).submit
      
            While IE.Busy Or IE.readyState <> 4: DoEvents: Wend
            While HTMLdoc.readyState <> "complete": DoEvents: Wend
            Do
                Set postcodeDiv = IE.document.getElementsByClassName("postcode")(0)
                DoEvents
            Loop While postcodeDiv Is Nothing
          
            .Cells(r, "Q").Value = Split(postcodeDiv.innerText, "Postcode: ")(1)
          
        Next
      
    End With
  
End Sub

John, nothing happened when I started the Macro, I also changed the code as shown earlier where the actual search input was multiple cells across the same row... Thanks for the help

 

[John_w]

You've moved the goalposts. Per your post no.3, my code loops through address cells in column P starting at P2 and puts the postcode result in the column Q cell.