Excel, VBA and web automation

Question

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.

not sure exactly what the problem is here but if you simply URLEncode your value from A1, you can pass it directly to a URL as a parameter: "https:/www.greatschools.org/search/search.page?" & UrlEncode([A1]) & "&distance=5" and then navigate directly to that. — David Zemens
– David Zemens, Commented Jan 22, 2018 at 21:26
If you need the UrlEncode function, check google or use the search feature here. — David Zemens
– David Zemens, Commented Jan 22, 2018 at 21:26

Scott Holtzman · Accepted Answer · 2018-01-22 21:31:21Z

1

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.

answered Jan 22, 2018 at 21:31

Scott Holtzman

27.3k5 gold badges41 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel, VBA and web automation

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related