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

[Govind_Dileep_G]

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. 

[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,

M.

[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.

[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?

[Jean-Balteryx]

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

[HomesickSurfer · Accepted solution]

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