Import Data from Excel to Excel
To pull data from another workbook to current active Excel, try these methods.
- Data Import option to query data from another workbook.
- Excel Import data from another workbook using external reference.
- 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.Visual Basic1234567891. Open Excel Workbook2. 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 window6. 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.Visual Basic12=’[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.
Previous Post: Convert a Formatted Cell in Excel into HTML Tag using VBA
Next Post: Excel VBA Check – IF String Contains – Only Letters
Excel Import Data – From another Workbook – Query External Source