How to Create Excel Data Connection to Another Excel File

Get FREE Advanced Excel Exercises with Solutions!

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, BC, 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.

dataset of excel data connection to another file


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.

Using Connections Function to Create Excel Data Connection to Another Excel File

Steps:

  • First open a new workbook in Excel.
  • Then go to the Data tab and select Existing Connections

Using Connections Function to Create Excel Data Connection to Another Excel File

  • After that, select the Browse for More option.

Using Connections Function to Create Excel Data Connection to Another Excel File

  • After that, select the source file.
  • Then, click on the open button.

Using Connections Function to Create Excel Data Connection to Another Excel File

  • Then select the desired table from the dialog box.
  • After that, press Ok.

Using Connections Function to Create Excel Data Connection to Another Excel File

  • A window will appear then.
  • After that, select the Table option & destination of the table.
  • Hence, press Ok.

Using Connections Function to Create Excel Data Connection to Another Excel File

  • Consequently, this command will paste the table by setting the connection. You will find the following result shown in the picture.

Using Connections Function to Create Excel Data Connection to Another Excel File

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.

Implementing VBA to Create Excel Data Connection to Another Excel File

  • Then, select the Module option from there.

Implementing VBA to Create Excel Data Connection to Another Excel File

  • 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

Implementing VBA to Create Excel Data Connection to Another Excel File

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.

Implementing VBA to Create Excel Data Connection to Another Excel File

  • Then, a window will pop up to select the source range.

Implementing VBA to Create Excel Data Connection to Another Excel File

  • After that, select the table from B4 to D10.

Implementing VBA to Create Excel Data Connection to Another Excel File

  • The dialog box will show the source range here.

Implementing VBA to Create Excel Data Connection to Another Excel File

  • After pressing Ok, select the destination cell B4.
  • Then press Ok.

Implementing VBA to Create Excel Data Connection to Another Excel File

  • As a result, this command will show the new connected data table like the picture given below.

Implementing VBA to Create Excel Data Connection to Another Excel File

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.

Applying Power Query to Create Excel Data Connection to Another Excel File

  • After that, select the desired file and click on the Import option.

Applying Power Query to Create Excel Data Connection to Another Excel File

  • From the Navigator panel, select the Power Query It will preview in the right side.
  • Then, click on the Transform Data option.

Applying Power Query to Create Excel Data Connection to Another Excel File

  • 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.


Related Articles

Souptik Roy

Souptik Roy

Hello there. This is Souptik Roy. I graduated from Naval Architecture & Marine Engineering department. I am trying to explore the world of Microsoft Excel and want to increase my analytical power apart from my Engineering degree, as I have a curious mind. this is why I am trying to learn Excel Operations and write articles to help others.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo