• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA navigating internet explorer

GBurke

New Member
Hi there,
I have attached a file that contains the code below.

It opens an instance of IE
It naviagtes to the desired website
It enters data into a search field
It executes the search

The problem I now have is that the results of the search appear over 4 pages. I need to navigate to each page. I have no idea how to do that.

74083

The search value is Happy Hearts Australia. It returns 127 matches.

I think I need to establish how many pages there are, as there may be just the one, or could be many.

You assistance would be much appreciated.

>>> use code - tags <<<
Code:
Sub GetABNs()
    Call Deletedata
   
    Dim objIE As InternetExplorer 'special object variable representing the IE browser
    Dim y As Integer 'integer variable we'll use as a counter
    Dim n As Integer 'integer variable we'll use as a counter
    Dim pages As Object

    Dim intRrow As Integer
    Dim intProw As Integer
   
    'initiating a new instance of Internet Explorer and asigning it to objIE
    Set objIE = New InternetExplorer

    'make IE browser visible (False would allow IE to run in the background)
    objIE.Visible = True

    'navigate IE to this web page
    objIE.navigate "http://abr.business.gov.au/"

    'wait here a few seconds while the browser is busy
    Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop

    'in the search box put business name in
    n = 0
    For Each itm In objIE.document.all
        If itm = "[object HTMLInputElement]" Then
            n = n + 1
            If n = 1 Then
                itm.Value = Sheets("Input").Range("A1").Value
            End If
        End If
    Next
   
    'click the 'go' button
    n = 0
    For Each itm In objIE.document.all
        If itm = "[object HTMLInputElement]" Then
            n = n + 1
            If n = 3 Then
                If itm.Name = "ctl00$ContentPagePlaceholder$SearchBox$MainSearchButton" Then
                    itm.Click
                End If
            End If
        End If
    Next
   
    'wait again for the browser
    Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop

    '*** this is where I need to establish how many pages in the results
    '*** then move to each page, to copy the data
   
    Sheets("Data").Select
    y = 1
   
    ''For Each Page In pages
       
        'copy the webpage results
        SendKeys "^a"
        SendKeys "^c"
           
        Application.Wait Now + TimeValue("00:00:01")
   
        DoEvents
   
        'paste the results onto the data tab
        Application.Wait Now + TimeValue("00:00:01")
   
        Range("A" & y).Select
        ActiveSheet.Paste
       
        DoEvents
        'move the row counter up by 100
        y = y + 100
    ''Next Page
   
    'close the browser
    objIE.Quit
   
    Err.Clear
    'delete all shapes
    Call DeleteAllShapes
   
    'get rid of unwanted rows at top of tab
    'will need to do this multiple times, depending on number of pages in results
   
    Range("A1:A50").Select
    Selection.EntireRow.Delete
    intRrow = 2
    intProw = 2

    Do While Len(Sheets("data").Range("A" & intRrow).Value) > 0
        Sheets("Output").Range("A" & intProw).Value = Sheets("Data").Range("A" & intRrow).Value
        Sheets("Output").Range("B" & intProw).Value = Sheets("Data").Range("B" & intRrow).Value
        Sheets("Output").Range("C" & intProw).Value = Sheets("Data").Range("A" & intRrow + 1).Value
        Sheets("Output").Range("D" & intProw).Value = Sheets("Data").Range("C" & intRrow).Value
        Sheets("Output").Range("E" & intProw).Value = Sheets("Data").Range("D" & intRrow).Value
        intProw = intProw + 1
    intRrow = intRrow + 2
    Loop

'exit our SearchBot subroutine
End Sub
 

Attachments

  • Get ABN No based on names.xlsm
    379.8 KB · Views: 3
Last edited by a moderator:
Hi,​
first according to forum rules :​
  • when creating a thread it must be in the appropriate section
  • when posting a code you must use the code tags via the 3 dots icon.
As I never answer to any thread where is a broken rule or if I see again the same behavior I can delay my answer for days.​
A moderator has moved your thread and has added the code tags but now you know …​
According to your attachment and to the easy webpage code, the useless 'Data' worksheet can be deleted,​
a VBA demonstration as an aussie beginner starter :​
Code:
Sub Demo1()
    Dim R&, T$(), P%, S$, C%, L&, oNxt As Object
     If Sheet1.[A1].Text = "" Then Beep: Exit Sub
        Sheet2.UsedRange.Clear
        Application.Cursor = xlWait
        On Error GoTo Fin
    With CreateObject("InternetExplorer.Application")
           .Navigate "https://abr.business.gov.au/Search/ResultsActive?SearchText=" & Sheet1.[A1].Text
            R = 1
            ReDim T(1 To Rows.Count, 3)
        Do
                P = P + 1
                Application.StatusBar = "       Downloading  page " & P & S
                While .Busy Or .ReadyState < 4:  DoEvents:  Wend
            With .Document.getElementsByTagName("TABLE")(0).Rows
                    If R = 1 Then For C = 0 To 3: T(1, C) = .Item(0).Cells(C).innerText: Next
                For L = 1 To .Length - 1
                    R = R + 1
                    T(R, 0) = Trim(.Item(L).Cells(0).Children(0).innerText)
                For C = 1 To 3
                    T(R, C) = Trim(.Item(L).Cells(C).innerText)
                Next C, L
            End With
               Set oNxt = .Document.querySelector("Button[rel='next']")
            If Not oNxt.disabled Then
                If S = "" Then S = " / " & oNxt.ParentNode.PreviousSibling.Children(0).Value
                   oNxt.Click
            End If
        Loop Until oNxt.disabled
Fin:
        If Err.Number <> -2147023706 Then .Quit
    End With
        Set oNxt = Nothing
        If R Then With Sheet2.[A1:D1].Resize(R): .VerticalAlignment = xlCenter: .IndentLevel = 1: .Value2 = T: End With
        Application.Cursor = xlDefault
        Application.StatusBar = False
    If Err.Number Then
        With Sheet2.Cells(R + 1, 1)
            .Resize(, 2).Value2 = Array(Err.Number, Err.Description)
             Application.Goto .Cells, True:  Beep
        End With
    End If
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
Thank you so much for:-
a) fixing up my rule breaking. A newbie here so I apologize.
b) providing the solution to my problem. Works like a dream
 
Thanks.​
Works just following the easy logic of this website, nothing hidden …​
 
I do have a follow up question.
Would the above code work on another webpage? If not, how do I establish how to replace
Code:
With .Document.getElementsByTagName("TABLE")(0).Rows
 
Last edited by a moderator:
This codeline aims the rows of the first table of the webpage so it may work on another webpage in the same context​
but must be amended for other cases …​
 
Back
Top