Combine Excel and Rename Sheets

[KamenRider]

Hi and Good day,

I would like to ask for your knowledge and assistance regarding the following.

I have multiple excel files (.xlsx) and would like to merge/combine into one. Excel files has different sheet names and I would like to name it as Sheet1 with date today when combined.

Attaching two excel files for your reference.

Looking forward for your help. Thanks.

Kamen

[KamenRider]

HI @grazitti_sapna 

For the location of the files, I have an Input folder and an Output folder to separate them in my shared drive. Do you think maybe because of the macro the makes the results screwed?

thanks,

Kamen

[binuacs]

@KamenRider I am getting the correct result except for the Report Date which is not any of the input files. I have included 3 od your input file and the output file generated from the workflow

Can you also check your directory settings? The below-highlighted fields need to be updated according to your input file path

[KamenRider]

Hi @grazitti_sapna 

Do I need to change this? Because what comes out is the template contents which is being repeated 5x and not the merge output in the input folder.

 

Thanks,

Kamen

[KamenRider]

Hi @binuacs 

I can't understand what went wrong. Maybe because of the macro since I change the connection to link to the input folder? When I deleted the file I link with, the workflow send me an error looking for the file. Am I correct to give the path in the macro file? Please send screenshots.

Meanwhile, my directory path seems correct.

Please advise.

Thanks

Kamen

[binuacs]

@KamenRider  I updated the macro with one of your input file

The macro (get sheet name) has the setting like below. 

The second macro has the below settings

[grazitti_sapna]

@KamenRider, yes you need to change the output part that you highlighted this is the reason why your records are being duplicated.

[KamenRider]

HI @binuacs 

for your recent workflow provided to me, I did not change anything except for the path of the directory in the input file but the result or output provided is the sample I've given to you instead of the inputs files in my shared drive. Please advised what particular tool I am going to update especially in the two macro you have provided. It makes me confuse in what particular instance and where do I need to change the path of my shared drive or either point it the input folder or output folder.

Hope to hear for your advised.

Thanks

Kamen

[KamenRider]

Hi @grazitti_sapna 

Like you said, I have entered the path pointing to my input file but still it is duplicating. It only read the first file but other are not so they are not merging. Can you please give me a step by step guide to do this. What to enter in the path either pointing to input folder or output folder. How do I get exactly all merging to one another? I apologize for being hard to learn since I am really confuse. I tried analyzing it and I can't get the answer.

Hoping for your response.

Thanks,

Kamen

[binuacs]

@KamenRider 

in the first macro update like below

second macro update like below

also please make sure your macro action tool configuration is like below

[grazitti_sapna]

@KamenRider, in my workflow I am not using an input tool I use a directory tool.

1. Select the directory tool as the input tool and enter the directory where you are keeping your files book1 and book2.

2. After reading data from the macro you need to create a path using a formula tool as I have below.

3. Configuration for the output tool.

Reattaching the workflow.

I hope this helps!