Merging excel files based on File Name

[Karam_Abulawii]

Hello
I’m Facing a problem when i merge excel files based on there names
I Want the output 1 Excel Sheet For Each Company
but i’m Getting a Merged Excel File For Each Of my Files
i want the process to as dynamic as Possible But i Can’t Do it
Please Find the Attached Zipped File For the Process To help
Xocialive Task.zip (411.3 KB)
the Data Folder Contains The Files After Running the Process
Thanks in advance

[Hiba_B (Hiba Beldi)]

Hi @Karam_Abulawii !

You forgot one loop to do so.
You need:

1. to have a way to only collect the names of the companies (= have for instance a list that contains Com 1 and Com 2)
2. then loop in the list that contains company names, and collect all files that start with the company name so the name that is before the sign “_” then merge
3. Once all the files of the company are read and merged, we can do the calculus and clear all datatables

Here is a suggestion: Main.xaml (50.9 KB)

Let us know if it does not work as expected !

[Karam_Abulawii]

Thanks a lot
But the Attached File has Some missing Activities
Can you Please Re-Upload it

[Hiba_B (Hiba Beldi)]

I am using Studio 21.4, either you are using a lower version, or you should update your packages
Do you mind update your Studio version and/or packages ?

[Karam_Abulawii]

Can you please just take a screenshot of all read and write range activities ?

[Hiba_B (Hiba Beldi)]

Well there are a lot of screenshots… here it is:

The request is the following:

Directory.GetFiles(FilesPath, "*.xlsx").Select(Function(j) path.GetFileName(j).ToString.Split(CChar("_"))(0)).distinct().tolist

Don’t forget to change the type of getFiles to list < of string > and note that the build has changed its place. Don’t forget to always initialize your lists.

The request:

Directory.GetFiles(FilesPath, company +"*.xlsx").where(Function(j) not j.Contains("Filtered")).ToList

Here is a new variable: list_of_files_of_com_A, also a list< of string >

Note that the merge is not in the same sequence as what you did, then:

And the rest is untouched (note that the first write range need to have the same name as the 2nd one, and that I changed a bit the name of the final file)