How to Create an Excel Data Connection to Another Excel File

This is the sample dataset.

dataset of excel data connection to another file

Method 1 – Using the Connections Function to Create an Excel Data Connection to Another Excel File

Steps:

Open a new workbook.
Go to the Data tab and select Existing Connections.

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

Select Browse for More.

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

Select the source file.
Click Open.

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

Choose the table in the dialog box.
Click OK.

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

Select Table and enter the destination in Existing Worksheet.
Click OK.

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

The table will be pasted:

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

Method 2 – Using a VBA Code to Create an Excel Data Connection

Steps:

Press Alt + F11.
Select the icon shown below.

Implementing VBA to Create Excel Data Connection to Another Excel File

Select Module.

Implementing VBA to Create Excel Data Connection to Another Excel File

Enter 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