Help needed- Adding sheet name with file path from directory tool.

Hi all,

I have 24 files and each of them have 5 sheets in them. I used the directory tool to get the file path of all the excel files. Now, to do further processes i need to add sheet names with this directory tool. I used a formula tool to add file path + sheet names. since, this only has 5 sheets it was ok for me to add 5 formula tool, one for each sheet. I was thinking whether there is a possibility of using an iterative macro to automate this process. I am attaching a screenshot of what I did here. if we can make some kind of iterative macros it would be really helpful to me. I am new to Alteryx and am not that experienced in macros. I have circled in red the part where i want to do some sort of iterative macros. So, please help me out.

Screenshot 2021-08-17 163919.png

Workflow

Macros

Accepted answers

HomesickSurfer

Hi @Govind_Dileep_G

Attach a browse tool or anything you require downstream after the macro in order for it to loop. Let me know.

If it meets your requirements, please share, like and/or Accept as Solution. Thx.

All comments

mceleavey

Hi @Govind_Dileep_G ,

This is best achieved using a simple batch macro. Wrap your output into a macro, in your workflow prior to the macro build you dynamic fullpath names (including the |||Sheetname), then group by this using the sum tool. Use this field as your control parameter value and use that to overwrite the output value of the fullpath.

This will then loop through and output each sheet into the workbook as a batch process.

Hope this hleps,

Govind_Dileep_G

Thankyou @mceleavey for the quick response but as i said i am new to alteryx and i didn't quite understand your idea.

HomesickSurfer

Hi @Govind_Dileep_G

Here is my approach.

Example workflow with sample files and macro package attached. Export to C:\temp to test.

There are 5 files with 5 sheets each...25 sheets total.

The macro will accept input FullPath, list out sheets, then modify the FullPath to include the sheet names.

List sheetnames.yxzp

Govind_Dileep_G

Thankyou @HomesickSurfer . I tried your solution but i am only getting the first 5 sheets from the first file. i have 24 excel files with 5 sheets in each of them so i should get 120 records as the output. any idea why this happened?

Screenshot 2021-08-17 181930.png

Jean-Balteryx

Add another tool after your batch macro ! This is something needed in order to get all your results.

HomesickSurfer

Hi @Govind_Dileep_G

Attach a browse tool or anything you require downstream after the macro in order for it to loop. Let me know.

If it meets your requirements, please share, like and/or Accept as Solution. Thx.

Govind_Dileep_G

Thanks alot @HomesickSurfer . It worked

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703