VBA + IE - Copy from IE class or tag

30136353

Board Regular
Joined
Aug 14, 2019
Messages
90
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...

VBA Code:
    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

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

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,769
Try this after the submit:
VBA Code:
    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

Board Regular
Joined
Aug 14, 2019
Messages
90
Try this after the submit:
VBA Code:
    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:

VBA Code:
    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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,769
To loop through the column P and put the result in column Q incorporate this into your code:
VBA 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

Board Regular
Joined
Aug 14, 2019
Messages
90

ADVERTISEMENT

To loop through the column P and put the result in column Q incorporate this into your code:
VBA 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?

VBA Code:
    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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,769
Try this:
VBA Code:
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

Board Regular
Joined
Aug 14, 2019
Messages
90
Try this:
VBA Code:
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,769
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,255
Messages
5,641,126
Members
417,195
Latest member
Vishal kumar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top