Excel Import Data – From another Workbook – Query External Source

Import Data from Excel to Excel

To pull data from another workbook to current active Excel, try these methods.

1. Data Import option to query data from another workbook.
2. Excel Import data from another workbook using external reference.
3. Using VBA Macro to Import data from another Workbook.

Except for the third option, the first two options explain about how to pull data from another Excel using build in options. The last one is about using VBA macro programming to extract data.

1. How to make Excel Import Data From Another Workbook

Use the below menu option to make Excel pull data from another workbook.

1. Open Excel Workbook
2. Choose Data Tab from Menu. (Keyboard Shortcut: ‘Alt + A + FO’)
3. Click on ‘From Other Sources’
4. Select ‘From Microsoft Query’
5. Choose ‘Excel Files’ in the popup window
6. Select the file path of Excel to be imported.
7. Choose sheet or table or view to be imported.
8. Configure fields & Click Finish.
9. Choose destination cell to Import data.

This completes the process of importing data from another workbook to an Excel file. This method is applicable in Excel versions from 2003 or above. You can also save the query & rerun it anytime to import the data later.

Also, the data refresh is also possible. Just click on the table that you have imported. From Design tab, just click “Refresh” button in the External Data Table section.

This method is very useful when you need to consolidate data from different Excel spreadsheets & summarize them to prepare your dashboards.

2. Import Data from another Workbook using External Reference

If you wonder how to pull data from another Excel file, this is the simplest method available.

To understand this, just type a ‘=’ Equal to symbol in any cell. And then type the address of any cell in another workbook in this format.

=’[D:/<File Folder Path>/sample.xlsx]<SheetName>’!<Cell Address>
=’[D:/Folder/Import_Excel_Data_From.xlsx]Sheet1’!$A$1

If we enter any cell address from the same workbook, then it is called internal reference. Since we are entering address of a cell in external workbook, it is called external reference.

Using this we can also import data from another sheet using the formula ‘=Sheet1!$A$1’ or refer this page too.

In both these methods, the data can be fetched at any time, as long as the path of the external Excel file remains the same and the spreadsheet name is not modified by anyone. The query & external reference will not work, if anyone of this parameter is modified.

3. Using Excel VBA to import Data from another Workbook

If you want an Excel VBA to get data from another workbook, then refer this page.

This page has explanation on the most used data importing techniques using VBA programming. It has VBA to read Excel file whether it is closed or in open status. Learn how to use Workbook object in Excel VBA macro to read or write data to non-active workbook.