Regarding dynamic excel Sheet name and dynamic worksheet name

Question

Sorry for reposting again! But since i am a beginner to alteryx. I am still facing issue in inputting my file. So basically the scenario is there will be a folder inside which there will be 4 files with different name. So i have to use one file named in the format as TSEQ_DD_MM_HHMM.xls  and there will be just one worksheet inside this xls file with the same name as file name.

So i have to use that file and then have to do some analytics. Now since this sheet name will change daily , as it is generated from some tool, can you again help me with some workflow , how i can code the input method that may  pickup the static keywork TSEQ_ and dynamically chooses that file daily(as the workflow i am preparing will run daily.

Looking forward to hear from you.

Jonathan-Sherman · Accepted Answer

Hi @nazuk,

I've attached a zip file with all materials I explain below:

Starting with the main workflow I bring in all filepaths of .xls files stored in a particular folder using the directory tool, remove unnecessary columns and filter for where the filename starts with "TSEQ_". I then create the full file path (including sheetname) as you stated it is identical to the name of the file.

This "new full file path" is fed into the batch macro to import the data.

The batch macro itself has very few tools in (just used to feed in a filepath into an input data tool):

This gives me the resulting output from the "TSEQ_" file in the directory selected:

Simply point the directory tool in the workflow at the correct location:

If this solves your issue please mark the answer as correct, if not let me know! I've attached a zip file containing all files needed (workflow, macro and two text .xls files) for you to download if needed.

Regards,

Jonathan

nazuk · Accepted Answer

Hello Jonathan!

Appreciate your quick response. The explanation is quite informative as well.

Just wanted to check, i have placed the macro at the shared drive, and Added the location in user setting as:

Still i am getting an error as unable to find macro.

Please suggest!

Jonathan-Sherman · Accepted Answer

Hi @nazuk,

You'll need to open up the workflow and replace the macro (right click on the canvas of the workflow -> insert -> macro -> browse -> navigate to the macros location):

Also make sure you've configured the macro correctly and you're passing the [FileName] field into the batch macro:

If this solves your issue please mark the answer as correct, if not let me know!

Regards,

Jonathan