This post will guide you how to import data from another workbook into your current worksheet in Excel. How do I copy data from a closed workbook with VBA Macro in Excel. How to insert worksheet from closed workbook into the current workbook in Excel 2013/2016.

1. Import Data from another Workbook

If you want to import data from another workbook, you can use the Connections feature to achieve the result in Excel. Here are the steps:

#1 go to DATA tab, click Connections command under Connections group. And the Workbook Connections dialog will open.

#2 click Add button in the Workbook Connections dialog box, and the Existing Connections dialog will appear.

#3 click Browse for More button in the Existing Connections dialog. And the Select Data Source dialog will open.

#4 select another workbook file that to be imported in the Select Data Source dialog box. Click Open button. And select one worksheet that you want to import. Click Ok button.

#5 click Close button to close the dialog.

#6 go to Data tab, click Existing Connections command under Get External Data group. And the Existing Connections dialog will appear.

#7 select a connection that you want to import. Click Open button in the Existing Connections. And the Import Data dialog will open.

#8 select one worksheet that you want to be import the data. Click Ok button.

#9 you would notice that the data has been imported from the selected connection.

2. Import Data from another Closed Workbook with VBA

You can also use an Excel VBA Macro to achieve the same result of importing Data from another closed workbook into a new worksheet in your active workbook. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

#4 paste the below VBA code  into the code window. Then clicking “Save” button.

Sub importDataFromAnotherWorkbook()
    ' Get workbook...
    Dim ws As Worksheet
    Dim filter As String
    Dim targetWorkbook As Workbook, wb As Workbook
    Dim Ret As Variant

    Set targetWorkbook = Application.ActiveWorkbook

    ' get the customer workbook
    filter = "Text files (*.xlsx),*.xlsx"
    Caption = "Please Select an input file "
    Ret = Application.GetOpenFilename(filter, , Caption)

    If Ret = False Then Exit Sub

    Set wb = Workbooks.Open(Ret)

    wb.Sheets(1).Move After:=targetWorkbook.Sheets(targetWorkbook.Sheets.Count)

    ActiveSheet.Name = "ImportData"
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

#6 Please Select an input file. Click OK button.

#7 lets see the result:

3. Video: Import Data from another Closed Workbook

This Excel video tutorial on importing data from another workbook, a crucial skill for efficient data management. We’ll explore two methods to achieve this seamlessly: leveraging the “Connections” feature and utilizing VBA code.

https://youtu.be/aoKkcq2jslU