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.
Table of Contents
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.
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: