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.
Download Practice Workbook
You can download the workbook and practice with them.
2 Ideal Examples to Import Data from Website to Excel Using VBA Code
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.
Read More: How to Auto Sort In Excel When Data Is Entered
Similar Readings
- How to Open Workbook from Path Using Excel VBA (4 Examples)
- Excel VBA to Populate Array with Cell Values (4 Suitable Examples)
- How to Alternate Row Colors in Excel Without Table (5 Methods)
- Learn Excel VBA Programming & Macros (Free Tutorial – Step by Step)
- How to Hide Unused Columns in Excel (5 Quick Tricks)
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.
Read More: How to Scrape Data from a Website into Excel (2 Easy Methods)
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.
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. Or you can glance at our other articles in the ExcelDemy.com blog!
Related Articles
- 22 Macro Examples in Excel VBA
- List of 10 Mostly Used Excel VBA Objects (Attributes & Examples)
- How to Use VBA Modules in Excel (8 Simple Ways)
- Introduction to VBA Features and Applications
- How to Write VBA Code in Excel (With Easy Steps)
- Types of VBA Macros in Excel (A Quick Guide)
- What You Can Do with VBA (6 Practical Uses)
- [Fixed!] Border Not Showing in Excel (6 Solutions)
Can this be replicated to hotel/travel search websites like trivago, expedia, etc?
Check out trying. I never tried. If you face any problem, let us know 🙂