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

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

  1. Hi,
    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. 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. 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. 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. 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.

  6. Hi,
    its nice way to exctract table values.
    I have a query, if with VBA excel I copied data from excel to fill a web form then click next button to go to next page on that page again I have to fill few text boxes from excel sheet and same way there are total 5 or 6 pages on which I want to update data using excel vba code from excel sheet to complete the new account creation. I have tried and can fill the first page but while clicking on next button its moving to next page to fill the data but data not filled and got the run time error 91 (object variable or With block variable not set)

    Please help me out how can i fix this error to fill the all pages and finallly submitt to create account. The web site i using is

  7. Dear Guys,

    KIndly give me idea how to get different mobiles prices from different websites in easier way. with automatic update in excel.

  8. Hi,
    I am finding VBA codes which is help me for my work, I have a site which is already opened in a explorer I need to find the web address(if not need to stop with msg not found) and copy the exact value and paste it to a excel.

    Can anyone help me out

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.