Get Sheet Name From Excel

[fjlorenzo]

Hi, can someone help me? How can I get the sheet name from Excel file and put the sheet name on the formula tool. Below is my sample workflow. Thank you for your help.

[fjlorenzo]

Hi Doug,

1st Formula Tool 

[FullPath] + "|<List of Sheet Names>"

2nd Formula Tool

replace([FileName], "<List of Sheet Names>", [Sheet Names])

[fjlorenzo]

Doug,

My goal is to select the second worksheet from the latest file every time I run the workflow.

Thanks,

Florence

[rodalferreira]

Hey @fjlorenzo 

Have a look in my solution. I think you have to cross join  your file with the list of sheet names.

You are doing something more complex so I hope it helps.

Cheers,

Rod

[AkimasaKajitani]

If there are an error message that is "different schema", you can't use "Dynamic Input" tool.

You have to use Batch Macro instead of it.

Batch macro sample is the below.

Please refer attached file(I think that it will be work at your case.)

[fjlorenzo]

Hi Rodal,

I think this solution will work if my sheet names don't change. The second work sheet name changes every day depending on what day is it. My second work sheet name is something like this "on demand 11-17-20". Then tomorrow it would be "on demand 11-18-20". Need to know how can I do this? 

Thanks,

Florence

[fjlorenzo]

Thank you for the suggestion.

[marcusblackhill]

Hey @fjlorenzo !

Is like @AkimasaKajitani , you need to create a batch macro for that. If you try to input many files, all need to have the same schema otherwise you will get error.

That said, you can create one workflow to get all sheets of all files in one folder, you can follow the steps:

1. Create workflow to get all files of the folder using directory folder
2. That list of files need to enter in your first batch macro
3. So, the first batch macro, here you will use a input tool configured to get all sheet names;
4. The list of sheet names will enter in the second batch macro;
5. To the second batch macro, you will put a simple input tool, but will be good to configure that tool to have filename as well, that way you can know the source of each data in the final output

After you do those steps, your app will get all files and for each file will get the list of all sheets and finally will input each sheet of each file of the entire selected folder.

Hope that help you!

[fjlorenzo]

Hi @AkimasaKajitani,

Thank you for your suggestion. I would appreciate if you could provide a sample workflow because I'm not familiar on using batch macro.

Regards,

Florence

[fjlorenzo]

Hi Marcus,

I would appreciate if you could provide a sample workflow because I'm not familiar on using macros.

Thank you,

Florence

[marcusblackhill]

Hey @fjlorenzo !

Sure, actually is a complex macro. That package have the workflow itself, here you have the directory tool, you can define the folder you want to look and he will get the name of all excel files inside the folder.

That list go to the first batch macro. That macro will get the list of sheet names for each file in the first workflow, and that list will go to the second batch macro, where will input data of each sheet, one by one.

So in the final you will have all sheets of all files in the first batch macro output , with the filenames as well to you know the source of each record.

Hope that help!