August 21, 2014

How to Scrape Title and Header H1 from Web Pages Using Excel VBA

How can we automate the process of scraping the title and header h1 from web pages using Excel VBA?
Our macro code should be able to do the following:

  • Open a web browser like Internet Explorer automatically
  • Load the relevant hyperlink from our Excel worksheet
  • Extract the title and the header h1
  • Place the title and the header data in appropriate cells in our worksheet
  • Go to the next web-link using looping process and do the same process again till it has automatically scanned all the links and extracted all the relevant data and placed it into relevant cells of our worksheet
  • In case the header h1 is not available for whatever reasons, the macro should perform an error handling and do its job neatly
  • Finally the Internet Explorer or web browser should quit
  • The extracted data in the  relevant cells should be neatly ‘auto-fitted’.

View the training video:

View this video on YouTube

The complete VBA code:

Sub get_title_header()
Dim wb As Object
Dim doc As Object
Dim sURL As String
Dim lastrow As Long
lastrow = Sheet1.Cells(Rows.Count, “A”).End(xlUp).Row

For i = 2 To lastrow
Set wb = CreateObject(“internetExplorer.Application”)
sURL = Cells(i, 1)

wb.navigate sURL
wb.Visible = True

While wb.Busy

‘HTML document
Set doc = wb.document

Cells(i, 2) = doc.title

On Error GoTo err_clear
Cells(i, 3) = doc.GetElementsByTagName(“h1”)(0).innerText
If Err <> 0 Then
Resume Next
End If
Range(Cells(i, 1), Cells(i, 3)).Columns.AutoFit
Next i

End Sub


  1. Go Tools and the References in the Microsoft Visual Basic Editor and check (a) Microsoft HTML Object Library and (b) Microsoft Internet Controls
  2. If your Internet speed is slow or erratic check your extracted data carefully

Download a sample file:

Further reading:

Two ways to get data from websites using Excel VBA

11 thoughts on “How to Scrape Title and Header H1 from Web Pages Using Excel VBA

  1. lastrow = Sheet1.Cells(Rows.Count, “A”).End(xlUp).Row

    Hello, Big fan of you website tutorials.
    Trying to run macro and error keeps asking me to fix the above line….

  2. I have a web page open. The URL is dynamic.
    It changes each time I click “Find” on the web page.
    I want to capture h1 and h2 elements each time the URL changes.
    Is this possible?

  3. Does not work – Copy and pasted example into Windows 7, MS Office 2010
    IE settings were all adjusted as instructed.
    Run time error 424, Object required when it hits the line
    Set wb = CreateObject . . . etc etc

  4. HEllo, for me some times skip website and no scraping data from web, maybe know how need make with out skipping it ?

Comments are closed.