Souptik Roy
In this article, I will show how to create Excel data connection to another Excel file by using 3 easy methods. When you work on an Excel worksheet, you may need to do various operations. Data connection to another Excel file is one of the important operations among them. In this article, I will show you three easy ways of creating Excel data connection to another excel file. Hopefully, this will help you to increase your Excel skills.
Download Practice Workbook
Please download the workbook to practice yourself.
3 Effective Ways to Create Excel Data Connection to Another Excel File
This is the dataset I am going to use in this article for every method. The database contains three columns, B, C, and D called ID, Name, and Joining Date. Hence, follow the steps of every method one by one. Moreover, I have added the necessary illustrations for every step for your better understanding.
1. Using Connections Function to Create Excel Data Connection to Another Excel File
In this part of this article, I will show the connections function to create Excel data connections to another Excel file. I have considered the following dataset for this method. Follow the instructions described in the following steps.
Steps:
- First open a new workbook in Excel.
- Then go to the Data tab and select Existing Connections
- After that, select the Browse for More option.
- After that, select the source file.
- Then, click on the open button.
- Then select the desired table from the dialog box.
- After that, press Ok.
- A window will appear then.
- After that, select the Table option & destination of the table.
- Hence, press Ok.
- Consequently, this command will paste the table by setting the connection. You will find the following result shown in the picture.
This is how you will make data connections by using the connections function.
Read More: How to Find External Data Connections in Excel (5 Quick Tricks)
2. Implementing VBA to Create Excel Data Connection
In this part, I will insert a VBA code to create a data connection to another Excel file. Follow the following steps. Use the former new sheet here.
Steps:
- Press the Alt + F11 buttons simultaneously.
- Then select the icon shown below.
- Then, select the Module option from there.
- After that, copy down the following code in the module.
Sub ImportDatafromotherworksheet()
Dim ss_wkbCrntWorkBook As Workbook
Dim ss_wkbSourceBook As Workbook
Dim ss_rngSourceRange As Range
Dim ss_rngDestination As Range
Set ss_wkbCrntWorkBook = ActiveWorkbook
With Application.FileDialog(msoFileDialogOpen)
.Filters.Clear
.Filters.Add "Excel 2007-13", "*.xlsx; *.xlsm; *.xlsa"
.AllowMultiSelect = False
.Show
If .SelectedItems.Count > 0 Then
Workbooks.Open .SelectedItems(1)
Set ss_wkbSourceBook = ActiveWorkbook
Set ss_rngSourceRange = Application.InputBox(prompt:="Select source range", Title:="Source Range", Default:="A1", Type:=8)
ss_wkbCrntWorkBook.Activate
Set ss_rngDestination = Application.InputBox(prompt:="Select destination cell", Title:="Select Destination", Default:="A1", Type:=8)
ss_rngSourceRange.Copy ss_rngDestination
ss_rngDestination.CurrentRegion.EntireColumn.AutoFit
ss_wkbSourceBook.Close False
End If
End With
End Sub
Here, we declared some variables in the VBA code to create a data connection to another excel file and used the statements to apply some conditions to create a data connection to another excel file.
- Then, press the F5 button to run the code.
- After running the code, a dialog box will show up. Select the desired file here and press the Open button.
- Then, a window will pop up to select the source range.
- After that, select the table from B4 to D10.
- The dialog box will show the source range here.
- After pressing Ok, select the destination cell B4.
- Then press Ok.
- As a result, this command will show the new connected data table like the picture given below.
Read More: How to Create a Data Source in Excel (with Simple Steps)
3. Applying Power Query
Here, I will use a power query to form a data connection with another file. This is a short process. I hope you will understand the process easily.
Steps:
- First, open a new sheet.
- Then go to the Data tab in your toolbar.
- Select the Get Data option then.
- After that, click on the From File option.
- Then select From Excel Workbook.
- After that, select the desired file and click on the Import option.
- From the Navigator panel, select the Power Query It will preview in the right side.
- Then, click on the Transform Data option.
- Now, go to the Home tab and select Remove Rows.
- After that, select Remove Top Rows.
- Then, specify the number of rows to remove.
- After that, select Ok.
- Then, this command will show the preview like the picture given below.
- Then, go to the Transform After that, select Use First Row as Headers.
- You can see the preview in the selected area.
- As a result, this command will show you the result like the picture given below.
This is how you can apply a power query to create a data connection to another file.
Read More: [Fixed!] External Data Connections Have Been Disabled in Excel
Linking Excel Workbooks for Automatic Update
Here, I will show how to link excel workbooks for automatic updates. I have used two data tables here from different workbooks. You can see the different names of the workbooks in the next pictures. Follow the following steps one by one.
Steps:
- Copy the joining date from the selected area shown in the figure.
- Then, right-click on the Joining Date column of the Report-1 file.
- Meanwhile, select the Paste Special option.
- After that, select the Paste Link option.
- By using this command, you will get the Joining Dates connected with the other file.
This is how you can link an Excel workbook with another.
Conclusion
In this article, I have tried to explain data connection to another excel file. I hope, you have learned something new from this article. Now, extend your skill by following the steps of these methods. You will find such interesting blogs on our website Exceldemy.com. I hope you have enjoyed the whole tutorial. If you have any queries, feel free to ask me in the comment section. Don’t forget to give us your feedback.
