Formula to Read the Contents of the File using the Sheet Name

[KamenRider]

Hi,

I have some problems on reading the Sheet Name to fully access the contents of the excel File. The name of the File is TKL 2023.08.11.xls and the Sheet name is Syndicates-20230811-1709. The structure of my workflow uses a directory tool to be able for me get the latest date as below.

My formula tool is just [FullPath] and I am receiving error as "No Table chose; please select a table from data source". Please advise what configuration shall be entered maybe in the formula tool for me view the contents of the file. How can enter to choose the sheet name with format as  >>  Syndicates-20230811-1709.

Hope to share your expertise on this.

Thank,

Kamen

[Felipe_Ribeir0]

Hi @KamenRider 

Try this inside of your formula: [FullPath] + "|||`Syndicates-20230811-1709$`"

[rzdodson]

@KamenRider when able, would you mind posting the actual workflow? May be able to help you out here.

I am suspecting that the issue is with your last formula tool. Typically, Alteryx needs a sheet name along with your file name together to be able to feed in to a Input or Dynamic Input tools.

Y:\01. Account Projects\Folder Path\File Name.Extension|||Sheet Name

In your use case, it would be something like Y:\01. Account Projects\Folder Path\TKL 2023.08.11.xls|||Syndicates-20230811-1709

[KamenRider]

Hi @rzdodson and @Felipe_Ribeir0 

I apologize that I can't upload the workflow since it prohibits us. I really hope I could. I can only provide the screenshots.

My only problem with the format you gave us "Y:\01. Account Projects\Folder Path\TKL 2023.08.11.xls|||Syndicates-20230811-1709" is that the sheet name changes everyday. The part highlighted with red changes everyday "....|||Syndicates-20230811-1709". for example tomorrow the sheet name will be "....|||Syndicates-20230812-1502". If this is the case, how can we present it the workflow. I have a hard time configuring the solution using the structure I made below.

Here is the per tool screenshots:

 

 

 

 

 

 

 

Hoping we could find a solution. Thanks

Kamen 

 

 

 

 

[Felipe_Ribeir0]

The dynamic input tool has an option to get tab names of excel files (https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Dynamic-Input-List-of-Excel-Sh...). The problem here is, this option doesnt work well with .xls files.

So my approach to this problem is:

1-Convert .xls into .xlsx files using this macro: https://community.alteryx.com/t5/Community-Gallery/Convert-xls-into-xlsx-and-parse-the-input/ta-p/99...

2-Now, with .xlsx files you can get the sheetnames using batch input macro: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Knowledge-Base/The-Ultimate-Input-Data-Flo...

3-Filter the pattern of the sheet

4-Import the sheet

[KamenRider]

Hi @Felipe_Ribeir0 

The solution is fantastic! except that I notice is that when you rerun it again, all the excel files are opening asking to be save. I do hope we have something that automatically save or overwrites the files.

One question of mine is that since this is run by macro and we are using shared drive, I hope anyone can still run it. Do I need to save the macro? If yes, how can I save it?

Looking forward for your response before I close this.

Thanks,

Kamen

[rzdodson]

@KamenRider 

You don't necessary need to save the individual macros.

Users can package the workflow and export it. Doing so will bundle all of the data inputs, outputs, and macros together so the workflow can be saved on a shared drive and run without issues.

If you have never saved the workflow before, Alteryx will prompt you to save it as an Alteryx workflow file (.yxmd) first. Once complete, you will be able to go to Options > Export Workflow to save it as a packaged workflow file (.yxzp).

[Felipe_Ribeir0 · Accepted solution]

Hi @KamenRider 

1-About the macros:

-Yes, its better for you to keep them in the network shared drive in an organized way. If you look at your download folder, you will find a folder with the same name of the workflow package that you downloaded (Batch Macro Workflow). Inside of this folder you will find the macros, just copy them to a specific folder inside your network drive:

After that, you can go to the main workflow and edit the dependencies of it to consider the new paths:

2-About the overwrite, the attached new version will do the job.

[KamenRider]

Thanks @rzdodson 

However, what if I save it directly in the shared drive without packaging it, will the macro still works for everyone accessing the workflow?

Thanks,

Kamen

[KamenRider]

Thank you @Felipe_Ribeir0  and also to @rzdodson.

Your ideas is a big help.

Kamen