To insert data from multiple Excel files into a Word template, the most reliable method is using Microsoft Word’s Mail Merge feature combined with Power Query. This approach allows you to consolidate numerous CSV or Excel sources into a single dataset without manual copying. By linking your Word document to this unified query, you can automatically generate monthly account statements for every transaction file in your folder.
Preparing Your Data Source
Before opening Word, you must ensure all your individual Excel or CSV files are stored in a single, dedicated folder. Each file should contain headers in the first row that match exactly across all documents. For example, if one file has columns named
Date,
Amount, and
Description, every other file must have those same column names. Inconsistent headers will cause Power Query to fail during the merge process.
If your files are currently scattered across different directories, move them all to one location. Do not include any non-data files like images or readmes in that folder, as the import process will attempt to parse them and likely throw errors. Ensure that no other application has these files open, particularly Excel, as locked files cannot be read by the background process.
Creating the Unified Query with Power Query
Power Query is an essential tool for this task because it can dynamically combine tables from multiple files. Open a blank Excel workbook to serve as the bridge between your data files and your Word template. Navigate to the
Data tab on the ribbon and select
Get Data. From the dropdown menu, choose
From File, then select
From Folder.
In the dialog box that appears, browse to the folder containing your transaction files and click
OK. Excel will display a list of the files found in that directory. You will see a preview window with a button labeled
Combine & Load. Click this button to instruct Excel to stack all the tables together. A new window may appear asking how to combine the files; confirm that the option to use the primary file as the basis is selected. This action creates a single table where rows from all your separate Excel files are appended vertically.
Once the preview looks correct, click
Load. The data will now reside in a new worksheet within your Excel workbook. It is crucial to give this sheet a clear name, such as
ConsolidatedData, so you can easily reference it later. Save this Excel workbook in the same folder as your Word template to ensure relative paths remain stable.
Configuring the Word Template
Open your Word document, which should be designed as a statement template. Place your cursor where you want the first piece of data to appear, such as the customer name or date. Go to the
Mailings tab and click
Select Recipients, then choose
Use an Existing List. Browse to the Excel workbook you just created and select the
ConsolidatedData sheet.
Word may ask you to specify which sheet to use; ensure you select the correct one containing your merged data. Once linked, you can insert fields by clicking
Insert Merge Field. Select fields like
Date or
Amount from the dropdown list. Repeat this process for every variable element in your statement. Static text, such as "Transaction Statement," should remain as plain text.
If you need to insert multiple rows of data for a single transaction record (for example, listing several items per customer), you might need to use a table merge field. However, for simple line-by-line statements, standard merge fields work perfectly. Preview your results by clicking
Preview Results to ensure the data aligns correctly with your layout.
Finalizing and Exporting
After verifying that the preview shows accurate data, click
Finish & Merge and select
Edit Individual Documents. This command generates a new Word document containing a separate page for each row in your consolidated data. Review the new document quickly to catch any formatting issues. If everything looks correct, save this new file as your final output.
This method ensures that whenever you add new Excel files to your source folder and refresh the Excel query, your Word template can instantly update with the latest information. This workflow eliminates the risk of manual entry errors and significantly reduces the time spent compiling monthly reports.
For more troubleshooting on data loading issues, you might find
Excel Won't Load Document on Double-Click helpful if your source files fail to open. Additionally, understanding
Solving Pasting Issues in Microsoft Word can help if you encounter formatting glitches when merging complex layouts.
Frequently Asked Questions
What if my Excel files have different numbers of columns?
Power Query will ignore columns that do not exist in all files, potentially resulting in null values for missing data. To avoid this, ensure all source files have identical column structures before starting the merge process.
Can I use this method for PDF files instead of Excel?
No, the
From Folder connector in Power Query is designed specifically for structured data formats like CSV and Excel. You would need to convert PDFs to Excel format first using a conversion tool.
How do I refresh the data after adding new files?
Right-click the consolidated table in Excel and select
Refresh. This updates the underlying data model, allowing you to re-run the mail merge in Word with the new information included.
Why are my dates appearing as serial numbers in Word?
This happens because Excel stores dates as numeric values internally. Format the cells in the Excel source as Date type before loading them into Power Query to preserve the correct display format.
Is there a limit to how many files I can combine?
While there is no hard limit on the number of files, performance may degrade if you combine thousands of large files. Consider splitting your data into smaller quarterly folders if you experience slow processing times.