Data Import & Export

How to Import Data from Multiple Excel Workbooks Using File Paths

Maira Mehtab Sep 28, 2026 869 views

Question details

The user needs to retrieve specific cell values (B23:B25) from various separate project workbooks and consolidate them into columns (D:F) of a main workbook based on source file paths specified in column C.

Product: Excel
Device & OS: not provided
Scenario: Consolidating specific project information from multiple external Excel files stored in different directory locations into a single summary workbook.
Observed behavior: Excel formulas do not natively support dynamically changing an external workbook reference by simply concatenating a file path stored in a cell, requiring alternative solutions like Power Query or VBA.

Before you start

Ensure you have the exact file paths for all source workbooks listed in your main file, and verify that the target sheets and cell references (e.g., B23:B25) are consistent across all project files.

Solution 1Recommended

Use Power Query to Import Data from Multiple Files

Power Query is the most robust and scalable method for consolidating data from multiple external workbooks dynamically without relying on volatile formulas.

Power Query allows you to extract, transform, and load data from folders or specific lists of file paths. This bypasses the limitations of the INDIRECT function, which cannot pull data from closed external workbooks.

Open Power Query

In your main workbook, navigate to the 'Data' tab and select 'Get Data' > 'From File' > 'From Folder' (if they share a directory) or 'From Workbook'.

Load the File Paths Table

If using specific paths from Column C, format those paths as a Table, load it into Power Query, and create a custom column to fetch the workbook contents using the formula Excel.Workbook(File.Contents([File Path])).

Expand and Filter the Data

Click the expand icon on the new column to reveal the sheets inside the workbooks. Filter for your specific target worksheet (e.g., 'Project Info') and extract the rows corresponding to cells B23:B25.

Pivot and Load to Workbook

Pivot the extracted values so they align into columns D, E, and F. Finally, click 'Close & Load' on the Home tab to return the consolidated data into your main Excel workbook.

Scalable Solution: Once set up, adding a new project path to your source list and clicking 'Refresh All' will automatically import the new data without manually editing formulas.

Solution 2

Create a VBA Macro to Extract Data

A VBA macro can loop through the file paths in column C, silently open each workbook, extract the values, and place them in the correct columns.

Solution 3

Create Manual External References

If you only have a few project workbooks, manually linking to the external cells is a straightforward approach that does not require scripting or advanced queries.

Free Microsoft Office alternative

Looking for a Seamless Alternative for Data Management?

If managing complex data imports or dealing with formula limitations feels overwhelming, try WPS Office. It provides an intuitive, highly compatible environment for your spreadsheets, ensuring you can manage external links and robust datasets effortlessly without the heavy subscription fees.

1. Download and Install: Visit the official WPS Office website to download and install the free productivity suite.
2. Open Your Workbooks: Launch WPS Spreadsheets and open your existing .xlsx or .xlsm consolidation files.
3. Manage External Data: Use familiar formulas, external links, and macros to organize your scattered project files efficiently.

Fully compatible with Microsoft Excel formats (.xlsx, .xlsm, .csv) and external linking featuresLightweight software suite that installs quickly and runs smoothly on multiple platformsBuilt-in support for advanced formulas, basic querying, and cross-workbook data referencingFamiliar tabbed user interface ensuring a seamless migration from Microsoft Office

microsoft office alternative - wps office

Frequently Asked Questions

Can I use the INDIRECT function to dynamically reference closed Excel workbooks?

No, the INDIRECT function only works when the referenced external workbook is currently open. If the target workbook is closed, the formula will return a #REF! error. Because of this limitation, you cannot dynamically build file paths for closed workbooks using simple cell concatenation.

Why doesn't concatenating a file path in an Excel formula create a working link?

Excel evaluates formulas based on structural references at the time of calculation. It interprets concatenated strings purely as text, not as actionable structural paths to external files, unless routed through functions like INDIRECT—which inherently fail on closed workbooks.

How do I extract data from multiple files automatically without macros?

The most effective non-macro approach is using Power Query. By setting up a query to pull data from a 'Folder' or a list of file paths, you can simply click 'Refresh All' to update your main workbook whenever new files are added to the source locations.

Can I use VBA to pull data without physically opening the source workbooks?

Yes, while standard VBA opens the workbook in the background, you can use specialized methods like ADO (ActiveX Data Objects) or ExecuteExcel4Macro to query and retrieve data from closed workbooks without opening them, though this requires more advanced coding.

Excel & Data Problems