How to extract data from another workbooks or text files in Excel?
Normally we can copy data from an opening workbook and then paste into another workbook. However, sometimes you may want to extract data from a closed workbook without opening it, or even extract data from other file types, such as text files, how to get it done in Excel? In this article I will show you a way to extract data from another closed workbooks or text files easily in Excel.
Extract data from one closed workbook or text file in Excel
Extract data from multiple closed workbooks in Excel
Easily combine multiple worksheets/workbooks into single worksheet/workbook
It may be tedious to combine dozens of sheets from different workbooks into one sheet. But with Kutools for Excel’s Combine (worksheets and workbooks) utility, you can get it done with just several clicks!
Extract data from another closed workbook or text files with Kutools for Excel
Kutools for Excel is an excellent Excel add-in with dozens of handy utilities. It’s Insert File at Cursor utility can help you to extract data from closed workbook, text files or any other files and insert into current worksheet easily.
Kutools for Excel - Packed with over 300 essential tools for Excel. Enjoy a full-featured 30-day FREE trial with no credit card required! Download now!
Step 1: Put cursor at the cell where you will insert extracted data. In our example, we select the Cell D4. Then click Enterprise > Import / Export > Insert File at Cursor.
Step 2: In the opening Insert File at Cursor dialog box, click the Browse button.
Note: If you want to extract data from other files without formulas, please check the Values only (no formulas) option; if you want to extract data without formatting, please check the Do not transfer formatting option.
Step 3: In the coming Select a file to be inserted at the cell cursor position dialog box, open the folder that the workbook or other file is in, click to select the file you will extract, and click the Open button.
Notes:
1. For inserting data from Text file, please check the Text Files is selected behind the File name box in the Select a file to be inserted at the cell cursor position dialog box.
2. If you select a workbook with multiple worksheets, a Select a Worksheet dialog box will be thrown out. Just click to select the worksheet you will extract data from, and click the Ok button.
Now you will see all data in the specified worksheet is extracted and inserted into current worksheet.
Demo: Extract data from one closed workbook or text file in Excel
Extract data from multiple closed workbooks in Excel
With Kutools for Excel's Combine worksheets and and workbooks feature, you can easily extract data from multiple worksheets and workbooks and place extracted data into one sheet. Please do as follows:
Kutools for Excel - Packed with over 300 essential tools for Excel. Enjoy a full-featured 30-day FREE trial with no credit card required! Download now!
1. Click Enterprise > Combine.
2. In the opening Combine Worksheets - Step 1 of 3 dialog box, please check the Combine multiple worksheets from workbooks into one worksheet option, and click the Next button.
3. In the Combine Worksheets - Step 2 of 3 dialog box, please:
(1) Click Add > File / Folder to add workbooks you will extract data from;
(2) In the Workbook list section, please check the workbooks you will extract data from;
(3) In the Worksheet list section, please check the worksheets you will extract data from;
(4) In the worksheet list section, click the browse button 
behind each worksheet name to specify the range you will extract from each worksheet.
(5) Click the Next button.
4. In the Combine Worksheets - Step 3 of 3 dialog box, please specify the combination rule as you need, and click the Finish button.
Now all specified range from closed workbooks are extracted and placed in one worksheet.
Demo: Extract data from multiple closed workbooks in Excel
Related articles:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
