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