How to Create Excel Data Connection to Another Excel File

In this article, I will show how to create an 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 an Excel data connection to another Excel file. Hopefully, this will help you to increase your Excel skills.

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 the 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


2. Implementing VBA Code 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: Excel VBA: How to Refresh All Data Connections


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


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.

Read More: How to Create a Data Source in Excel


Download Practice Workbook

Please download the workbook to practice yourself.


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

<< Go Back to Excel Data Connections | Importing Data in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Souptik Roy
Souptik Roy

Souptik Roy, a BSc graduate in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology, dedicated 1.5 years to the ExcelDemy project. During this time, he authored 50+ articles and reviewed 20+ for ExcelDemy. Presently, he is a designer and content developer at YouHaveGotThisMath and Brainor, sister concerns of ExcelDemy. His educational content spans science, mathematics, and grammar. Roy's interests include developing creative ideas, visualizing concepts with tools like Adobe Illustrator, and problem-solving within Microsoft... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo