HELP!! HOW TO READ 20 input sheets from a DYNAMIC input EXCEL file -

I do know how to read a file and read 20 sheets from an excel without having to manually read it 20 times. (I can create a input and do dynamic input and macro to read the sheets.)

but how to create a dynamic file Browse and read 20 sheets in the excel file.

example v1 to v20 are the sheet names, the sheet names in the excel are fixed.

good thing is input format for the 20 sheets is same, see attached example file attached which has 5 tabs. (I cannot attach the actual file)

inputfile.xlsx

Input

Help

Dynamic Processing

Accepted answers

ntakeda

This configuration works.
Please check the image.

1. Input tool

2. Dynamic input tool

All comments

Qiu

@amruta
What you mean by "a dynamic file Browse "?
What is the difference between it and you have achieved as below?

(I can create a input and do dynamic input and macro to read the sheets.)
but how to create a dynamic file Browse and read 20 sheets in the excel file.

KGT

So, you have a macro that gets a list of all the sheet names? just construct the list of qualified inputs, filename.xlsx|||Sheetname, and feed that to the Dynamic Input. I'm not sure what solution you have in your first statement, but I can't see how this is different. Did you already have the sheet names in that example and don't in this question?

If I've got the right of that, then try the attached macro to get the sheeetnames, then a formula to construct the input for the DynamicInput.

Batch.ListAll_XLSX_SheetNames.yxmc

amruta

if I have a input file, always same file name and from same folder, then I can read the tabs.

But if I do a file browse tool (instead of a fixed file name and file path) then I am not able to retain the value of file name that is being browsed so cant take it further to read all the tabs

KGT

Your File Browse will connect to an Input tool, right? You just need to configure that Input tool to Output Filename. If it doesn't connect to an input tool, then the solution may be a bit more bespoke depending on your situation

ntakeda

This configuration works.
Please check the image.

1. Input tool

2. Dynamic input tool

apathetichell

@amruta--- this is an incredibly common use case in Alteryx. Instead of using capital letters to clarify that this dynamic --- search community for how to build a batch macro to accomplish what you need.

amruta

Thank you for this.

amruta

@ntakeda Thank you, that configuration almost worked.

Earlier I was not reading doing <list of Sheet names>, I was only getting the file name, and then adding sheet names. (which was causing the issue)

But your way , it worked, I had the file with all <List of sheet names> and then use a join to filter down only select the 20 sheets of interest and then use the macro to read. (could not use dynamic input due to mismatch data type access the sheet names)

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703