Programmatically searching websites using VBA
I've already pinched (with permission!) the ideas of Fergus Cairns to explain ways to run automatic searches on popular websites like Google; this blog gives an update on the same topic.

Posted by Andy Brown on 19 September 2014

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Auto-submitting searches to websites in VBA (part 2)

I've previously blogged about this here - this is another bite at the cherry!

Once more, thanks to Fergus Cairns, who provided the ideas and code for this page.

A different approach: just use the entire URL

Where a website has a predictable URL you can use this to run a search.  For example, when I search for owls on the BBC website, it generates this website address:

Owls search BBC

A beautifully simple search URL!

This means that we can build this string and search for it.  As readers of the previous blog will know, the first thing to do is to ensure that you reference the right object library in VBA:

Referencing Microsoft Internet Controls

In VBA, choose Tools / References and tick the library shown above (you may have to scroll a long way to find it).

You can then type in the following code:

Sub SearchBBC()

'what to search for

Dim SearchString As String

SearchString = InputBox( _

"What do you want to search for?", _

"Enter search string", _

"Type search string here")

'create and show a new instance of IE

Dim ie As New InternetExplorer

ie.Visible = True

'use this to find what you're looking for (note: you'll need

'(to remove the spaces from this when copying and pasting)

ie.Navigate "http: // www .bbc .co .uk/search?q=" & SearchString

End Sub

When you run this, you can type in a search string:

Searching for owls

As mentioned above, here we're going for owls ...

The result:

Searching BBC site

The VBA code opens IE and goes to the specified URL.

 

A great solution: simple and easy to understand!

A solution for Google 

The Google search URL isn't quite as simple as the BBC one ...

Google search string

The equivalent URL on my computer is 156 characters long!

However, the following code will work (it's similar to the BBC code above):

Sub SearchGoogle()

'what to search for

Dim SearchString As String

SearchString = InputBox( _

"What do you want to search for?", _

"Enter search string", _

"Type search string here")

'create and show a new instance of IE

Dim ie As New InternetExplorer

ie.Visible = True

'navigate to Google (note: you'll need

'(to remove the spaces from this when copying and pasting)

ie.Navigate "http :// www .google .co. uk/?_rd=ssl#q=" & SearchString

End Sub

Very impressive!  If you search for Owls you get: 

Owl search in Google

Bet they do make good pets really ...

A solution for when you can't see the URL

Some sites (like this one) hide the search string URL:

Searching Wise Owl for owls

Here we've just searched for owls on the Wise Owl site, but you wouldn't know it from the URL.

 

In this case, instead of looking for the HTML element by id, you can sometimes just get a collection of all HTML elements with the right class, and pick out the one you want:

HTML for search box

The HTML for the new Wise Owl website search box.

You can see the underlying HTML for a webpage in most browsers by right-clicking and choosing to view the source, although you'll need to know a bit about HTML to understand the rest of this blog.

The above shows that the search text box has a class of search, and the submit button has a class of search_submit.  We'll gamble that these are the only items on the page with these respective classes:

Sub SearchWiseOwlSite()

'what to search for

Dim SearchString As String

SearchString = InputBox( _

"What do you want to search for?", _

"Enter search string", _

"Type search string here")

'create and show a new instance of IE

Dim ie As New InternetExplorer

ie.Visible = True

'go to website and wait patiently ... (you'll need to remove spaces from URL)

ie.Navigate "www .wise owl. co. uk"

While ie.ReadyState <> 4

DoEvents

Wend

'get the search box by finding all objects on page

'with the correct HTML class

Dim SearchBox As Object

Set SearchBox = ie.Document.GetElementsByClassName("search")

'type text into the first (and probably the only) element

'with this HTML class name on this page

SearchBox(0).Value = SearchString

'now get a reference to the search button

Dim SearchButton As Object

'get a collection of all objects on this page

'with the given class name (there is probably only one)

Set SearchButton = ie.Document.GetElementsByClassName("search__submit")

'click on the first (and presumably only) object in the collection

SearchButton(0).Click

End Sub

However, please don't run too many searches against our site - Google have much deeper pockets (and faster servers) than us!

This blog has 0 threads Add post