Dynamic Input No Sheet specified error ... from Directory, select, sort and sample

[Dark5tar]

I have searched far and wide on the community and though i can see my issue has been experienced, I seem not to be able to understand the solution or it does not work for me, maybe because context is slightly different.

There is a xlsx file I want to extract from a Shared Folder.  The Shared folder has a new file added every day and I wish to pick-up only the latest.  I have used the directory file, to sort and sample the the first, i.e. latest file by name, in the Shared Folder.  I then connect the Dynamic Input too, which I have pointed to the "template" file, which is basically one of the older files in the folder; so are exactly the same in format, name, convention etc.  The files have 3 worksheets within them, I'm trying to pick out just one of them, which I have specified in the Dynamic Input tool; "Select s Sheet" and from drop down, the sheet I am striving for.

It results in the "No sheet specified, you must specify a sheet" error.

Would appreciate any assistance in trying to understand this error and how to resolve ... I suspect its related to what I have read about needing a formula to add sheet name etc but either i am not understanding the solution or missing the detail on the workflow to make it work or it is not what i need to do.

[mceleavey]

Hi @Dark5tar ,

I've attached a workflow which does this for you.

The first tool reads in the tab names from the spreadsheet(s), this then creates the full path including the sheet names, and dynamically loads them all in for you.

Hope this helps,

M.

[Dark5tar]

thank-you, but not sure i follow your tool set-up or it maybe not come through  to me right...I see the attached - how should i be attaching these?

[danilang · Accepted solution]

Hi @Dark5tar 

The Dynamic Input has 2 logical modes to it.  The first one is the one you use when developing the workflow.  It shows standard input dialog where you specify the file and sheet name.  The second mode occur when you run the workflow.   The file and sheet names that you defined in dev mode are replaced by the values coming in from the connected tool.  In your current setup, you only have the filename specified.  To fix this add a formula tool after the Sample tool that adds a sheet name to the filename like this. 

[FullPath]+"|||`sheet name`"

Note that the quotes around sheet name are the backward facing single quotes. On an American keyboard layout, it's the quote mark on the tilde "~" key.

Use this new field in your Dynamic Input and select "Change Entire File Path" as the action

Dan

[Dark5tar]

thank-you very much.  I think I needed the explain to understand what i was doing to make it work -much appreciated!