Combine Excel and Rename Sheets

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

@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

ExcelSheets_Combined_26_07.yxzp

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?

Tool with Question Mark.JPG

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.

Get Sheets.JPG

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.

Get Sheets Macro.JPG

Thanks and hoping to hear from you soon.

Kamen.

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

ExcelSheets_Combined_01_08.yxzp

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_workflow.yxzp

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

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

@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

HI @binu_acs

Thank you for your guidance. I was able to run the workflow. Just to confirm, the first macro is Get Sheet Macro wherein I will select "Full Path (V String) and second one is Get Sheets Details and will select "Full Path (V String) also.

The problem is that it read only the first date in my directory plus it produced two sheets in one file. See screenshots below.

Two Sheets Produce.JPG

Two Sheets Produce2.JPG

Is there's something we could do to correct the workflow. The workflow should read the files in the directory under one sheet. No duplicates.

Hoping for your continued assistance.

Thanks

Kamen

@KamenRider The macro is designed to read all the sheets from the given excel files and put in each sheet one excel file. Do you want to ready only from the first sheets of each excel file and put it all in one excel file with different sheets?

Thanks for the response. I would like to request for Sheet1 to combine all the contents of the excel files. I am not sure why the workflow does not get all the files. It show only date 07-19 plus with duplicates. I have change the connection of the file pointing to the directory of my input files in the Get Sheets Macro because the workflow won't run because of the error stating the file does not exists.

Please advise.

Kamen

@KamenRider I updated the workflow to combine all the data into one sheet with the name sheet1

ExcelSheets_Combined_updated_0802.yxzp

nitin_l

Just putting this workflow here, if anyone wants to merge few excel files into one with a dynamic sheet name.

Merge_Two_Excels.yxmd

Hi @nitin_l

Thank you for sharing your ideas however, my input files does not only limit to 2. It may have two or more reports to merge. Please feel free to still share your ideas.

The results in still the same except that it shows only one sheet which is correct but the content still looks like this. I've change the path or connection of the input file in Get_Sheets_Macro to ..//BSA Input/BSA 01.xls. The workflow does not still read the 4 files but read only the first file BSA 01.

Two Sheets Produce.JPG

Can you please use the 3 excels below. It shows the correct schema or columns. I'm not sure if this is the reason why the data is screwed. I've also attached the sample out. On this first column I've added a date of the report which I take from the sheet name.

Looking forwards. Thanks so much.

Kamen

BSA Report - 07-17-2022.xlsx

BSA Report - 07-16-2022.xlsx

BSA Report - 07-16-2022 - Routes.xlsx

Sample Output.xlsx

@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

ExcelSheets_Combined_updated_0802.yxzp

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

@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

ExcelSheets_Combined_updated_0802.yxzp

HI @binu_acs

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