How to Import Data from Website to Excel with VBA Code: 2 Ideal Methods

Method 1 – Import Specific Data from Website to Excel VBA MsgBox

STEPS:

Go to the Developer tab from the ribbon.
Click on Visual Basic from the Code category to open the Visual Basic Editor. Or press ALT+F11 to open the Visual Basic Editor.

This will appear in the Visual Basic Editor.
Go to the Tools menu and click on References.

The References – VBAProject dialog box will appear.
Scroll down a bit and enable two Microsoft Library. Check mark Microsoft HTML Object Library and Microsoft Internet Controls.
Click OK.

Click on Module from the Insert drop-down menu bar.

This will create a Module in your workbook.
Copy and paste the VBA code shown below.

VBA Code:

Sub Import_SpecificData()
Dim request As Object
Dim response As String
Dim html As New HTMLDocument
Dim website As String
Dim total As Variant
website = "https://en.wikipedia.org/wiki/2022_FIFA_World_Cup"
Set request = CreateObject("MSXML2.XMLHTTP")
request.Open "GET", website, False
request.setRequestHeader "If-Modified-Since", "Mon, 14 Nov 2022 00:00:00 GMT"
request.send
response = StrConv(request.responseBody, vbUnicode)
html.body.innerHTML = response
total = html.getElementsByClassName("wikitable")(0).innerText
MsgBox total
End Sub

Run the code by clicking the RubSub button or pressing the keyboard shortcut F5.

This will fetch data import those data from the website and show them into a MsgBox.

VBA Code Explanation

Sub Import_SpecificData()

Sub is a part of the code used to handle the work in the code but will not return any value. It is also known as subprocedure. We named our procedure Import_SpecificData().

Dim request As Object
Dim response As String
Dim html As New HTMLDocument
Dim website As String
Dim total As Variant

The DIM statement in VBA refers to “declare,” which must be used to declare a variable.

website = "https://en.wikipedia.org/wiki/2022_FIFA_World_Cup"
Set request = CreateObject("MSXML2.XMLHTTP")

This will create an object that will make the webpage request.

request.Open "GET", website, False

From this block we will know where to go and how to go there. You don’t need to change the line, you can directly copy this line while importing any sort of data from any website.

request.setRequestHeader "If-Modified-Since", "Mon, 14 Nov 2022 00:00:00 GMT"

We will get the fresh data from our website.

request.send

This line sends the request to the webpage.

response = StrConv(request.responseBody, vbUnicode)

We get the webpage response data into variables.

html.body.innerHTML = response

The line of the code puts the webpage into an HTML object to make data references easier.

total = html.getElementsByClassName("wikitable")(0).innerText

With this line, we will get the total of the specified elements of the webpage. We need to get the class name. To get the class name of that particular element, we have to follow the simple instructions.

Go to the website and right-click on that particular block.
Then, click on Inspect.

This will display the HTML and CSS elements of that website. Click on the class you need to take and put them inside html.getElementsByClassName(“wikitable”).

MsgBox total

This will show the total in a Microsoft window.

End Sub

This will end the procedure.

Method 2 – Scrape Data from Website to Excel

STEPS:

Click on the tiny icon that Record Macro.

The Record Macro dialog will appear.
Give the macro a name. We name the macro Get_Data.
Click OK.

Go to the Data tab from the ribbon.
Click on From Web under Get & Transform Data.

The From Web window will show.
By clicking CTRL+V on your keyboard, paste the URL in the URL area after choosing Basic.
Press the OK button.

The Navigator dialog box will appear as a result.
Choose any data table from the Display Options menu that suits your needs.
Select “Load To.”

The Import Data dialog will show up.
Select Table from “Select how you want to view this data in your workbook.” and click on the Existing worksheet.
Choose the cell where you want to import the data.
Click OK.

This will import the data from the website.

Select the whole worksheet and click the Delete button, the Microsoft Excel dialog will appear. Make sure you click No.

This will Delete all the data.
Go to Data from the ribbon and click Refresh All under Queries & Connections.
Get all the data back in the worksheet.

Open the ribbon and choose Developer from the drop-down menu.
Select Visual Basic to open the Visual Basic Editor. Or, the Visual Basic Editor may also be accessed by pressing ALT+F11.
In the Module, you will get the VBA Macros there.

VBA Code:

Option Explicit
Sub Get_Data()
'
' Get_Data Macro
'
'
    Sheets("Get Data").Select
    Range("N11").Select
    ActiveWorkbook.Queries.Add Name:="2022 FIFA bidding (majority 12 votes)", _
        Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(""https://en.wikipedia.org/wiki/2022_FIFA_World_Cup""))," & Chr(13) _
        & "" & Chr(10) & "    Data1 = Source{1}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes (Data1,{{""Bidders"", type text}, {""Votes Round 1"", type text}, {""Votes Round 2"", type text}, {""Votes Round 3"", type text}, {""Votes Round 4"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    Workbooks("Import Data from Website .xlsm").Connections.Add2 _
        "Query - 2022 FIFA bidding (majority 12 votes)", _
        "Connection to the '2022 FIFA bidding (majority 12 votes)' query in the workbook." _
        , Array( _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""2022 FIFA bidding (majority 12 votes)"";Extended Properties=""""" _
        , ""), "SELECT * FROM [2022 FIFA bidding (majority 12 votes)]", 2
    Application.CommandBars("Queries and Connections").Visible = False
    ActiveWorkbook.Queries.Add Name:="2022 FIFA bidding (majority 12 votes) (2)" _
        , Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(""https://en.wikipedia.org/wiki/2022_FIFA_World_Cup""))," & Chr(13) & "" & Chr(10) & "    Data1 = Source{1}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Data1,{{""Bidders"", type text}, {""Votes Round 1"", type text}, {""Votes Round 2"", type text}, {""Votes Round 3"", type text}, {""Votes Round 4"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""2022 FIFA bidding (majority 12 votes) (2)"";Extended Propertie" _
        , "s="""""), Destination:=Range("$B$2")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array( _
        "SELECT * FROM [2022 FIFA bidding (majority 12 votes) (2)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "_2022_FIFA_bidding__majority_12_votes___2"
        .Refresh BackgroundQuery:=False
    End With
    Columns("A:A").ColumnWidth = 2.86
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Columns("G:G").ColumnWidth = 12
    Range("M26").Select
    Application.CommandBars("Queries and Connections").Visible = False
    Cells.Select
    Selection.ListObject.QueryTable.Delete
    Selection.ClearContents
    Range("B2:F9").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("B2").Select
End Sub

This is the automated macro code.

How to Import Web Data Using Data Tab in Excel

STEPS:

Place the URL in cell C4 in our import data worksheet.
Copy the URL by pressing CTRL+C.

Go to the Data tab from the ribbon.
Click on From Web under Get & Transform Data.

A window named From Web will appear.
Select Basic and in the URL field, paste the URL by pressing CTRL+V from your keyboard.
Click on the OK button.

This will display the Navigator dialog box.
Select any data table as per your requirements from Display Options.
Click on Load.

This will import the selected data from that website and load it into your excel file.

Download Practice Workbook

You can download the workbook and practice with them.

Import Data from Website.xlsm

Get FREE Advanced Excel Exercises with Solutions!

How to Import Data from Website to Excel with VBA Code: 2 Ideal Methods

Method 1 – Import Specific Data from Website to Excel VBA MsgBox

Method 2 – Scrape Data from Website to Excel

How to Import Web Data Using Data Tab in Excel

Related Articles

Leave a reply Cancel reply