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

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.

Batch Macro Workflow.yxzp

All comments

Hi @KamenRider

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

@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

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:

max date.PNG

ull pat.PNG

Hoping we could find a solution. Thanks

Kamen

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-Sheet-Names/td-p/367528). 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/999044

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-Flowchart/ta-p/20480

3-Filter the pattern of the sheet

4-Import the sheet

Batch Macro Workflow.yxzp

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

@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).

Exporting Alteryx workflow.png

Hi @KamenRider

1-About the macros:

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.

Batch Macro Workflow.yxzp

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

Thank you @Felipe_Ribeir0 and also to @rzdodson.

Your ideas is a big help.

Kamen

@KamenRider to answer your question, if you don't package the workflow before saving to the shared drive, the macros may not work because their macro input tools typically rely on incoming data fields arranged in specific ways, or have specific formatting. If that macro relies on an Excel file input, for example, the workflow will error out once it hits the macro because it is trying to retrieve data to support the macro that is no longer available due to the file being stored elsewhere and is "unattached" to the workflow.

If the input data is dynamically retrieved (i.e. web source) your macro would still work in the workflow regardless if the workflow was packaged.

As a best practice, always export your workflow to your shared drive as a packaged workflow. This will ensure won't run in to issues with being able to retrieve incoming data, can help the developer with error trapping/troubleshooting, and can provide an element of version control to your project.

Hi @rzdodson

Thank you for your response. What I did in order to connect to the macro is to save it in the shared drive together with the workflow and call it out after I open the workflow. In line to what you said to package, am I right to go to the Options and export workflow? Do you have a step by step process on how to package the workflow?

Kamen