How to Automate Reports from Excel Workbooks in a Folder
Question details
The user needs to combine data from multiple Excel workbooks stored in a folder into a single, automatically updating report, while allowing specific users to view and edit the source files.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a consolidated, auto-updating weekly report from multiple Excel files added regularly to a specific directory.
- Observed behavior
- A unified master report that automatically refreshes when new weekly files are added, with correct file sharing permissions set up for team collaboration.
Ensure all the Excel workbooks you plan to combine have an identical column structure and sheet names to prevent data mismatch errors during the import process.
Use Power Query to Combine Files from a Folder
Power Query is the most efficient built-in tool to import and consolidate multiple Excel workbooks from a single folder automatically.
By setting a specific folder as your data source, Power Query can automatically detect new files added to that directory and append their data to your master report.
Open all source Excel workbooks and verify that the column headers, data types, and sheet names match exactly across all files.
Open a new Excel workbook. Go to the 'Data' tab, click 'Get Data', select 'From File', and then choose 'From Folder'.
Browse to the folder containing your weekly workbooks and click 'Open'. In the preview window, click the 'Combine' dropdown button and select 'Combine & Load'.
Select the specific sheet or table you want to extract from the sample file and click 'OK'. Power Query will merge the matching data into a single master table.
When new files are added to the folder in the future, open your master report, navigate to the 'Data' tab, and click 'Refresh All' to instantly update the report with the new data.
Store and Share the Folder via SharePoint or OneDrive
To allow selected users to read and edit the source files, host the folder on a cloud platform like SharePoint or OneDrive.
Easily Combine Multiple Excel Workbooks Using WPS Spreadsheet
WPS Spreadsheet offers a built-in 'Merge Workbooks' feature, allowing you to quickly consolidate multiple weekly files into a single report without needing advanced Power Query knowledge.
- 1. Open WPS Spreadsheet: Launch WPS Office and open a blank spreadsheet to act as your master report.
- 2. Access the Merge Tool: Navigate to the 'Data' tab on the top ribbon and select the 'Merge' button.
- 3. Add Your Files: Choose 'Merge Multiple Workbooks into a Single Workbook', click 'Add Files', and select all the weekly reports from your target folder.
- 4. Execute Merge: Follow the on-screen prompts to finalize the merge, consolidating all your data into one seamless report.
Frequently Asked Questions
Why is my Power Query report not updating when I add new files to the folder?
Your report might not be set to refresh automatically. You need to manually click 'Refresh All' under the Data tab, or go to 'Connection Properties' to enable a background refresh upon opening the file.
Can I combine workbooks that have different column headers?
While Power Query can combine files with different headers, the resulting table will have null values for mismatched columns. It is highly recommended to standardize your column headers across all workbooks before combining them.
How do I fix file path errors when sharing the master report?
If the master report and source folder are moved to a shared drive or SharePoint, the local data source path will break. Go to 'Data' > 'Get Data' > 'Data Source Settings' and update the file path to point to the new cloud or network location.
Will other users see the updated report if they don't have access to the source folder?
Users can view the historical data currently loaded and saved into the master report, but they will not be able to refresh the query to pull new data unless they have read access permissions to the source folder.
