VBA Web Scraping

This VBA script is one of the best for general web scraping and automation. Its simple to use and is applicable in most situations.

All you have to configure is the TagName and Element Identifier. The tag name and id can be found in the web pages source code. Just right click view element on the part of the page you want to interact. Then just update the TagName in the vba script to match the webpage. Examples of Tagnames could be td, a, input, etc. Then within the first tag there should be an identifier of some kind, like a name, id, or title.

HTML should look something like this

<a name=”vba-market” id=”vb1″>vba-market</a>

Search: <input type=”text” id=”text1″ name=”Search”> </>

The below example is selecting a input box with the id value of lst-ib and inserting “www.vba-market.com” as the value.

Set doc = ie.Document
Set hcol = doc.getElementsByTagName("input")
For Each text In hcol
     If text.ID = "lst-ib" Then
          text.Value = "www.vba-market.com"
    End If
Next
HTML
<input id="lst-ib" name="Search"></input>

You can also click elements.

Set doc = ie.Document
Set hcol = doc.getElementsByTagName("a")
For Each text In hcol
If text.Name = "Vba-Market" Then
text.Click
End If
Next

HTML
<a name="Vba-Market">www.vba-market.com</aa>

The majority of web automation can be done using these 2 codes, one to update values and one to click.

Example

Dim ie As SHDocVw.InternetExplorer
Set ie = New InternetExplorerMedium
                While ie.busy
                    DoEvents
                Wend

ie.Visible = False
ie.navigate ("https://www.vba-market.com")
While ie.busy
       DoEvents
Wend
Set doc = ie.Document
Set hcol = doc.getElementsByTagName("input")
For Each text In hcol
     If text.ID = "lst-ib" Then
          text.Value = "www.vba-market.com"
    End If
Next