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.

Select Browse for More.

Select the source file.
Click Open.

Choose the table in the dialog box.
Click OK.

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

The table will be pasted:

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

Steps:

Press Alt + F11.
Select the icon shown below.

Select Module.

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