Power Query Problems

Sum Values Across Multiple Excel Workbooks Using Power Query or VBA

Phi Hung Vo Oct 1, 2026 868 views

Question details

The user needs to extract and sum subtotal values from multiple monthly patient invoice workbooks stored across year and month folders on OneDrive.

How to Sum Values Across Multiple Excel Workbooks Using Power Query or VBA

Product: Excel
Device & OS: not provided
Scenario: Automating the consolidation of financial subtotals from hundreds of distributed invoice files without manually opening each workbook.
Observed behavior: The user seeks an efficient way to compile data into monthly and yearly totals automatically, utilizing either Power Query or a VBA macro.

Before you start

Ensure all your invoice workbooks share a uniform structure, meaning the worksheet name and the specific cell address containing the subtotal are identical across every file. If using VBA, confirm that your OneDrive folders are synced locally to your computer to prevent path errors.

Solution 1Recommended

Use Power Query to Combine Files from a Folder

Power Query is the most robust and maintainable method for extracting and summarizing data from multiple files across main folders and subfolders.

Power Query handles bulk file imports efficiently without writing code. It can dynamically scan a main 'Year' folder, dive into 'Month' subfolders, and extract the necessary cell values from all contained workbooks.

Import the Folder

In Excel, navigate to the Data tab, click on Get Data > From File > From Folder. Browse to your main 'Year' folder on your local OneDrive sync and click Open.

Transform the Data

When the file list appears, click 'Transform Data' instead of Load. This opens the Power Query Editor. Filter the 'Extension' column to only include .xlsx files to exclude unnecessary files.

Combine the Files

Click the 'Combine Files' icon (two downward arrows) on the Content column header. In the prompt, select the specific worksheet name that contains your invoice data, and click OK.

Extract the Subtotal Cell

Within the query editor, remove extraneous columns, leaving only the file path (to extract month/year) and the data columns. Filter the rows to isolate the specific known cell containing the subtotal.

Group and Load

Use the 'Group By' feature on the Home tab to group the data by Year and Month, choosing to sum the extracted values. Finally, click Close & Load to output the summary table into your main workbook.

Use Power Query to Combine Files from a Folder

Solution 2

Use a VBA Macro to Loop Through Folders

A VBA macro allows for precise programmatic control to open each workbook in the background, read a specific cell, and log the results into a summary sheet.

WPS Spreadsheet Data Tools

Merge Multiple Workbooks Easily with WPS Spreadsheet

WPS Spreadsheet includes a built-in 'Merge Workbooks' utility that allows you to easily combine multiple files from different folders into one sheet without needing complex VBA or Power Query setups. This makes compiling monthly and yearly invoices incredibly straightforward.

1. Open WPS Spreadsheet: Launch WPS Office and open a new blank spreadsheet document to act as your master summary file.
2. Access the Merge Tool: Navigate to the Data tab on the top ribbon, look for the 'Merge Workbooks' or 'Consolidate' option, and click it.
3. Add Your Folders: Click 'Add Folder' and select your main year/month folders on OneDrive. Choose the option to merge multiple workbooks into a single workbook.
4. Consolidate the Data: Once WPS automatically combines the sheets, simply use the built-in Subtotal or PivotTable features to quickly aggregate your monthly and yearly totals.

Built-in Merge Workbooks feature requires no coding or query knowledge.Fully compatible with Microsoft Excel (.xlsx, .xls) formats.Extremely lightweight and fast for processing hundreds of spreadsheet files.100% free Office suite with a familiar, easy-to-use tabbed interface.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use Power Query to extract data from a specific cell in every workbook?

Yes. Once you connect to the folder using Power Query, you can expand the file contents and filter the rows and columns to isolate the exact cell (for example, row 25, column G) containing your subtotal across all imported files.

Do both files and folders need to be stored locally for VBA to work?

While VBA can be scripted to interact with SharePoint/OneDrive URLs, it is significantly easier and less error-prone to ensure your OneDrive client is syncing the folders to your local hard drive. This allows VBA to use standard local file paths (e.g., C:\Users\Name\OneDrive\Invoices).

Will Power Query automatically update my totals when new monthly invoices are added?

Yes. Once your Power Query connection and grouping steps are set up, you simply need to click 'Refresh All' on the Data tab. Power Query will rescan the folder, pick up the newly added workbooks, and update the summary table automatically.

Which method is faster for processing hundreds of invoice files: Power Query or VBA?

Power Query is highly optimized for extracting data in bulk and is generally much faster and more stable. VBA has to simulate opening, reading, and closing the Excel application for every single workbook, which uses more memory and takes significantly longer.

Excel & Data Problems