Merge Sorted Excel Files into One with Custom Sheet Names

[akvsachin]

Input: 3 excel files named Rahul Expenses_11-25-2024.xlsx, Amy Expenses_11-25-2023.xlsx & Karen Expenses_11-25-2024.xlsx, all with 1 sheet only randomly named. Also, to be sorted with only column name.

Output: 1 excel file with 3 sheets named as Rahul Expenses, Amy Expenses & Karen Expenses.

[binuacs]

@akvsachin batch macro would be an option for your use-case. sample attached

[akvsachin]

Hi @binuacs 

Can we do it without batch macro, as I would be creating a analytic app later. and I can't use directory as I'll be using FileBrowse, Action and Input Data tools later.

[binuacs]

@akvsachin batch macro is the best option for you because your sheet names are dynamic, you can make it analytical app and connect the Folder Browse tool to the directory tool with action tool

[akvsachin]

The Prime Objectives of my Task

Create an Analytic App:Consolidate up to 10 input Excel files into a single output Excel file with multiple sheets, each having custom sheet names.

Input Flexibility:Support 1-10 input files & Reporting Period, with optional parameters for Group By Columns, Grand Totals, and Sub-Totals.

Naming Convention:Ensure the input file names match the reporting period in the output.

Handle Optional Inputs:The app should run with 1-10 files, allowing null inputs for any unprovided files. Use Block Until Done to process all selected files.

Output Formatting:Apply consistent formatting: bold headers, grey background/white font for totals, and fit columns to width. Include an empty row before the grand total/subtotal.

Sheet Names:Use the file names from the inputs as sheet names in the output Excel.

Dynamic Reporting Period: Derive the reporting period from the file names (MM/DD/YYYY), based on the current or prior business day.

I have been researching each step one by one and wasn't looking to use macros. If you're suggesting that using macros is the best way, I may need to reconsider my approach of using FileBrowse, ActionTool, and Input Data.

However, if you can help me find an alternative method, that would be greatly appreciated.

[flying008]

Hi, @akvsachin 

As your said, Does all sheets in your .xlsx file have the same schema?

[akvsachin]

hi @flying008 ,

No, They differ.
There are going to be files like 1 has 38 fields, 2 also has 38 fields but names differ, 3 has 24 fields and so on.

[flying008]

Hi, @akvsachin 

So, like @binuacs said, maybe batch macro or powershell script as your better way.

[akvsachin]

So @flying008 and @binuacs ,

I am using macros to extract sheet names, and the process includes sorting the data as part of the workflow. The steps are as follows: retrieve the data, sort it, extract the sheet names, and then print them accordingly.

Additionally, users will have the option to specify how the data should be sorted for different input files. This is why I am considering the FileBrowse method, as it allows users to sort the data as needed before processing it further.

However, I’m wondering if this approach might become overly complex. While it is working with the data I am testing currently, there is a risk that it might fail once implemented in the development environment.