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.
Import Table from Website to Excel: 2 Easy Ways
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.
- Next, 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. 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.
Download Practice Workbook
The example workbook used during the session is available for anyone to download at no cost.
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. 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.
Related Articles
- How to Automate Copy and Paste from Website to Excel
- How to Import Data from Secure Website to Excel
- How to Extract Data from Website to Excel Automatically
- How to Extract Data from Multiple Web Pages into Excel
- How to Import Data into Excel from Web
<< Go Back to Web Scraping in Excel | Importing Data in Excel | Learn Excel
Thanks for free code and it works for your website.
However it did not work for Concur Expense;
Error message as follows:
“This Web query returned no data. To change the query, click OK, click the arrow on the name box in the
formula bar, click the name of the eRernaI data range for the Web query, right-click the selection, and then
click Edit Query. ”
I suspect User login is a problem.
Would you help me modify code for above website?
Thanks and Kind Regards,
Hello GFIN SUNNY
Thanks for your nice words. Your appreciation means a lot to us. Thanks once again for sharing your problem.
Unfortunately, using VBA to automate web interactions with sites that require authentication is not easy when it requires login. You may need a more advanced approach to handle authentication, such as sending HTTP requests with the necessary credentials.
Regards
Lutfor Rahman Shimanto