March 15, 2014

Complete Automation of Getting Web Page Data into Excel Worksheet Using VBA

In this video we show the complete automation of how to get data into an Excel worksheet using VBA.
1. We first study the website and find out the elements we’ll need to access a form and the subsequent results. When you study the web page’s HTML source code you’ll note that the actual results are wrapped up in DIV containers.
2. Next we write the VBA code We use the getElementById method to get a reference to a single object and the getElementsByTagName method to get a collection of all the elements. Next we loop through all the elements and get the text properties or data (‘innertext’) of all the elements we wish to have. Our code instantiates our web browser (Internet Explorer) and navigates to the URL of our choice and then helps to get or extract the data using events. We also ensure that the code is placed in appropriate columns and rows so that any further analysis is made easy. Finally we use a recorded macro to format the data to make it more presentable to the human eye. Note: If the class names or other HTML code changes on a web page you’ll need to modify your VBA code accordingly to scrape the data correctly.
The macro code to get web page data into an Excel worksheet using VBA is given below:
Sub test()
Dim eRow As Long
Dim ele As Object
Set sht = Sheets(“Sheet1”)
RowCount = 1
sht.Range(“A” & RowCount) = “Title”
sht.Range(“B” & RowCount) = “Company”
sht.Range(“C” & RowCount) = “Location”
sht.Range(“D” & RowCount) = “Description”
eRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Set objIE = CreateObject(“InternetExplorer.Application”)
myjobtype = InputBox(“Enter type of job eg. sales, administration”)
myzip = InputBox(“Enter zipcode of area where you wish to work”)
With objIE
.Visible = True
.navigate “”
Do While .Busy Or _
.readyState <> 4
Set what = .document.getElementsByName(“q”)
what.Item(0).Value = myjobtype
Set zipcode = .document.getElementsByName(“where”)
zipcode.Item(0).Value = myzip
Do While .Busy Or _
.readyState <> 4
For Each ele In .document.all
Select Case ele.classname
Case “Result”
RowCount = RowCount + 1
Case “Title”
sht.Range(“A” & RowCount) = ele.innertext
Case “Company”
sht.Range(“B” & RowCount) = ele.innertext
Case “Location”
sht.Range(“C” & RowCount) = ele.innertext
Case “Description”
sht.Range(“D” & RowCount) = ele.innertext
End Select
Next ele
End With
Set objIE = Nothing
End Sub
Sub Macro1()

‘ Macro1 Macro
‘ Formatting imported data

With Selection
.VerticalAlignment = xlTop
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Columns(“D:D”).ColumnWidth = 50
End Sub

Watch the training video below to see how the process of complete automation of data collection from a web page into an Excel spreadsheet is implemented:

Watch the video on youtube

Further reading:
Excellent article on web services using VBA

25 thoughts on “Complete Automation of Getting Web Page Data into Excel Worksheet Using VBA

  1. When I copied the code above and tried, I got “Compile error: Syntax error” message with “myjobtype = InputBox(“Enter type of job eg. sales, administration”)
    myzip = InputBox(“Enter zipcode of area where you wish to work”)” & .navigate “” highlighted in red. Can you check the code and let me know what’s wrong?

    1. If you copy/paste the code above, it will fail because the quote characters pulled in are the left/right double quote, not the real ” character–just retype the quotes.

  2. For most beginners, i usually write comments on each line (or functional classes) of code so they understand what that section of code actually does. This feedback is iff your code is intended for beginners

  3. My Internet explorer fills nurse into the google search bar but then just idles there? anyone with the same problem?

  4. since last week I am trying to pull data from a website required user id & password to login than clicking next button and entering value it displays name and status.
    in excel by vba I am able to login and also able to visit next page and even return value and it is displaying value as name :…. & status ….. I want a code in continue to above that will copy the result in excel;
    for your ready reference the source code is

    PAN Details


    I want from above “Rakesh Rupendra Sharma” & “Active” in column A & B respectively

    very thankful in advance

  5. Hello Sir,

    I want to populate the Contacts Data Excel Worksheet from various websites. The feed for websites will be from URLs listed in a worksheet column
    I would like to pull Company name into Company Column, Names and Designations from ‘Board of Directors’ and ‘Management Team’ pages into respective columns, nature of the business from ‘About Us’ page into Industry column and Address, Telephone, Fax, Email, URL from ‘Contact Us’ Page into respective columns.
    This Excel data will then be exported into my Salesforce CRM.
    Could you please guide on how this process can be automated – that is about writing the VBA subroutine?
    I thank you in advance for showing the way.


  6. Hello,

    I copied the code and corrected the QUOTES, I get a Run-time error ‘9’: Subscript out of range.

    Any help would be greatly appreciated.

  7. @Jon
    Still the quotation inside the range of cells indicating should be replaced by “

  8. Pingback: Get web page data into Excel using VBA - Online make jobs
  9. There is something wrong with the .click (.document.getElementById(“JobsButton”).Click)
    Can’t get it to work

  10. Hello,

    Under the createObject.IE line, I am trying to use Google Chrome as the preferred web browser to work from. Can you please provide the correct lines as substitution for IE. Thanks for the awesome instruction, and as a beginner, I am thoroughly enjoying the journey.

  11. I copied the code but while compiling I am getting an error saying sub or function not defined near Sub Macro1() . What am I missing here ?

  12. There is something wrong with the .click (.document.getElementById(“JobsButton”).Click)

  13. Also for me seems to be a problem with following section “(.document.getElementById(“JobsButton”).Click)”
    I did not find such an Id like “JobsButton”.Probably HTML file name changed instead of I used the following
    “.document.getElementById(“searchBox”).Click” as Button ID’s name is “searchBox”


    1. hello is there anyway that i can use Google chrome instead of InternetExplorer , and how ?

      thank you

  14. How to get data from particular web that is already open and need to get data from particular text, plz help me out.

  15. I need your help for the below requirement
    Once we open the page we have many Serial numbers, for each serial number there will be status and comment .

    So based on serial number we have to copy comment from webpage and paste it to excel.

    In Excel Serial Number column we have serial numbers which we need to search in webpage and for that serial number what we have status, based on that comment will be there, so that comment we have to paste it in Excel comment column.

    On webpage for each serial number we have in table format.

    Eg: Serial number – TH85U5G1GC , and Status – Sytstem Reject, then what comment we have under Internal comment “Invoice to partner Ingram Micro (UK)Ltd”

    If any thing i have to provide, you can contact me to my email.

  16. Hi ,

    Is it possible to input a key number into web and extract data from that scren ,as i have 600+ key numbers to search and extract data from web , please help me

  17. Key numbers from Excel sheet to web and then get that data to excel ,i do it manually everyday it takes me 3 hours onndaily basis ,copying from Excel colum and pasting in website and getting key fields data from there and pasting it in Excel

Comments are closed.