Merge Excel Files in Power Automate

PDF4me Merge Excel Files action enables combining multiple Excel files into a single consolidated workbook in Power Automate with intelligent worksheet merging and format conversion. This powerful file consolidation feature supports merging all or selective worksheets from multiple source files, automatic worksheet name conflict resolution with suffix numbering, flexible sort ordering, and multiple output formats (XLSX, XLS, PDF, CSV), perfect for report consolidation, data aggregation, and multi-source Excel file combination across Microsoft 365 workflows.

Authenticating Your API Request

To access the PDF4me Web API through Power Automate, every request must include proper authentication credentials. Authentication ensures secure communication and validates your identity as an authorized user, enabling seamless integration between your Power Automate flows and PDF4me's powerful Excel file merging services.

Get your API Key

Key Features

• Multiple File Merging: Combine unlimited Excel files into one workbook
• Selective Worksheet Merging: Choose specific worksheets or merge all
• Automatic Conflict Resolution: Duplicate worksheet names automatically renamed
• Sort Control: Specify merge order with SortPosition property
• Multiple Output Formats: Save as XLSX, XLS, PDF, or CSV
• Content Preservation: Full worksheet content and formatting copied

Parameters

Complete list of parameters for the Merge Excel Files action. Configure these parameters to control file merging.

Important: Parameters marked with an asterisk (***) are required. Each document in the Documents list requires Base64-encoded file content.

Parameter	Type	Description	Example
File Content***	Base64	Source Excel File Content • Map Excel file from previous action output • Supports Excel files from SharePoint, OneDrive, email attachments • Can be dynamically retrieved from flow variables • Must be valid Excel document (.xlsx, .xls formats)	[File Content from Get File]
File Name***	String	Excel Document Name • Specify Excel file name with extension (.xlsx, .xls) • Used for processing and output file naming • Must include proper file extension • Supports dynamic naming from flow variables	data.xlsx
Documents***	Array	List of Files to Merge • Array of MergeDocument objects with properties: FileContent* (Base64), Filename* (string), SortPosition (number, default: 0, ascending order), WorksheetsToMerge (array of worksheet names, empty = all worksheets) • Must contain at least one document • Processed in SortPosition order (lower numbers first) • Conflict Resolution: Duplicate worksheet names automatically renamed with suffix (e.g., Sheet1, Sheet1_1, Sheet1_2)	[{"FileContent":"...", "Filename":"jan.xlsx", "SortPosition":1}]
Output Format	Enum	Output File Format • XLSX (default) - Excel 2007+ (.xlsx extension) • XLS - Excel 97-2003 (.xls extension) • PDF - PDF document (.pdf extension) - read-only distribution, archival • CSV - Comma-separated values (.csv extension) - Note: CSV output only includes first worksheet • Default: XLSX	XLSX
Output File Name	String	Output Filename (without extension) • Name for merged output file • Extension automatically added based on OutputFormat • Default: "merged" if empty • Do not include file extension	Q1_Report

Output

The PDF4me Merge Excel Files action returns comprehensive output data for seamless Power Automate flow integration:

Table

Table View

Response data in a structured table format:

Parameter	Type	Description
document	Base64	Merged Excel file (or PDF/CSV based on OutputFormat)
FileName	String	Generated filename with appropriate extension
Success	Boolean	true if operation successful, false if failed
Error Message	String	Error description (null if successful)
Errors	Array	List of detailed error information (empty array if successful)

JSON

JSON Response Format

The raw JSON response from the action:

{
  "document": "[Base64 Merged File Content]",
  "FileName": "Q1_Report.xlsx",
  "Success": true,
  "ErrorMessage": null,
  "Errors": []
}

Filename Generation Examples:

OutputFileName = "Report", OutputFormat = XLSX
→ FileName = "Report.xlsx"

OutputFileName = "", OutputFormat = PDF
→ FileName = "merged.pdf"

OutputFileName = "Combined", OutputFormat = XLS
→ FileName = "Combined.xls"

Error Response Example:

{
  "document": null,
  "FileName": null,
  "Success": false,
  "ErrorMessage": "File content is empty for file january.xlsx",
  "Errors": [
    {
      "Code": "FILE_CONTENT_ERROR",
      "Message": "File content is empty for file january.xlsx"
    }
  ]
}

Flow Integration

Power Automate Flow Usage

Use merged Excel files in subsequent actions:

• Report Consolidation: Combine monthly, quarterly, or annual reports into one file
• Data Aggregation: Merge department data into consolidated company view
• Multi-Source Integration: Combine data from different systems/sources
• Archive Creation: Merge related Excel files into single archive file
• Email Distribution: Send single consolidated file instead of multiple attachments
• SharePoint Publishing: Upload merged workbook to document libraries

Common Error Messages

Understanding and troubleshooting errors helps ensure smooth Excel file merging workflows:

Error Message	Cause	Solution
"Request is empty"	Request object is null	Provide valid request object
"File content is empty for file {filename}"	FileContent is null or empty string	Provide valid Base64-encoded file content
"No matching worksheets found in {filename}"	WorksheetsToMerge specified but none exist in file	Verify worksheet names match exactly (case-sensitive)
"Error processing file {filename}: Invalid Base64 string"	FileContent is not valid Base64	Encode file content as Base64 before sending
"Error processing file {filename}: File is corrupted"	Source Excel file is corrupted or invalid	Provide valid, non-corrupted Excel files
"Error processing file {filename}: Unsupported format"	File format not supported	Use .xlsx or .xls format files only

Workflow Examples

The PDF4me Merge Excel Files action in Power Automate provides comprehensive workflow templates designed for real-world business scenarios:

Monthly Consolidation

Automated Monthly Report Consolidation Workflow

Transform your reporting with automated merging of monthly department reports:

Complete Workflow Steps:

1. Trigger: Scheduled trigger on 5th of month at 9 AM
2. Get Department Reports: Retrieve Excel files from SharePoint folder (Sales, Marketing, Finance)
3. Build Documents Array: Create MergeDocument objects for each file
4. Set Sort Order: SortPosition = 1 (Sales), 2 (Marketing), 3 (Finance)
5. Select Worksheets: WorksheetsToMerge = ["Summary", "Data"] for each
6. Merge Files: Combine into single workbook
7. Set Output: OutputFileName = "Monthly_Report_[Month]", OutputFormat = XLSX
8. Email Leadership: Send consolidated report to executive team
9. Archive: Store merged report in monthly reports library

Business Benefits:

• Consolidates 3 department reports monthly automatically
• Eliminates manual file merging saving 1 hour per month
• Ensures consistent report structure with worksheet selection
• Provides single source of truth for monthly performance

Create Flow

Regional Aggregation

Automated Regional Sales Data Aggregation Workflow

Streamline your sales reporting with automated regional file merging:

Complete Workflow Steps:

1. Trigger: Weekly scheduled trigger on Monday at 8 AM
2. Get Regional Files: Retrieve sales Excel files from regions (North, South, East, West)
3. Loop Through Files: Apply to each for regional files
4. Prepare Documents: Create MergeDocument with SortPosition by region order
5. Merge All Worksheets: Leave WorksheetsToMerge empty to include all
6. Set Parameters: OutputFileName = "Weekly_Sales_All_Regions"
7. Convert to PDF: OutputFormat = PDF for distribution
8. Email Sales Team: Distribute consolidated PDF to sales leadership
9. SharePoint Archive: Upload to weekly reports folder

Business Benefits:

• Aggregates 4 regional sales files weekly automatically
• Provides complete sales visibility across all regions
• Generates PDF format for easy distribution and review
• Reduces manual consolidation time by 90%

Create Flow

Quarterly Archive

Automated Quarterly Data Archive Creation Workflow

Optimize your data archival with automated quarterly file merging:

Complete Workflow Steps:

1. Trigger: Scheduled quarterly on last day of quarter at 11 PM
2. Get Monthly Files: Retrieve 3 monthly Excel files from quarter folder
3. Build Merge List: Create Documents array with chronological sort
4. Set Sort: SortPosition = 1 (Month 1), 2 (Month 2), 3 (Month 3)
5. Selective Merge: WorksheetsToMerge = ["Results", "Summary"] per file
6. Merge Files: Combine into quarterly archive
7. Set Output: OutputFileName = "Q[#]_[Year]_Archive", OutputFormat = XLSX
8. Compress Archive: Optional - zip merged file for storage
9. Store Archive: Upload to long-term archive SharePoint library
10. Send Summary: Email archive confirmation to data steward

Business Benefits:

• Archives 3 months of data quarterly automatically
• Creates comprehensive quarterly records from monthly files
• Reduces archival file count with consolidated storage
• Saves 25% storage space with selective worksheet merging

Create Flow

Multi-Source Integration

Automated Multi-System Data Integration Workflow

Enhance your data integration with automated merging from multiple sources:

Complete Workflow Steps:

1. Trigger: Data export completed in all source systems
2. Get CRM Export: Retrieve Excel export from Dynamics 365
3. Get ERP Export: Retrieve Excel export from SAP/Oracle
4. Get Finance Export: Retrieve Excel export from accounting system
5. Build Documents: Create MergeDocument objects for each source
6. Set System Order: SortPosition by system priority (CRM=1, ERP=2, Finance=3)
7. Select Data Sheets: WorksheetsToMerge = data sheets only (exclude metadata)
8. Merge All Sources: Combine into integrated workbook
9. Set Output: OutputFileName = "Integrated_Data_[Date]"
10. Load to Database: Use merged file for SQL bulk insert

Business Benefits:

• Integrates 3 system exports daily automatically
• Provides unified view of cross-system data
• Eliminates manual file consolidation from multiple sources
• Enables seamless multi-system reporting and analysis

Create Flow

Industry Use Cases & Applications

Finance & Accounting

Finance & Accounting Use Cases

• Monthly Close: Merge departmental financial reports into consolidated monthly close
• GL Consolidation: Combine general ledger exports from multiple entities
• Budget Aggregation: Merge departmental budgets into company-wide budget
• Audit Preparation: Consolidate financial records from multiple sources

Sales & Marketing

Sales & Marketing Use Cases

• Regional Reports: Merge regional sales data into national sales report
• Campaign Consolidation: Combine campaign results from different channels
• Pipeline Aggregation: Merge opportunity pipelines from multiple sales teams
• Lead Integration: Consolidate lead lists from various marketing sources

Human Resources

Human Resources Use Cases

• Payroll Consolidation: Merge payroll data from multiple locations/divisions
• Benefits Enrollment: Combine enrollment data from different benefit providers
• Headcount Reporting: Aggregate employee data across departments
• Training Records: Merge training completion data from multiple programs

Operations & Supply Chain

Operations & Supply Chain Use Cases

• Inventory Aggregation: Merge inventory data from multiple warehouses
• Production Reports: Consolidate production data from different facilities
• Supplier Data: Combine supplier performance data from multiple sources
• Quality Metrics: Merge quality control data across production lines

IT & System Administration

IT & System Administration Use Cases

• System Reports: Merge monitoring data from multiple servers/systems
• Asset Management: Consolidate IT asset data from different locations
• License Tracking: Combine software license data from various departments
• Security Logs: Merge security incident data from multiple sources

Education & Research

Education & Research Use Cases

• Grade Consolidation: Merge grade books from multiple instructors/courses
• Research Data: Combine experimental data from different trials/studies
• Enrollment Reports: Aggregate enrollment data across programs
• Survey Results: Merge survey responses from multiple periods

Get Help

PDF4me ConnectGet your API KeyPower Automate Documentation