CODEWITHSUNDEEP
excelvba
Elham
Elham

Reputation: 11

Excel, VBA and web automation

I have a very big Excel spreadsheet that includes addresses. I want to write a code to automatically open https://www.greatschools.org/, one by one paste addresses in the search box, get the name of best schools for this address, and paste them into the Excel file.

I use this piece of code to paste one of the addresses in the web page but when it pastes and push the search button it doesn't show me any result.

Sub SearchBot()

'dimension (declare or set aside memory for) our variables
Dim objIE As InternetExplorer
'special object variable representing the IE browser

Dim aEle As HTMLLinkElement
'special object variable for an <a> (link) element

Dim y As Integer
'integer variable we'll use as a counter

Dim result As String
'string variable that will hold our result link

Set objIE = New InternetExplorer
objIE.Visible = True
objIE.navigate "https://www.greatschools.org/"

Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop

objIE.document.getElementsByClassName("form-control")(0).Focus

objIE.document.getElementsByClassName("form-control")(0).Value = _
  Sheets("Sheet1").Range("A1").Value
'click the 'go' button
objIE.document.getElementsByClassName("input-group-btn")(0).Click

Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop

I even added the above line code to give it more time to find result but this doesn't help me at all.

At first, it pastes the address in the search box and after a while the address disappears from the search box and there is no result for me there.

Can anybody help me solving this problem? Thank you.

Upvotes: 1

Views: 235

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27239

These two lines between the Do While loops worked for me:

objIE.Document.getElementsByClassName("form-control")(1).Value = Sheets("Sheet1").Range("A1").Value
'click the 'go' button
objIE.Document.getElementsByClassName("btn search-btn")(0).Click

Notice the use of (1) index on form-control entry.

Upvotes: 1

Related Questions