Maintain File name and sheet name using macro

Question

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 · Answer

Hello @SH_94 ,

Regarding your questions:

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

Regards

SH_94 · Answer

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.

SH_94 · Answer

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.