Launching excel with changing filename

[sbreslin14]

I made a desktop flow that completes reports containing dates in the filename. Weekly, the dates of the filename change and I can't launch the excel action without selecting the file path each week. I saw a solution using "Get Files in Folder" action which seems like the right track, but I didn't know what .FullName was.

To better explain, I get a new file every week named 1-22-23report.xlxs and the next week would look like 1-29-23report.xlsx. I'm wanting to find an action that looks like "Launch excel with a file containing "report"" so every week, it runs without looking at the dates. 

[mmonline]

Are you saying that the only file in the folder will be the one in question - the newest one? 

Are there other files? 

If that is the only file with the word report, that is an Excel file, you can do the following:

Action: Get files in folder with a file filter of: *report*.xlsx

Action: For Each iterate through the %Files% object created above, store in %file%

Action: Set Variable  set %fullname% variable with %file.Fullname%

Then you can launch Excel using that file name.

This only works if it is the only file with "report" that is a .xlsx

But... you can also sort the files on the Get files in folder action by the "Creation time" (created date), descending order. That will give you the most recently created *reports*.xlsx file. Then leave the loop after the first iteration or you access the file with a Set Variable - immediately after the Get files in folder action.

You'd be referencing: %Files[0].fullname%

Which is saying, the first file returned [0] and the fullname property.

Then you don't need the loop. Again, this assumes there are no other *report*.xlsx files that will make their way into the folder.

Hope this help.

[sbreslin14]

I followed your instructions but I'm having a hard time launching excel. After "Get files in folder" is this what the loop and launch should look like?

[MichaelAnnis]

If you only need the one file, do your Get Files action and then in the Advanced section of that action, sort like @mmonline said:

Now, your file with be %Files[0]%...no need for a for each.

Good luck!

[sbreslin14]

Sorry, me again but I feel like I'm so close!

For "Get files in folder" I have the correct folder selected and I added the sort by creation which is super helpful. In that action, the variables produced is %Files%

Next, do I need the "Set variable" action if I don't have the loop still? If so, this is what I have at the moment. 

My next action is "Launch Excel" with these parameters. However, I'm getting an error that Index '0' is out of range.

[MichaelAnnis]

Index 0 means no files exist in %Files%.  Please review the Get files command and make sure the path is correct.  If you hit the page icon with the up arrow on get files, it should take you to the folder in question, if not, then it is not correct.  

You can also view %Files% after an unsuccessful run and see if anything is in it.  Once you fix the path, %Files% should be a list of full file path and names and then Launch Excel with %Files[0]% should open up the first file in %Files%.  No need to set variable or have a for each since you are just working with the latest file.

[mmonline]

Here are a few things to reference.

I created a video, with a working FLOW, and a downloadable text file with the FLOW.

https://achievedatasolutions.com/power-automate-desktop-examples/

The video on YouTube here:

https://www.youtube.com/watch?v=FgsHG7leH3E

Sorry for not answering sooner. I've been offline for a few days.

[mmonline · Accepted solution]

The Flow Actions in my above alluded to video.

Get Files in folder: Sorted by 

Set variable (FileName)

Launch Excel (and open the following document - %FileName%)

[sbreslin14]

Thank you all so much for your help!! It's working now!

[Agnius]

You do not really need to store the full name into the variable. A variable of type file is also perfectly valid to be used in the Launch Excel action. So, you could simply use %Files[0]% in there.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution.
If you like my response, please give it a Thumbs Up.

If you are interested in Power Automate, you might want to follow me on LinkedIn at https://www.linkedin.com/in/agnius-bartninkas/

[sfos83]

Hi guys,

I'm also grappling with this one; I'm following the exact instructions as laid out, but I get this error:

If I test the Files1 value with a message display after Get Files, it returns the exact filename I want to open

I've tried every permutation of the solutions, with no luck

If anyone can help, that would be amazing

Thanks

Steve

[MichaelAnnis]

Get Files results in a list type variable. So %Files1% is a list and you cannot open a list in Excel.  What you want to do is open the first file in the list. That syntax is %Files1[0]%.  PAD is 0 based index so the first of anything is always identified by 0.

[sfos83]

I literally just hooted! This worked after days of struggling with it - it's a really good thing to know that the Files are created as a list and not a single item.

Thankyou so much for this, I'll spread the word should I see anyone else struggling with it

Steve

[HollyS]

Thank you so much! Worked like a charm.