Microsoft Excel is indeed an extremely effective software. Using Excel‘s resources and features, we may perform countless operations on a given dataset. We frequently need to import tables from the Website to Excel. When we copy table data from websites to excel, information from websites needs to be updated in the excel file, making the procedure very time-consuming. This article will discuss two easy ways to insert tables from websites to Excel. Therefore, you should go through these 2 Easy Ways to Import a Table from a Website to Excel.
Download Practice Workbook
The example workbook used during the session is available for anyone to download at no cost.
2 Easy Ways to Import Table from Website to Excel
As an illustration, we will explore a sample Website from where we will fetch a Table. In this case, we will visit microsoft.fandom.com. The following website, for example, has a table labeled Windows Servers containing two columns Name and Release date. Using these two approaches, we will import this table from this website to Excel. In addition, I have yet to mention that I have been using the Microsoft Excel 365 version for this article. You can choose any other edition that you find most convenient except the Earlier Version before 2016.
1. Utilize Power Query to Import Table from Website to Excel
Excel‘s Power Query is a data analysis tool that enables users to input data from various sources and then clean, convert, and reformat it as necessary. It allows you to build up a query one time and then reapply it after a simple refresh. The greatest thing about Power Query is that you do not need to know or use any code to perform any of it. In this context, we will fetch a table from the website mentioned earlier. Moreover, we will import specific data columns from the table. To complete the task, please follow the instructions below.
STEPS:
- First of all, in the active sheet, choose cell B4.
- Secondly, Go to the Data tab, followed by From Web.
- Due to this, the From Web window will pop up.
- Then, In the URL Box, input the URL of the Website you want to import tables.
- Next, hit OK.
- Subsequently, the Navigator window will pop out. The left section of this window contains all the tables. In contrast, the right section allows us to view the table.
- For this context, choose Windows Servers. In this case, we will find an unnamed column. We will import all columns except for this column.
- To achieve this, go to Transform Data.
- Afterward, the Power Query window will open.
- Then, right-click on the top of the column.
- Now, select Remove.
- Next, go to Close & Load, followed by Close & Load To.
- Latterly, the Import Data window opens.
- At this time, check the Existing worksheet, then hit OK.
- As a result of it, the following outcome will occur.
Read More: How to Scrape Data from a Website into Excel (2 Easy Methods)
2. Import Table from Website Through Excel VBA
VBA is an abbreviation for Visual Basic for Application. VBA is a programming language established by Microsoft. Users can utilize the VBA programming language to access Excel-incompatible functions. We will use VBA in this part to import a table from a Website into Excel. It is essential to note that the active sheet must be opened as Sheet1. In other words, the sheet where we want the table to appear must be the first sheet among all other sheets in the workbook. Please follow the directions below to carry out the task correctly.
STEPS:
- To begin, choose the active sheet of the workbook as Sheet1.
- Second, navigate to Developer.
- Then, click Visual Basic.
- After that, select Insert, followed by Module.
- Next, enter the following code into the Module Box.
Sub SOFTEKOimporTablefromWeb()
Dim Data As QueryTable
Dim URL As String
URL = "https://microsoft.fandom.com/wiki/List_of_Microsoft_products"
Set Data = Sheet1.QueryTables.Add( _
Connection:="URL;" & URL, _
Destination:=Sheet1.Range("B4"))
With Data
.RefreshStyle = xlOverwriteCells
.WebFormatting = xlWebFormattingRTF
.WebSelectionType = xlSpecifiedTables
.WebTables = "2"
.Refresh
End With
End Sub- Make sure to change both the Range and the URL you want to visit.
- After that, press F5 or click the Run button to finish.
- As a consequence of it, the following will be the outcome.
Read More: Excel VBA to Scrape Table from Website (With Easy Steps)
Conclusion
You are now able to Import a Table from a Website to Excel by making use of the ways that we have just covered. Many articles on the ExcelDemy website are comparable to this one. If you think of alternative methods to get the work done, please share those thoughts and suggestions with us. If you have any questions, comments, or recommendations, please leave them in the space provided below.
