How to Import Data from Website to Excel with VBA Code

The key advantage of importing data from a website to Excel is the ability to routinely evaluate data in Microsoft Office Excel without having to continuously duplicate the data. It enables the user to tabulate worksheet cells and enter data into them. Although URLs to the web can be entered by users in individual cells, Excel is not built with the online in mind. In this article, we will demonstrate some methods to import data from a website to Excel using VBA code.

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

Import data is mainly adding information to an existing spreadsheet by importing data from a text file. We can quickly import data from the web to Excel using VBA code. Let’s look at the examples to do this.

1. Import Specific Data from Website to Excel VBA MsgBox

We can import specific data which will show in a Microsoft window using VBA. With Excel VBA, users can easily use the code which acts as an Excel menu from the ribbon. To use the VBA code to import data from a website to Excel.

STEPS:

Firstly, go to the Developer tab from the ribbon.
Secondly, 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.
Now, go to the Tools menu and click on References.

Thus, the References – VBAProject dialog box will appear.
Further, scroll down a bit and enable two Microsoft Library. For this, check mark Microsoft HTML Object Library and Microsoft Internet Controls.
Then, click OK.

Furthermore, click on Module from the Insert drop-down menu bar.

This will create a Module in your workbook.
And, 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

After that, run the code by clicking on the RubSub button or pressing the keyboard shortcut F5.

Finally, this will fetch data and import those data from the website and show them into a MsgBox.

VBA Code Explanation

Sub Import_SpecificData()

Sub is a part of code that is used to handle the work in the code but will not return any value. It is also known as subprocedure. So we name 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,” and it 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. Remember that, 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"

With this we will get the fresh data from our website.

request.send

This line sends the request to the webpage.

response = StrConv(request.responseBody, vbUnicode)

Here, 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 element of the webpage. For this, we need to get the class name first. To get the class name of that particular element we have to follow the simple instructions.

First, 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. From there click on the class that 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.

2. Scrape Data from Website to Excel

Scraping is a computerized technique for gathering copious volumes of data from websites. Let’s follow the steps to import data from the website to Excel.

STEPS:

To begin with, click on the tiny icon that Record Macro.

Consequently, the Record Macro dialog will appear.
Give the macro a name. In our case, we name the macro Get_Data.
Further, click OK.

Go to the Data tab from the ribbon.
After that, click on From Web under Get & Transform Data.

As a result, the From Web window will show.
By clicking CTRL+V on your keyboard, paste the URL in the URL area after choosing Basic.
Additionally, press the OK button.

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

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

This will import the data from the website.

In addition, if you select the whole worksheet and click on the Delete button, the Microsoft Excel dialog will appear. Make sure you click No.

This will Delete all the data.
Now, go to Data from the ribbon and click on Refresh All under Queries & Connections.
And, you will get all the data back in the worksheet.

Further, if you open the ribbon and choose Developer from the drop-down menu.
Then, 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

As this is the automated macro code, you don’t need to write them manually.

How to Import Web Data Using Data Tab in Excel

The menus on the data tab are mostly for data filtering, sorting, and manipulation. There are choices for importing outside data. Using this we can import data from other sources such as Access, the Web, text, SQL Server, and XML. Let’s follow the procedures to use the Data tab to import data from the website to Excel. To do this, we are going to use the FIFA World Cup 2022 Wikipedia source.

STEPS:

Firstly, we place the URL in cell C4 in our import data worksheet.
Secondly, copy the URL by pressing CTRL+C.

Thirdly, go to the Data tab from the ribbon.
After that, click on From Web under Get & Transform Data.

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

This will display the Navigator dialog box.
Furthermore, select any data table as per your requirements from Display Options.
Then, 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

Conclusion

The above methods will assist you to Import Data from Website to Excel with VBA Code. Hope this will help you! Please let us know in the comment section if you have any questions, suggestions, or feedback.

Get FREE Advanced Excel Exercises with Solutions!

How to Import Data from Website to Excel with VBA Code

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

1. Import Specific Data from Website to Excel VBA MsgBox

2. Scrape Data from Website to Excel

How to Import Web Data Using Data Tab in Excel

Conclusion

Related Articles

Leave a reply Cancel reply