Dynamic Excel Open & Write

[MarekRe]

Hello,

I am looking for some guidance to automate the following process...

1. Every day I receive an Excel file to my mailbox.

2. In this file there is information about daily sales (always in the same sheet and cell) that I need to paste into a different workbook.

What I was thinking to do is the following:

- Save incoming daily sales Excel reports on SharePoint with a specific naming (DDMMYY_Sales)*;

- Open daily sales Excel with a dynamic input tool (because every shipments file will have a different name as these are sent daily) and store the sales value;

- Open consolidated Excel with shipment information and enter a value in the specific cell (also needs to be dynamic, because each cell will represent a different day).

*This I can do with Power Automate, not sure if with Alteryx too.

If the above makes sense to you, is there someone who could help to design a workflow like this? I am a bit confused with how to use the dynamic input tool to get Alteryx to open different Excel files every day...

thank you!

[Vijuzy]

Use formula tool to change the file name dynamically. Use the example workflow attached. Hope it will be helpful.

Regards,

Vijay

[ImadZidan]

Hello @MarekRe ,

this sounds very doable.

Sample files of ur excel will be very helpful.

[MarekRe]

Of course, here are the attachments.

16022021_Shipments - daily sales update file I receive via e-mail; the data will be always in cell H60, sheet WB101.
DailyShipments - consolidated file in which I would like to paste the data from the daily sales update; in column F; row depending on the day when the sales update comes (because the name of the file will be based on the day received, example DDMMYYY_Shipments). therefore it has to be dynamic somehow.

thank you very much for your help!

marek

[LukeG]

@MarekRe 

If you have your SharePoint synced with something like OneDrive, then you should be able to use a combination of the directory tool and dynamic input to read the correct file. You can have this follow the logic of the naming convention, or you can use the "Date Created" to dynamically get the most recent file.

Here is an article that shows an example of Directory and Dynamic Input together.

Finally, I have also seen some similar automation using the Outlook tool to fetch new files from an inbox and use them in a workflow each day.

Please let me know if you have any questions. Good luck!

- Luke

[ImadZidan · Accepted solution]

Hello @MarekRe ,

Apologies for the delay.

Well we have to start from somewhere.

Please look at the workflow and lets build on it to make it fit. 

The workflow does the following:

Reads xlsx files from a pre-defind directory

selects today's file based.

filters to get the required record and cell.

Reads the consolidation files and in this case I am EMEA to update

Joins and unions records to get the full set.

Overrides the EMEA sheet with the new value.

Please have a look and then lets engage.

[MarekRe]

Excellent! Works perfectly, thank you very much.