Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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! Full Feature Free Trial 60-day!

ad combine sheets books 1


arrow blue right bubbleExtract 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 - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

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.

doc extract data from workbook 2

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.

Extract without formula and formatting:
Extract with formula and formatting:
doc extract data from workbook 7

Demo: Extract data from one closed workbook or text file in Excel

In this Video, the Kutools tab and the Kutools Plus tab are added by Kutools for Excel. If need it, please click here to have a 60-day free trial without limitation!


arrow blue right bubble 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 - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

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

In this Video, the Kutools tab and the Kutools Plus tab are added by Kutools for Excel. If need it, please click here to have a 60-day free trial without limitation!


arrow blue right bubbleRelated articles:



Recommended Productivity Tools for Excel

Kutools for Excel Helps You Always Finish Work Ahead of Time, and Stand Out From Crowd

  • More than 300 powerful advanced features, designed for 1500 work scenarios, increasing productivity by 70%, give you more time to take care of family and enjoy life.
  • No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
  • Become an Excel expert in 3 minutes, Complicated and repeated operations can be done in seconds, 
  • Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
  • 110,000 highly effective people and 300+ world-renowned companies' choice.
  • 60-day full features free trial. 60-day money back guarantees. 2 years of free upgrade and support.

Brings Tabbed Browsing and Editing to Microsoft Office, Far More Powerful Than The Browser's Tabs

  • Office Tab is designed for Word, Excel, PowerPoint and Other Office Applications: 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!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.