Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Dynamic Input not selecting specific excel sheet

SamStig
6 - Meteoroid

I have searched many topics and I haven't found a tip to solve this yet! Though I am sure I am not the first person to have the issue. 

I am setting up my workflow for a specific report to take in the most recent file from a shared drive. My issue is in selecting the correct SHEET once the file is found. I am using the Dynamic Input took to bring in my data, in the settings I am choosing the sheet I want to take data from, however I continue to get an error that I need to specify the sheet. 

 

See screenshot! I have tried a few solutions, none of which worked. Anyone know what I'm missing?

 

Sam

8 REPLIES 8
AkimasaKajitani
17 - Castor
17 - Castor

Hi @SamStig

 

Does the input file contain unnecessary sheets?

I succeeded in showing the same error.

 

AkimasaKajitani_0-1621653944045.png

When I add the unnecessary sheet to the input file, the error occurs.

 

Please check the input file.

 

mceleavey
17 - Castor
17 - Castor

Hi @SamStig ,

 

I've attached an example and the two tools you need.

Simply add a sort tool after the directory and sort by creation time descending, then a sample tool to pick the first one. This is the latest file.

Then my tools do the rest. They find the sheet names and then dynamically load them in.

You can filter down to the sheets if required.

 

I hope this helps,

 

M.



Bulien

SamStig
6 - Meteoroid

This makes sense! I know there is one hidden sheet in the file and could likely be the culprit. I will report back if this solves. 

SamStig
6 - Meteoroid
Thank you @mceleavey! As an Alteryx beginner, can you share with me how to install the custom tools you made? I believe this may be the solution, as removing or unhiding hidden sheets didn't solve the issue. thanks!
mceleavey
17 - Castor
17 - Castor

Hi @SamStig ,

 

Ensure you have set up a macro folder. To do this, in Alteryx, go to Options->User Settings->Edit User Settings:

 

mceleavey_0-1622034858315.png

...then select the "Macros" tab.

Hit the mceleavey_1-1622034909484.png button. 

Navigate to the folder you would like to use for your macros.

 

When you have done this, open the .yxmc files in Alteryx. You will get a warning if you're using an old version of Alteryx but it will be fine to continue. Then simply save it to the macro folder you just set up. You should now be able to see the tools in the "In/Out" section of Alteryx.

 

Then open the workflow (the .yxmd file). If you see two black boxes with question marks in them, then you need to replace them with these tools. The first is the Dynamic Sheet Names tool, and the second is the Input Multi Schema Flat Files tool. The workflow should look something like this:

 

mceleavey_2-1622035160900.png

 

On each  of the new tools, go to the "Questions" tab and select "Fullpath" from the dropdown.

 

To remove the sheets you don't want you can place a filter after the first macro, which lists all the sheets it found. You can remove the ones you don't want.

 

Let me know if this works.

 

M.

 

 

 

 

 

 



Bulien

SamStig
6 - Meteoroid

WOW thank you so much for the in-depth response. I will try this and report back later today. 

Much Appreciated! 

 

SamStig
6 - Meteoroid

Neither of these ended up being my solution - though they may BE a solution, what worked best for me was using a Filter tool to choose the sheet name before the Dynamic output.... 

 

 

AkimasaKajitani
17 - Castor
17 - Castor

It's a very simple and effective solution!

 

Labels