From share drive folder count latest .xlsx files on a monthly frequency

[sumankothari]

Hi,  I am building a Bot, to pick files from different folders in a specified share drive location and provide a count of the unique files monthly.
In process stage I have created an action using  "Utility file managment to Get files" for each folder into a collections. Then I have created  another action using "Utility Collection Manipilation to Append rows to collection" which gives me the total count of files.
The problem I want to resolve is i dont want to hard code the "child folder" name where the files are stored as a new/ multiple  folder gets created every month when an action happens.
Sample folder path
X:\Index\trade\Test\Strategy\2021\Jan
X:\Index\trade\Test\ this path is constant i have 10 different folders similar " Strategy" which i need to go and get files for year 2021 followed by month and Date


------------------------------
suman kothari
------------------------------

[BenLyons · Accepted solution]

Hi Suman,

To get the respective month you can use the calculation FormatDate(Today(),"MMM"). This will give you the 3 letter form of the current month, for example FormatDate(11/02/2021,"MMM") = "Feb".

This would require you to run the process during the respective month, to get a previous month you can use the add months function as follows FormatDate(AddMonths(Today(), -1), "MMM").

So in full you'd have "X:\Index\trade\Test\Strategy\"&FormatDate(AddMonths(Today(),-1),"yyyy")&"\"&FormatDate(AddMonths(Today(), -1), "MMM").

This way you can get the year and month for last month automatically in the folder path.

Hope this helps.

------------------------------
Ben Lyons
Product Consultant
Blue Prism
UK
------------------------------

[michaeloneil]

Hi Suman

Ben's description below will do what you need for getting the path of each folder. Since you say you dont want to hard code this in every stage where you call one of the paths then what you could do is put a multi-calculation stage at the start and set all the folders to output to data items. Then all you need to do in each file stage is call the relevant data item for that file. Also a good practice approach would be to put your root folder, X:\Index\trade\Test\ , in an environment variable that way if the root folder ever does change then you only need to change this in the env variable instead of in the code.

One extra tip, when you are running this process in a schedule you may want to set the root folder without the drive letter as this can cause failures in accessing it when running on a schedule even though it will access it fine in debug mode. So you folder would look like \\Index\trade\Test\  instead of X:\Index\trade\Test\ and this will work consistently in debug and scheduler.

Good luck 🙂

------------------------------
Michael ONeil
Technical Lead developer
Everis Consultancy
Europe/London
------------------------------

[sumankothari]

Thanks Ben, I appreciate your detail steps this was very helpful.

------------------------------
suman kothari
------------------------------

[sumankothari]

Thanks Michael, for all you guidance. I got some head start will keep you posted once done.

------------------------------
suman kothari
------------------------------