Include Excel Sheet name in output dataset using Dynamic Input tool

[MaverickVZ]

I may be overlooking something obvious but this is the first time I'm using the Dynamic input tool and I need to add the name of the Excel Worksheet (Table) to the dataset. My Excel Workbook has 43 tabs each one named by month. "Dec-14", "Jan-15", "Feb-15" etc. Using the Dynamic Input tool I am able to Union all the data into one output but I have no way of telling which sheet it came from for a summary view. I need to bring the Worksheet Name into the data so I can summarize it by month. I don't want to manually add it to each sheet and then import it. Any help would be greatly appreciated.

Thanks,

Pete

[PoojaSingh88]

@MichalM 

I need to read a directory with multipl eexcelsheets namesd ad per dates.I am trying to pick up and read all sheets within the excel with todays date in its name.

Example : I have 3 files named as Region_20190618 ,Region_20190619 ,Region_20190620 with multiple sheets.

I need to read todays excel all sheets.

The current way it reads my sheetname for the current date file instead of readin ghte file sheet contents.

[MichalM]

Ok, makes sense.

You'd need to split the process into multiple steps.

Firstly, you will use the directory tool to load in details of all the files within the directory and use a filter to select the one with today's date.

Secondly, you will need to build out the full path to the file including the sheet names. If these are the same all the time, you can hard-code the logic however it'd be safer to use the approach you did

• List all the sheets within the relevant work book
• Also Full path to the file as part of the process

• And then use another formula tool to replace the reference to <List of Sheet Names> in the full path with the individual sheet names

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

Once ready, you can use another dynamic input to read each of the sheets within today's workbook.