You may believe that just copying and pasting data from another file into Excel would suffice to extract data. However, changing the source worksheet has no effect on the data. You’ll need to import data to make dynamic value changes. In this tutorial, we will show you how to import data into excel from another Excel file.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
2 Handy Approaches to Import Data into Excel from Another Excel File
We’ve included a sample data set with a workbook entitled Subtotal Pivot Table in the image below. This worksheet’s data will be imported into another workbook. To achieve this, we’ll use the Power Query first. Later on, in another method, we will run VBA Codes to import data.
1. Apply Power Query to Import Data into Excel from Another Excel File
Firstly, we will import a table from another workbook in our current workbook Import Data from Another File. To add any data without editing, read the below section.
1.1 Import a Table from Another Excel File
Step 1: Use the Data Tab
- Firstly, click on the Data.
- Select the Get Data.
- Choose the From File.
- Then, choose the From Excel Workbook option from the list.
Step 2: Select the Source File
- Select the source file.
- Then, click on Import.
- As a result, the existing Tables and Sheets will appear as Navigator.
- Select Table1 from the list and check the preview on the right side.
- Finally, click on Load.
- As a result, as seen in the figure below, your Table will be imported into your current worksheet.
1.2 Import Data from Another Sheet and Edit Data
If you need to edit your imported data, follow the outlined steps below to edit or change it in The Power Query Editor.
Step 1: Use the Data Tab Feature
- From the Data tab, click on the Get Data.
- Then, choose the From File
- Finally, select the From Excel Workbook option from the list.
Step 2: Selection of Source File
- Select your source file to import.
- Then, click on Import.
Step 3: Get the Preview in Navigator
- As the Navigator window pops up, select Sheet1 as we want to import data from Sheet1.
- Then, click on Transform Data.
- Consequently, you will see the following image in the Power Query Editor.
Step 4: Remove Additional Rows
- To remove the top row, click on the Remove Top Rows.
- Type the number on the row.
- Press Enter.
- Therefore, you will see the following result with the removed top row.
- To remove the bottom row, click on the Remove Rows.
- Select the Remove Bottom Rows.
- Type the number on the row.
- Then, press Enter.
- As a consequence, the result will appear with no additional bottom rows.
- Additionally, click on the Use First Row as Headers for making the first row of the Table the Header.
- As shown in the image below, the first row will transform into a Header.
- Finally, click on the Close & Load option to see the data in the current workbook.
- As a result, imported data will appear in the current worksheet.
Steps: Update Imported Data from Source File
- Firstly, change the source file data to 28 and save it.
- Go back to the current workbook, and right-click on the cell.
- Click on the Refresh.
- Therefore, the changed data will be updated to 28 according to the source file.
Read More: How to Import Data from Text File into Excel (3 Methods)
Similar Readings
- Excel Formula to Get First 3 Characters from a Cell(6 Ways)
- Transfer Data from One Excel Worksheet to Another Automatically
- VBA Code to Convert Text File to Excel (7 Methods)
- How to Import Text File with Multiple Delimiters into Excel (3 Methods)
- Excel VBA: Pull Data Automatically from a Website (2 Methods)
2. Run a VBA Code to Import Data into Excel from Another Excel File
Additionally, you can apply VBA codes to import data from another file. Follow the simple steps below to do so.
Step 1: Insert a Module
- Firstly, press Alt + F11 to start the VBA Macro.
- From the Insert tab select the Module.
Step 2: Paste VBA Codes
- In the Module box, paste the following VBA codes.
Sub ImportData()
'Define variables for workbooks
Dim CurrentWkb As Workbook
Dim SourceWkb As Workbook
'Define variables for source
Dim SourceRng As Range
Dim DestinationRng As Range
'Set value for your current workbook
Set CurrentWkb = ActiveWorkbook
With Application.FileDialog(msoFileDialogOpen)
.Filters.Clear
'Define file types for selection
.Filters.Add "Excel 2008-19", "*.xlsx; *.xlsm; *.xlsa"
'Give statement to 'False' for not allowing selection of multiple files
.AllowMultiSelect = False
.Show
'Apply If condition
If .SelectedItems.Count > 0 Then
Workbooks.Open .SelectedItems(1)
Set SourceWkb = ActiveWorkbook
'Insert Input Box for selection source range
Set SourceRng = Application.InputBox(prompt:="Import source range", Title:="External Source Range", Default:="A1", Type:=8)
CurrentWkb.Activate
'Enter Input box for selection for destination range
Set DestinationRng = Application.InputBox(prompt:="Select your destination", Title:=" Click any cell selecting Destination", Default:="A1", Type:=8)
'Import the source range to the current workbook
SourceRng.Copy DestinationRng
DestinationRng.CurrentRegion.EntireColumn.AutoFit
SourceWkb.Close False
End If
End With
End Sub
Step 3: Run the VBA Codes
- Save the program and press F5 to run it.
- Select the source file to import.
Step 4: Select Range from the Source File
- Then, select the range to import data.
- Click on OK.
Step 5: Place the Imported Data in the Current Worksheet
- Click any cell as the Destination Cell.
- Finally, click on OK.
- As a consequence, your data from the other file will be imported into the current file, as seen in the figure below.
Read More: Import Data from Excel into Word Automatically Using VBA (2 Ways)
Conclusion
To conclude, I hope this article has given you a tutorial about how to import data into excel from another Excel file. All of these procedures should be learned and applied to your dataset. Take a look at the practice workbook and put these skills to the test. We’re motivated to keep making tutorials like this because of your valuable support.
If you have any questions – Feel free to ask us. Also, feel free to leave comments in the section below.
We, the Exceldemy Team, are always responsive to your queries.
Stay with us & keep learning.
Related Articles
- How to Extract Data from a List Using Excel Formula (5 Methods)
- Extract Data from Cell in Excel (5 Methods)
- How to Pull Data from Multiple Worksheets in Excel VBA
- Extract Data from One Sheet to Another Using VBA in Excel (3 Methods)
- How to Extract Year from Date in Excel (3 Ways)
- How to Extract Month from Date in Excel (5 Quick Ways)
