This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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 15 September 2014

In this blog

A different approach: just use the entire URL
A solution for Google
A solution for when you can't see the URL

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:

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:

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:

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

The result:

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 ...

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:

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:

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:

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!

if this has whetted your appetite, we run introductory and advanced courses in VBA macros.

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

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

A different approach: just use the entire URL

A solution for Google

A solution for when you can't see the URL

Head office

London

Manchester