For Each File in Folder Exclude temporary files

[Sekoleyte]

Hello. I’m trying to learn RPA and studying on UiPath academy courses. In the course that is called as Build your first automation process with studio, we are creating a Sales Results excel file to consolidate Daily Reports. In the course, it creates Header as writing all the headers one by one in Sales Results excel file with “Write Cell” activity. Instead, I wanted to use “Append Range” activity in studio to get the Header from one of the Daily Reports. But after, this when I use “For Each File in Folder” activity to consolidate the Daily Reports, it gives me an error because while taking the Header from Daily Reports it opens it and it creates a temporary excel file that starts with “~$”. The error is like that:

“Excel Process Scope: Failed opening the Excel file FolderPath\UiPath\Excel Reports Consolidation_rev2\Daily Reports~$Coffee Shop 1.xlsx. Possible reasons: the file is corrupt, the file is already used by another process, you don’t have permissions to open the file or the file has incorrect sensitivity label.”

The problem is “For Each File in Folder” activity sees this temporary folder because I used it to get the Header and it gives me an error. But I couldn’t use the “Filter by” option not to use this temporary folder. Can you please help me to show how I can omit this temporary folder in “Filter by” in “For Each File in Folder” activity. Or I am open to alternative solutions, because it gives me an error the file is already open. I don’t know yet if there is a way to close the file to use it again not to get this error.

Kind Regars,

[ashokkarale]

@Sekoleyte,

In the Filter by property of the For Each File in Folder activity, you can use a wildcard character to include only files that do not start with "~$"

If the above don’t work, you can use this logic to get all files of the folder excluding the temporary.

allfiles = Directory.GetFiles(FolderPath, "*.xlsx").Where(Function(f) Not Path.GetFileName(f).StartsWith("~$")).ToArray()

Use this in Assign activity and then use the allfiles variable in for each activity to iterate.

[Sekoleyte]

Hi. I’ve already tried to do it but it didn’t work because I’m not able to use “CurrentFile” here. Do you know how I can write it in Filter By place.

[ashokkarale]

@Sekoleyte ,

Updated my answer with another approach

[Sekoleyte]

Hi. With 2nd approach, i could able to solve it. I chose the data type for variable as System.String and after assigning the code to this variable, i used For Each activity and then in Use Excel File activity i chose Excel file as currentText. and it worked.

Thank you.

Btw, I’ve found another solution before this. I changed the outline for For Each File in Folder activity, so I didn’t use open Daily Report file again and didn’t get any error. You can check ex and new outlines in attachment.