Web Scraping Techniques

In this post we are going to discuss about few more techniques used in web scraping. If you are new to web scraping, please read my earlier posts from below.

Web Scraping - Basics

Useful References for VBA Web Scraping

In this post I'm going to explain about more advance techniques.

When we do the data mining, sometimes we need to go to certain websites, put some value in text box and click on "Search" button. Then website gives list of results. These results sometimes may in several pages. If you want to automate this kind of process, you can develop a code to go to that url, then put value to text box, and then write a code to click button and so and so. You can learn how to write this kind of code from my earlier post.

But sometimes this can be easier than you think. Because it may be possible to get result of each page by changing the url. For an example if the website gives results in several pages, url's of each page sometimes have part like "?page=1", "?page=2" etc. So you can develop a program to get data from each page directly. And if you are searching for some value in textbox, then that value also can contained in the url. So it is always better to carefully look at the url and try to find some patterns.

Then sometimes we need to open the each result in separate pages to get all the information of each and every result. Think website have hyperlink like "View" for each result. So we can collect url of each of this "View" link from following code.

Set objIE = CreateObject("InternetExplorer.Application") url="url of particular result page" objIE.Navigate (url) Dim WS as worksheet set WS=activesheet 'wait to load page... Do DoEvents Loop Until objIE.readystate = 4 Set Alllinks = objIE.document.getElementsByTagName("A")     For Each Hyperlink In Alllinks         If InStr(Hyperlink.innerText, "View") > 0 Then             WS.Cells(row, 1).Value = Hyperlink.href             row = row + 1         End If     Next

So what it does is, it lists url of each result in a worksheet. Also you should have some idea about how many result pages the website will give. Then you can set suitable upper limit to the number of pages and should use some technique to exit the loop when there are no more result pages. Here below is example of such program.

Res = InputBox("Please enter number You want to search?", "") Set objIE = CreateObject("InternetExplorer.Application") objIE.Top = 0 objIE.Left = 0 objIE.Width = 1100 objIE.Height = 700 objIE.Visible = True Dim i, row As Integer Dim url, isdata As String Dim WS As Worksheet Set WS = Worksheets("DataBase") i = 1 row = 1 For i = 1 To 100     'genarate url...     url = "your website url+ ?page=" + CStr(i) + "There may be some additional parts of url here" + CStr(Res)     objIE.Navigate (url)         'wait to load page...     Do     DoEvents     Loop Until objIE.readystate = 4         'collect links...     isdata = "n"         Set Alllinks = objIE.document.getElementsByTagName("A")     For Each Hyperlink In Alllinks         If InStr(Hyperlink.innerText, "View") > 0 Then             WS.Cells(row, 1).Value = Hyperlink.href             isdata = "y"             row = row + 1         End If     Next     If (isdata = "n") Then         i = 101     End If Next i objIE.Quit

Determine whether characters inside a cell are bold or regular

Sometimes we need to determine whether characters inside a cell are bold or regular. And some cells can contain both bold and regular characters. Here is an example.

So today we are going to develop a macro to determine whether each and every character inside a cell is regular or bold. What it does is, it outputs each letter in immediate window and tells whether that each letter is bold or regular. Here below is the full code.

Dim i As Integer Dim Sentence_Length As Integer Sentence_Length = Len(Range("B2").Value) For i = 1 To Sentence_Length     Debug.Print Range("B2").Characters(i, 1).Text         If Range("B2").Characters(i, 1).Font.Bold = True Then         Debug.Print "Bold"     Else         Debug.Print "Regular"     End If Next i

Below is the explanation of above code.

First we need to define our variables. Then below line of code measure the number of characters inside B2 cell.

Sentence_Length = Len(Range("B2").Value)

And this for next loop, loop through all the letters in B2 cell.

For i = 1 To Sentence_Length     Next i

Below line prints the each character in immediate window. Please note that this program will consider spaces also as characters.

Debug.Print Range("B2").Characters(i, 1).Text

This part of the code checks whether the character is bold or regular and print the result in immediate window.

If Range("B2").Characters(i, 1).Font.Bold = True Then         Debug.Print "Bold" Else         Debug.Print "Regular" End If

Here is an image of immediate window after running the macro.

So this is just a very simple example. You can alter this code to suit with your requirements.

Useful References for VBA Web Scraping

        This post is to give small tip related to web scraping. If you are  new to web scraping please see my earlier post which explain everything from beginning. Below is the link to that post.

Web Scraping - Basics

In this post I will explain you what are the best VBA references you should add when you develop a Visual Basic Application to scrape data from websites. Adding these references are not mandatory. But it will make your life easier. So here are the list of useful references.

• Visula Basic for Applications
• Microsoft Excel 15.0 Object Library
• OLE Automation
• Microsoft Office 15.0 Object Library
• Microsoft Forms 2.0 Object Library
• ietag 1.0 Type Library
• iextag 1.0 Type Library
• Microsoft HTML Object Library
• Microsoft Internet Controls

As you can see that there are numbers like 15.0 etc. These numbers can vary according the versions you have installed in your computer.

Also when you open your VBA editor you will notice that first four items of the list are automatically ticked by default. So you don't need to add them manually.

And when you insert form for the first time to your project, VBA editor will automatically add reference to Microsoft Forms 2.0 Object Library. So you also don't need to worry about adding the reference to that library.

Ultimately you have shorter list to add references manually. Here is that list

• ietag 1.0 Type Library
• iextag 1.0 Type Library
• Microsoft HTML Object Library
• Microsoft Internet Controls

Once you add references, your references window should look like this.

Shapes

We often need to use shapes in Excel worksheets. You can insert various kinds of shapes to Excel worksheets from insert menu. Also you can use your own pictures as shapes. Here are few VBA examples related to shapes.

If you want to assign macro programmatically to your shape you can use below method.

Sub AssignMacro() ActiveSheet.Shapes("Picture 20").OnAction = "'" & ActiveWorkbook.Name & "'!Macro1" End Sub

You may want to know the row number of cell of top left of your shape. You can use below code for that.

Sub TopLeftCellRowNumber() MsgBox ActiveSheet.Shapes("Picture 20").TopLeftCell.Row End Sub

Below code will select all your shapes which have "picture" in its name.

It will select one shape at a time. Then will wait for 1 second and will select next shape.

Sub SelectPictureShapes() Dim sh As Shape     For Each sh In ActiveSheet.Shapes         If InStr(1, sh.Name, "picture", vbTextCompare) > 0 Then             sh.Select             Application.Wait Now + TimeValue("00:00:01")         End If Next End Sub

Sometimes you may need to know the name of the clicked shape in your program. Because sometimes your program will need to do different things depend on what shape is clicked. So below code will show the name of clicked shape in a message box. You can develop this to suit with your needs.

msgbox ActiveSheet.Shapes(Application.Caller).Name

Split a Long Row of Data Into Multiple Rows

We can use Excel Macros to organize our data. Below is one such example. Here what we going to do is split long row of data into multiple rows. Here the rule is we need 5 cells per row, then to start the next row. So data in F1 will move to A2, G1 to B2 and so on. Same on third row until data runs out.

Here is how our data currently in the sheet.

Below is the end result we need.

So here is the sample code to do that. You can customize the code to suit to your situation. New sheet will be added and result will be created in that sheet. At last sheet will renamed as "Result sheet" and activated. And you will get a confirmation message at end.

Dim WS As Worksheet Dim WS_Result As Worksheet Set WS = Worksheets("Input file") Set WS_Result = Worksheets.Add 'find last column Lastcol = WS.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column Counter = 1 ResultRowCount = 1 For i = 1 To Lastcol     If Counter = 6 Then         Counter = 1         ResultRowCount = ResultRowCount + 1     Else         WS_Result.Cells(ResultRowCount, Counter).Value = WS.Cells(1, i)     End If         WS_Result.Cells(ResultRowCount, Counter).Value = WS.Cells(1, i)     Counter = Counter + 1 Next i WS_Result.Name = "Result sheet" WS_Result.Activate MsgBox "Completed!", vbInformation, ""

Detect Operating System

Sometimes we need to develop VBA programs to compatible with both Windows and Mac operating systems. If you develop a VBA code for windows, sometimes it will work for Mac as well. For an example if your program only manipulate data within a excel sheet it will work for both operating systems. But you will find that some VBA programs not compatible with Mac. For an example if you are  trying to access a directory using a VBA code this will not work in both OS's. Because file path of Windows contains backslash ( \ ). But Mac use colon (:) to separate the folder names. This is an only one example. If you develop VBA programs you will find that there are various instances not supported by both OS's

So if your code is not compatible with both OS's, you will need to develop two VBA codes. But there are situations you don't know what OS your user will use. However you can make your code compatible with both OS by combining both codes. What you can  do is first check the OS, and if it detects Mac, you can ask the program to follow one path by using a if statement. Else you can ask it to follow the path written for Windows.

Here is a VBA code to do that.

'If the operating system is Mac If Application.OperatingSystem Like "*Mac*" Then     '..........     'Here goes the code compatible with Mac    '.......... 'If windows Else     '..........     'Here goes the code compatible with Windows     '.......... End If

Use of On Error GoTo 0

        In this post I will explain why we need to use On Error GoTo 0 in error handling. I will explain the use of On Error GoTo 0 using a simple example.

Assume we need to make changes in different workbook. So first we need to declare the variable and set the workbook as follows.

Dim WB_Example As Workbook Set WB_Example = Workbooks("Example File.xlsx")

So if we run above two lines variable WB_Example will be declared and workbook "Example File.xlsx" will be assigned to that variable. But this will be successful only if "Example File.xlsx" is in open state. Otherwise it will give below error.

If you click the debug button second line will be highlighted as follows

However if this workbook is not in open state, we can open it automatically and continue the rest of the code. For that we need to avoid that error message. Error message can be avoided and resume code using On Error Resume Next statement. We can do it as follows.

Dim WB_Example As Workbook On Error Resume Next Set WB_Example = Workbooks("Example File.xlsx") If Err <> 0 Then     If Err.Number = 9 Then         Workbooks.Open ("D:\VBA Blog\On Error Goto Zero\Example File.xlsx")     Else         MsgBox "Unknown Error.", vbCritical, "warning!"         Exit Sub     End If End If

 So if there is an error, program will check whether error number is equal to 9. If it equals to 9, Then program will automatically opens the workbook. Because we know that this error occurs at this point due to workbook is not in open state. But if the error number is different to 9, error occurred due to some other reason. So program will notify it to the user and terminated at that point.

Think we run above code and didn't get any other Err.Number different to 9. So then our program will continue from above code. So this is where we need On Error GoTo 0. Consider the following code. If you look at the last line you will see that we are trying to put a string to a cell A1 of sheet2. But what will happen if we don't have worksheet with name "Sheet2". Progrma should give us an error.

Dim WB_Example As Workbook On Error Resume Next Set WB_Example = Workbooks("Example File.xlsx") If Err <> 0 Then     If Err.Number = 9 Then         Workbooks.Open ("D:\VBA Blog\On Error Goto Zero\Example File.xlsx")     Else         MsgBox "Unknown Error.", vbCritical, "warning!"         Exit Sub     End If End If WB_Example.Worksheets("Sheet2").Range("A1").Value = "This is an example"

But if you run the code, you will realized that it is not giving such error if there is no Sheet2 available. So this happen due to effect of On Error Resume Next statement. So this On Error Resume Next statement was helpful up to particular point of code. But after that we are getting unexpected problem due to it. So now we need to end effect of On Error Resume Next statement in a earliest possible point of our code. This can be done by using On Error GoTo 0 statement.

Dim WB_Example As Workbook On Error Resume Next Set WB_Example = Workbooks("Example File.xlsx") If Err <> 0 Then     If Err.Number = 9 Then         Workbooks.Open ("D:\VBA Blog\On Error Goto Zero\Example File.xlsx")     Else         MsgBox "Unknown Error.", vbCritical, "warning!"         Exit Sub     End If End If On Error GoTo 0 WB_Example.Worksheets("Sheet2").Range("A1").Value = "This is an example"

So now if we run the macro again and if there is no Sheet2 in our workbook, then we will get this error message.

So if you click the Debug button, last line will highlighted as follows.

So you can see that effect of On Error Resume Next statement is avoided by On Error GoTo 0.