How to Import Excel Data from Multiple Folders to a Master Workbook
Question details
The user needs to extract specific cell values from multiple Excel workbooks located across various server folders and compile them into a single master workbook.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Compiling inspection data from scattered Excel files into a single master workbook, bypassing non-Excel files like PDFs and images stored in the same directories.
- Observed behavior
- If Power Query attempts to process non-Excel files in those folders, it throws an error. The user also requires that the master workbook does not maintain live links to the source files once the data is successfully collected.
Identify the main root folder path that contains all your target Excel files, and note the common file extensions (.xlsx, .xls) for the workbooks you want to import to help isolate them from PDFs and images.
Use Power Query to Extract and Filter Excel Data
Retrieve files from a root folder and filter by file extension to ensure only Excel workbooks are processed, avoiding formatting errors from PDFs and images.
Power Query allows you to target a main directory and dynamically pull data from its subfolders. To prevent errors caused by attempting to read images or PDFs as Excel workbooks, you must filter out non-Excel files before expanding the binary content.
Open your master workbook, go to the 'Data' tab on the ribbon, and select 'Get Data' > 'From File' > 'From Folder'.
Enter or browse for the path to the main server folder containing your scattered subfolders and inspection files, then click 'OK'.
In the preview window that appears, click 'Transform Data' instead of Load. This opens the advanced Power Query Editor where you can refine the file list.
Click the drop-down arrow on the 'Extension' column header and apply a filter to only include '.xlsx' or '.xls'. Uncheck all other extensions like .pdf, .png, or .jpg.
Click the 'Combine Files' icon (double downward arrows) in the 'Content' column header to extract the actual Excel data from the filtered files.
Format the combined data as needed, then click 'Close & Load' in the Home tab to push the isolated plain text data directly to your master workbook.
Use a VBA Macro to Consolidate Data Without Links
If you prefer not to use Power Query, a VBA script can loop through folders, open specific files, copy cells, and paste them as plain values.
Easily Manage Multiple Workbooks with WPS Office
While advanced Power Query automations are Microsoft Excel specific features, you can seamlessly open, manage, and manually consolidate Excel workbooks using WPS Office. It offers high compatibility with Microsoft formats, a familiar interface, and excellent data processing tools—completely free.
- 1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite.
- 2. Open Your Workbooks: Launch WPS Spreadsheets and easily open your master .xlsx file alongside your inspection reports.
- 3. Consolidate Data: Use WPS Spreadsheets' intuitive Data tools to copy, organize, and consolidate values efficiently without needing complex M-code.
Frequently Asked Questions
Why do I get an error when importing files from a folder using Power Query?
This usually happens when Power Query attempts to read non-Excel files (like PDFs, JPGs, or hidden system files) as Excel workbooks. You must filter the 'Extension' column to only include .xlsx or .xls before expanding the 'Content' column.
Will my master workbook break if I delete the original inspection files?
If you use Power Query to load the data, the imported results remain stored as text/values in your master workbook even if the source files are moved or deleted. However, triggering a query refresh will cause an error if the source files are missing from the original path.
Do all source files need to be saved in the exact same folder for Power Query to work?
No. When using the 'From Folder' option, Power Query automatically retrieves files from all subfolders within the main root folder you select. Just ensure your main path is high enough in the directory tree to encompass all your inspection folders.
Can I import only specific cells rather than the whole spreadsheet?
Yes. Once the files are combined in the Power Query Editor, you can select only the columns you need, remove the rest, and apply row filters to keep only the specific data parameters required for your master workbook before loading it.
