Scraping Web Page Data Automatically with Excel VBA

Scraping data from web pages can be done in a variety of different ways including the built in feature ‘Get Data from Web’ in MS Excel. But this method of extracting data from web pages may not work in many situations. Also we learnt that we can scrape data from web pages like the website quickly and easily because the web design of the website allows us to access the web pages and data easily using VBA. But how do you extract data from a website which has a difficult form – the name or ID of the button are not given. The data is hidden in tables upon tables and sometimes within nested table. Here adopt a different strategy although the study of the source code of the web page remains the most important element. A button can be hidden inside a form or connect to the data source via a java-script print command. So study the source code carefully and then device your plan of action. Amazing articles have been written by João Neto.
In our case we tried to extract data from another very important jobs website The button was difficult to locate and could be clicked only indirectly via the form object. The Excel VBA code below describes the complete process:
Sub clickFormButton()
‘we define the essential variables
Dim ie As Object
Dim form As Variant, button As Variant

‘add the “Microsoft Internet Controls” reference in your VBA Project indirectly
Set ie = CreateObject(“InternetExplorer.Application”)

‘more variables for the inputboxes – makes our automation program user friendly
myjobtype = InputBox(“Enter type of job, eg. sales, administration”)
myexperience = InputBox(“enter your no of years experience, for example, 3”)
mycity = InputBox(“Enter the city where you wish to work”)

With ie

.Visible = True
.navigate (“”)

‘ we ensure that the web page downloads completely before we fill the form automatically
While ie.ReadyState <> 4

‘assigning the vinput variables to the html elements of the form
ie.document.getelementsbyname(“fts”).Item.innertext = myjobtype
ie.document.getelementsbyname(“exp”).Item(0).Value = myexperience
ie.document.getelementsbyname(“lmy”).Item.innertext = mycity
‘ accessing the button via the form
Set form = ie.document.getElementsbytagname(“form”)

Set button = form(0).onsubmit

‘ again ensuring that the web page loads completely before we start scraping data
Do While ie.busy: DoEvents: Loop

Set TDelements = .document.getElementsbytagname(“td”)
r = 0
c = 0

For Each TDelement In TDelements
Sheet1.Range(“A1”).Offset(r, c).Value = TDelement.innertext
r = r + 1

End With

‘ cleaning up memory
Set ie = Nothing

End Sub

Watch the Excel training video below to see how we can scrape data from difficult web pages using Excel vba quickly and easily:

Once you have the data you need to check whether it has blank rows. You can use the following VBA code to remove the blank rows quickly and easily:

Sub deletblankrows()
lastrow = ActiveSheet.Range(“A” & Rows.Count).End(xlUp).Row
For i = 1 To LastRow
If Cells(i, 1) = “” Then
Cells(i, 1).EntireRow.Delete
End If


End Sub

Note: you may have to run the macro several times to delete all the blank rows!

We can also use Autofilter to remove the blank rows:

sub myautofilter()
Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Range(“A:A”).AutoFilter Field:=1, Criteria1:=”<>”
Application.CutCopyMode = False
End Sub

To extract the required based on a keyword like “Administration” you can use the following VBA code:

Sub extractDatatoNeighboringColumn()
lastrow = ActiveSheet.Range(“A” & Rows.Count).End(xlUp).Row
For i = 1 To LastRow
If InStr(Cells(i, 1).Value, “Administration”) Then
Cells(i, 2).Value = Cells(i, 1).Value
End If
End Sub

Further reading:
VBA Web Services

6 thoughts on “Scraping Web Page Data Automatically with Excel VBA

  1. Ashwani K

    there is some requirements in my project for weather data , so i need the hourly base weather data which available on web page “”, there is option in the page table , so after i click on this option i gor the hourly base weather data for next 10 days and i need the data for next 5 days . i was using web query option in excel but unable to extract all data if you can help me to pull this data in to excel for next 5 days

    ashwani kumar

  2. Roopa

    Hi Sir,
    I found this website very usefull.
    can we automate the process of fetching the data from a website without having to enter the values sequentially into the webpage??
    For eg, i need to get the data for values 56679878 to 56698765. while doing this we need to enter the values individually and submit a button and get the data and then the next number. can we automate this process??
    Please advice me..

  3. Ataliq Ali

    Hi Sir,

    I have found a lot of information from your website, thank you for your videos.

    Can you help me on how to extract a particular data from a set of different URLs. I need this urgent pls help sir.

  4. Michael Barrow

    Hi, how are you, I have this doubt

    I’ve done this before in a Webbrower to call a JavaScript

    Call WebBrowser1.document.parentWindow.execScript(“goToComprobante()”, “JavaScript”)

    It works great, but now we have another page that has this document in HTML.

    function validacionOSCP(cer, signature) {
    var control = document.getElementById(“csd”);
    control.content.Fiel.ActivaBusy = true;
    type: ‘POST’,
    cache: false,
    url: “/Cfdi/VerificaOSCP”,
    data: {
    cer: cer
    success: function (data) {
    control.content.Fiel.ActivaBusy = false;
    if (data == “”) {
    control.content.Fiel.ActivaConfirmar = true;
    else {
    control.content.Fiel.EstatusConf = false;
    control.content.Fiel.MensajeError = data;
    error: function (request, status, error) {
    control.content.Fiel.ActivaBusy = false;
    control.content.Fiel.MensajeError = “Problemas de comunicación con el servicio.”;

    I’ve tried this but occurs an error

    ‘WebBrowser1.Document.parentWindow.execScript f2, “javascript”
    ‘WebBrowser1.Document.parentWindow.execScript “‘validacionOSCP'([‘certificado’],[‘firmas’]”, “javaScript”

    .Document.parentWindow.execScript “validacionOSCP()”, “JavaScript”
    ‘Call WebBrowser1.Document.parentWindow.execScript(“validacionOSCP()”, “JavaScript”)

    is there another way?

    The page contains Silverlight.

    any suggestion? I really aprecciated

  5. Vicky Mohan

    Please provide solution for below macro.

    Create a macro which on the click of Ctrl+k opens the website: and
    copy the BSE and NSE rate mentioned on that page to the excel file, along with a column mentioning todays date.
    assuming that everyday all a person has to do is open this file and press Ctrl+k,
    no other human input should be required.


Leave a Reply

Your email address will not be published. Required fields are marked *