I need to clean and combine a few thousand excel files using a batch macro

[fisai]

Hi guys,

The situation is, I have a few thousand invoices in Excel.xlsm that i need to normalize and the union the data so it's all in tabular form.

I had an attempt, but the output is nothing like what I expected, the rows are duplicate many time, and I don't really understand what is happening..

Can someone help? I have attached my flow and 3 example files (here they are xlsx, but this qould be a small change to xlsm) and the macro I made.

Thanks a lot!

Filip

[Raj]

Share the Zip of your workflow.
Although, i can see some problems in this.
you should have created a dynamic way to select all the files from the directory and using the file path use dynamic input tool in Macro to bring all the Columns and then arrange them according to requirement and this will produce the required.

[fisai]

Hi Raj, If i knew how, i would haha

I basically wanna read those 3 files with the Input tool using a *.xlsx, then combine them with the macro.

Everything i used is there

[Raj · Accepted solution]

find attached flow.hope you want to see the 1043 records.

[fisai]

Hi Raj,

thanks a lot for your help, really appreciate it, I'll "Accept as Solution", but I think it still needs some tweaks!

To give you a bit of backgorund:

My files are stored in folders and sub folders. Will these be a problem? The macro input tool doesn't seem to read the different files a have.

When i switch to my files, the macro give me an error: Error: RAM LOOP MACRO_Autosave (5): Record #1: Tool #1: No sheet specified, you must specify a sheet.

Why can this be? My files are in many directories and sub directories and the extension is .xlsm. Made the change and checked the box, still same error.

And if i make some change, i just get the same file duplicated by the number of files i allow the folder tool to pick up.

Best regards,

Filip

[Raj · Accepted solution]

First thing first it is never a problem if you have your files in sub-folders
You need to Tweek the flow accordingly and to specify the sheet open the macro and select any actual sheet in Macro and save it as it is taking reference from my directory.
this shall help.