Maintain File name and sheet name using macro

[SH_94]

Hi Community,

I would like to ask about the excel file name and multiple sheet /tab name of the excel files (xlsx format) after using the macro.

Previously, i will choose the output file name as File Name when i run the single excel file with single tab/sheet. This is because i want to the data to be easy differentiate with the each file name appearing in the Alteryx. However, now i would like to use the batch macro to run the multiple excel file including the subfolder and together with multiple tabs within the excel.

May i know if we can still maintain the excel file name as our output file name when using batch macro?

In addition, may i know if we can extract the name of multiple sheet/tab and it will appear in the alteryx when using macro?

I have the batch macro but i not sure how to configure it in order to achieve the above result. Could anyone share with me on how to edit the macro so that it can read the above criteria?

Many thanks in advance and happy weekend

[afv2688]

Hello  @SH_94 ,

With this macro you will be able to read all sheets from different xlsx files:

It will read all the sheet names and then add all the data together. The filename can be then extracted from the filename field as well as the sheet name.

Regards

[SH_94]

Hi @afv2688 ,

Thanks a lot for the workflow shared.

May i know how can i fix the error below as i not sure what field to select in this case. When i drop down the options, it show the data file header column name which i think it is not the right field to choose.

Secondly,may i know if there is any formula that we can split the file name and sheet name ?

Thank you

[afv2688]

Hello @SH_94 ,

I have added the filename and sheet to the output with a regex tool.

You don't need to specify anything within the macro.

If you want to edit them yourslef you would also need to replace the txt input and write manually a filename there already is.

Regards

[SH_94]

Hi @afv2688 ,

Thanks a lot for your explanation in the previous post. 

I had run the workflow and noted that it only captures one of the subfolder. I have three big subfolder and it only capture one of the subfolder.

Firstly, i able to see the workflow below which capture the three source file subfolder Area: asia, europe and african.

Following with that, i was unable to see the workflow that capture three area when i click the macro button . It only capture one of the subfolder: Asia as per screenshot below. May i know in this case, is it i choose wrong field when fill in the macro ?

After that ,i went to click the macro and the result as per screenshot below. If you notice that , there was error shown on the formula tools. However, it is disappear when i run it on the macro. It appear again the error after i run the original workflow (refer to the first workflow screenshot )

May i know in which area that i had made error based on the screenshot above?

Many thanks for your explanation and help again.

[SH_94]

Hi @afv2688 ,

Thank you for the macro workflow provided. I have three queries which would like to clarify with you as below after analysis the workflow:

1.  Could you briefly explain what is the role / function for the icon circled in the below?

2. Could you briefly explain what is the role / function that the icon circled in the below playing (when i open the first macro)?

3. What is the difference of roles between the first macro and second macro that playing in this case?

Thank you again for your help.

[afv2688]

Hello @SH_94 ,

Regarding your questions:

1. The role for the macro is to provide all the data sheets on an excel file, independent of the file format or the sheetnames.
2. The functions here will read all he sheet names on he files and pass them to the next macro
3. Here all the data will be extracted and merged together based on column names

Regards