Internet Explorer.Application For VBA

Some days ago I’ve been asked to grab some data from a lot of pages of one web site and place them in an Excel file in order to work with them as a normal list of records. Here it’s my solution to this problem that can be applied in several other cases and that explain the potentiality of InternetExplorer.Application library for VBA.

First of all let’s define the problem I had to solve:
Create a detailed list of people from one website where the link to the each person page was a javascript command.

First consideration: the kind of links provided by the site couldn’t permit to download with a web tool all the pages starting from one starting page.

Unable to download all the pages and open them as text files searching for the needed information I thought about a different solution: create a VBA application in MS Excel ables to surf by itself the whole web site and ables to read the HTML of each page needed.

Therefore as first step I had to create a Internet.Application Object in order to initialize an Internet Explorer session. This operation can be done by the following instructions:

Dim objIE as Object
Set objIE = CreateObject(“InternetExplorer.Application”)

Once that we start Internet Explorer it’s up to us to show the application or not. We can use the Visible property associated to the object in this way:

objIE.Visible = True ‘We will see the window navigation
objIE.Visible = False ‘The IE window isn’t visible

After this very fast set-up I had to start opening the starting page containing all the links needed to take the correct information. To open a link we can use the following method:

objIE.Navigate “http://www.website.com”

IE may require sometimes to open a web page therefore this operation should be followed by some loop to wait until we have the page opened:

While objIE.Busy
DoEvents
Wend

In my case I had always a page containing several link to personal web pages and just from these personal web pages I could take the data from. Therefore the next step was to surf all the links of the starting page and access them one by one:

For Each lnk In objIE.document.Links
‘Loop code
Next lnk
The objIE.document.Links property contains all the tags of a web page in an array form. Of course not all the links in the web page are useful for our purpose therefore we need to find if there is something that would make us recognize easily the right ones. In my case it was easy, every link that contained javascript word was one link to a personal page. Therefore I looked for the right links using a String functions like InStr to know if surf the link or not. There maybe a lot of our ways you can use to define the links you need to open, it’s up to you and to the web site design. The loop will then be like this:

For Each lnk In objIE.document.Links
If (InStr(lnk, “some tag string”)) then
‘Code to be executed in case it’s a good link
end if
Next lnk

To open all the sub links I used one additional InternetExplorer.Application Object in order to keep track of what links I had already visited without keeping any index about it. This solution avoided me to surf again the array to find the correct position and let the For Each Next loop start and end without any additional empty loop. So just think that exist another InternetExplorer.Application Object called objIEpage that will open all these link. At the beginning it’s initialize as objIE and that means that it has open the same web page.

As we are looping throught all the link of the main page and both the objects are on the same page we can assume that the structure of the HTML page is the same in both of them. Therefore if we add this lines to the For Each Next loop we will have the lnk opened without loosing the list of links from the original page.

For Each lnk In objIE.document.Links
If (InStr(lnk, “some tag string”)) then
objIEPage.Navigate lnk
end if
Next lnk

objIEPage now shows the personal web page from which we are taking the data from. As already seen, it’s better to wait until the page is fully loaded. So the next version of the loop is the following.

For Each lnk In objIE.document.Links
If (InStr(lnk, “some tag string”)) then
objIEPage.Navigate lnk
While objIEPage.Busy
DoEvents
Wend
End If
Next lnk
Once we finish the first execution of this loop surfing a good link we will not be able to open a new link in objIEPage object because it’s no more on the same page of objIE. Therefore after viewing each good page we have to go back to the previous page. For this we can use the .GoBack method or again the .Navigate one to open the same starting page. Let’s see the new code adding one of them.

For Each lnk In objIE.document.Links

If (InStr(lnk, “some tag string”)) then

objIEPage.Navigate lnk
While objIEPage.Busy
DoEvents
Wend

objIEPage.GoBack
While obkIEPage.Busy
DoEvents
Wend
End If
Next lnk

If we would execute this cicle from the beginning to the end we would be able to surf all the personal pages. Now let’s see how to get back some data. First of all let’s present the property that contains the body HTML code:
objIEPage.document.body.innerHTML
Using string functions on the content of this property we can identify where the information we are looking for are located. Let’s see just an example because of course each situation is different and requires to be managed based on what we have in the HTML code as tag and as information.

In my case I could get the name of the person based on the id of the tag containing the name. So…
pos = InStr(objIEPage.document.body.innerHTML, “_ctl0_lblFirmName”)
start_pos = pos + 18
s = Mid(objIEPage.document.body.innerHTML, start_pos, 100)
end_pos = InStr(s, “SPAN”) – 3
Sheet2.Range(“A” & j).Value = Mid(s, 1, end_pos)

So in pos I stored the position of the first char of of the _ctl0_lblFirmName. Unluckily the name itself wasn’t right after this word, therefore I had to calculate by hand how many chars I had to avoid to read starting from pos. In this case they were 18. Because InStr would accept just an interget as starting position I had to take some standard lenght of the string starting from start_pos to be able to find the exact length of the name and use the Mid string function to get it back and save it. So, in s I stored a string of 100 chars beginning with the first letter of the name and ending with anything else. In end_pos then I looked for the end of the tag that enclosed the name and I subtracted some chars that weren’t part of the name. As last I stored in the Excel cell the name contained in the s string variable starting at the beginning of the string and long end_pos chars. Just for this article purpose I write here some code using these functions to retrive some data from the web page.

For Each lnk In objIE.document.Links

If (InStr(lnk, “some tag string”)) then

objIEPage.Navigate lnk
While objIEPage.Busy
DoEvents
Wend

pos = InStr(objIEPage.document.body.innerHTML, “tag or string preceding the data we want”)
start_pos = pos + 18
s = Mid(objIEPage.document.body.innerHTML, start_pos, 100)
end_pos = InStr(s, “tag or string following the data we want”) – 3
MsgBox(“The name of the person is ” & Mid(s, 1, end_pos))

objIEPage.GoBack
While obkIEPage.Busy
DoEvents
Wend
End If

Next lnk

Once we this loop finishes all the data will be correctly take from every web page. All that remains is to quite the IE Object created and free the variables.

objIE.Quit
objIEPage.Quit
Set objIE = Nothing
Set objIEPage = Nothing

Reference:

• msdn.microsoft.com/en-us/library/aa752084(VS.85).aspx