Power Query Problems

How to Append BOM Files Automatically Using Excel Power Query or VBA

Muhammad Talha Oct 7, 2026 869 views

Question details

The user needs to import one of many similarly formatted Bills of Materials (BOM) files and automatically append their rows to a master worksheet based on a selected file name.

How to Append BOM Files Automatically Using Excel Power Query or VBA

Product: Excel
Device & OS: not provided
Scenario: Consolidating multiple BOM files into a master tracker automatically without manual copying and pasting.
Observed behavior: Manual data entry is inefficient, INDIRECT functions fail to reference closed workbooks properly, and functions like VLOOKUP cannot append variable-length tables.

Before you start

Ensure all your source BOM files share a consistent column structure and are saved in a dedicated, accessible folder on your computer before attempting to consolidate them.

Solution 1Recommended

Use Power Query to Combine BOM Files from a Folder

This is the best method when your source workbooks are stored in a consistent folder and have matching data structures.

Power Query can seamlessly import files from a designated folder, merge tables with identical structures, and automatically refresh your master file whenever new source files are added to the folder.

Organize Source Files

Place all the BOM Excel files you intend to append into a single, dedicated folder on your computer.

Get Data from Folder

Open your master Excel workbook. Navigate to the Data tab, click Get Data, select From File, and choose From Folder. Browse to and select your dedicated BOM folder.

Combine and Transform Data

In the folder preview dialog, click the Combine button and select Combine & Transform Data. Choose the specific sheet or table name that contains the BOM data across all files, then click OK.

Load to Master Worksheet

The Power Query Editor will open, allowing you to review the appended data. Once verified, click Close & Load in the top-left corner to import the combined BOM records into your master worksheet.

Use Power Query to Combine BOM Files from a Folder

Automatic Refresh: When you add new BOM files to the folder in the future, simply right-click your master table and select Refresh. Power Query will automatically append the new data.

Solution 2

Use an Excel VBA Macro for Dynamic File Selection

VBA is ideal when the process must read a specific file name from a cell, open that exact workbook, and append variable-length data.

Free Microsoft Office alternative

Consolidate BOM Data with WPS Office

If you are looking for a highly compatible and cost-effective way to manage your Bills of Materials and run VBA macros, WPS Office provides a lightweight, full-featured alternative to Microsoft Excel.

1. Download and Install: Download WPS Office from the official website and follow the installation prompts to set it up on your device.
2. Open Your Workbooks: Launch WPS Spreadsheet and seamlessly open your existing Microsoft Excel master files and BOM trackers.
3. Run Your Macros: Access the Developer tab in WPS Spreadsheet to enable and run your existing VBA macros for appending data.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .xlsm, .csv).Supports standard VBA macros for automating data consolidation and file appending.Familiar spreadsheet interface that requires zero learning curve.Lightweight and free, ensuring fast performance even with large BOM datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I use the INDIRECT function to append closed BOM files?

The INDIRECT function cannot reliably reference or retrieve data from closed workbooks. If the source BOM file is not currently open in Excel, the INDIRECT formula will return a #REF! error, making it unsuitable for automated data aggregation from saved files.

Can VLOOKUP be used to append multiple rows from a BOM file?

No. VLOOKUP is designed to search for a single lookup value and return a corresponding result from the same row. It cannot dynamically pull in or append variable-length tables or entire datasets from external files.

Do Google Sheets functions like QUERY or IMPORTRANGE work in desktop Excel?

No, functions such as QUERY and IMPORTRANGE are proprietary to Google Sheets. For desktop Excel, you must rely on Power Query, VBA macros, or standard Data Consolidation features to link and merge external files.

Excel & Data Problems