Scribd
Upload
14 views

Scraping Data From Website

The document describes how to automatically extract data from a web page and input it into an Excel spreadsheet using VBA macros. It involves studying the HTML of the web page to identify the elements containing the desired data, then writing VBA code using methods like getElementById and getElementsByTagName to programmatically navigate to the page, enter search criteria, and loop through the results to extract the text and input it into the spreadsheet at the appropriate cells. The document provides the full VBA code as an example to automate extracting job listing data from a jobs website.

Uploaded by

anilks3
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
14 views

Scraping Data From Website

The document describes how to automatically extract data from a web page and input it into an Excel spreadsheet using VBA macros. It involves studying the HTML of the web page to identify the elements containing the desired data, then writing VBA code using methods like getElementById and getElementsByTagName to programmatically navigate to the page, enter search criteria, and loop through the results to extract the text and input it into the spreadsheet at the appropriate cells. The document provides the full VBA code as an example to automate extracting job listing data from a jobs website.

Uploaded by

anilks3
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 2

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

(https://youtu.be/cSoRVZKRkvY)

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:

You might also like