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

[binuacs]

@KamenRider One way of doing this with the help of batch macro. You can also see the detailed explanation of how this can be achieved with the batch macro in the below post

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/t...

[KamenRider]

Hi @binuacs 

Sorry for late reply since I am still figuring the workflow you have sent. I am not much well verse when it comes to macro and I would like to ask again for your assistance unto how I am going to insert my data.

With the workflow below, there is a question mark tool. Can you please let me know how I can fix  this?

 

 There is an input file showing B excel file. May I know what data can I use to replace this? I am not sure if this is related to my data.

Another file which is confusing to me, what file can I use to change the content in the input file? Please let me understand how I am going to group these files to be able to run my workflow? I read the link you have sent but I am not  able to follow. Perhaps can you please give me another detailed explanation so I can learn.

Thanks and hoping to hear from you soon.

Kamen.

[binuacs]

@KamenRider attaching the workflow again. please let me know if you face the same issues

[grazitti_sapna]

Hello @KamenRider, give this a try.

Step 1: I created a macro. In the below macro (workflow) I am fetching the sheet names then using a dynamic input tool I am reading the sheets.

Step 2: Used this macro in the first image workflow. Also, you need to add your files to the directory tool

I hope this helps!

Thanks!

[KamenRider]

Hi @grazitti_sapna 

First, thanks for sharing me your ideas. My problem is that whenever I open your workflow, it gives me a symbol question mark on the macro tool. 

Please advised how can I fixed this since whenever I delete this and right click to insert your macro, it also gives me "invalid macro".

Another question I have is that what is the purpose of "Book 1 (2)" inside the macro tool? Should I link it to my input data or should I create another folder and copy paste my input data?

I do hope you would provide me a step by step guide and clarification since I am very much new to macro.

Thanks and looking forward.

Kamen

[KamenRider]

Hi @binuacs 

I am encountering same problems. My problem maybe is how to insert the macro since like my response to @grazitti_sapna , it gives me a symbol question mark whenever I open your workflow. Tried removing the symbol question mark and insert the macro but it gives me a message "invalid macro".

Plus would like to ask if there is a need to change the link of input file in the GetSheets_Macro with my data or just ignored it? Same question with the Get_Sheets_Details with "B.xlsx". Is this a dummy or this something I need to change with my data?

Hoping for more clarifications.

Thanks so much for your patience.

Kamen

[binuacs]

@KamenRider No need to change any thing from the macro

to resolve your issue

1. Save both the macro

2. in your workflow delete the ? tools and right click - insert - macro - select the respective macro 

3. update the macro input file with the field 'full path'

once that is done update the macro input like below

[grazitti_sapna]

Hello @KamenRider, the input data tool inside the macro is just for template as the filepath will be provided outside the macro using control parameter therefore the input file specified inside the macro will get replaced eventually.

Outside the macro I suggest you to create a folder and then call it using directory tool as the solution I provided will be for multiple excel files at same time.
Also, I am not sure why is it saying invalid macro in your case, try opening the macro workflow in alteryx first save it  as a new macro then try to insert it in the workflow.

I hope this helps!

Thanks!

[KamenRider]

Hi @grazitti_sapna 

I was able to do your direction and able to run the workflow, however the problem is it does not read the files in my directory as output but rather it read what is on the template in the macro. Other than this it produce two sheets and along with it with duplicates. See screenshot below.

Is there anything we could do to correct the output file? The workflow should read multiple files in the directory with no duplicates under one sheet file.

Hoping for your response. Thank you for your patience.

Kamen