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

Published by

Dinesh Kumar Takyar

Welcome to! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: For a structured Excel VBA training course online you can visit:

9 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

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.