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 “http://www.jobs.com/”
Do While .Busy Or _
.readyState <> 4
DoEvents
Loop
Set what = .document.getElementsByName(“q”)
what.Item(0).Value = myjobtype
Set zipcode = .document.getElementsByName(“where”)
zipcode.Item(0).Value = myzip
.document.getElementById(“JobsButton”).Click
Do While .Busy Or _
.readyState <> 4
DoEvents
Loop
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
Macro1
Set objIE = Nothing
End Sub
Sub Macro1()

‘ Macro1 Macro
‘ Formatting imported data


Columns(“A:D”).Select
Selection.Columns.AutoFit
With Selection
.VerticalAlignment = xlTop
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Range(“D1”).Select
Columns(“D:D”).ColumnWidth = 50
Columns(“A:D”).Select
Selection.Rows.AutoFit
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

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

  1. Sarah Ha

    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 “http://www.jobs.com/” highlighted in red. Can you check the code and let me know what’s wrong?

    Reply
    1. tom johnson

      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.

      Reply
  2. Pingback: Best Training on Advanced Excel and VBA in Bangalore | jaseemblog

  3. rrshirsa

    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

    Reply
  4. Marcus

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

    Reply
  5. rakesh sharma

    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

    Status
    Active
    Name
    RAKESH RUPENDRA SHARMA

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

    very thankful in advance

    Reply
  6. Abhijit Dandekar

    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.
    Regards,

    Abhijit

    Reply
  7. Jon

    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.

    Reply
  8. Pingback: Get web page data into Excel using VBA - Online make jobs

Leave a Reply

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