How to Import Data from another Workbook in Excel

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.

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.

import data from another workbook1

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

import data from another workbook2

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

import data from another workbook3

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

import data from another workbook4

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

import data from another workbook11

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

import data from another workbook5

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

import data from another workbook6

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

import data from another workbook7

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.

Get the position of the nth using excel vba1

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

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

convert column number to letter3

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

import data from another workbook8

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.

import data from another workbook9

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

#7 lets see the result:

import data from another workbook10

 

 

You might also like:

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar