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.
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 <<<
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.
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
Last edited by a moderator: