How to pick latest excel sheet from a folder?

[SwatiAgrawal]

Hi Everyone,

I have created a bot wherein I am using "MS Excel VBO - Open workbook" to open a particular excel workbook by giving the name of excel workbook with the path.

Eg: "C:\abc\xyz\Status Report - 28-08-2020.xlsx"

But instead of giving name, can I give instruction to bot to pick a latest sheet without giving it's name? 

The reason being, bot is "saving as" the excel sheets and if I give name then it will pick particluar excel sheet. But everytime I run the bot then I want the latest excel sheet to be picked.

Please suggest and help.


------------------------------
SWA
------------------------------

[michaeloneil]

Hi

If you use Utility - File management you can get all the files in a directory by defining the file extensions e.g. "*.xls, *.xlsx, *.doc, *.docx". This will output to a collection list all the file names and information including Created (date), Last accessed(date) and Last written(date). You can then use this list to select the most recent file by date, usually the list is sorted automatically using the last accessed column but to be sure you can all use the Utility - Collection Manipulation to sort it the column you want then select the first row.

Hope this helps 🙂

------------------------------
Michael ONeil
Technical lead developer
Europe/London
------------------------------

[SwatiAgrawal]

Hi Michael,

Thanks. The Utility File Management > Get Files worked. Then I used Utility Collection Manipulation > Filter collection to get the latest excel sheet. But it throws error for me. Please suggest.

------------------------------
Swati Agrawal
------------------------------

[Daniel_Sanhueza]

Hello Swati,
Only as a tip, if the Column has an space " " between the name, you have to add "[]" For your example would be something like "[Last Written] = '"&[DateTime]&"'"
Last Written is a column, so it must has the [] inside the string, when giving a condition you have to pass it has a Data Item and Concatenate it, in the filter action you can use <, >, <>, like, not like, =, AND, OR, etc etc

Just keep in mind the memory and dont do this un large collection

------------------------------
Daniel Sanhueza
RPA Professional Developer
Deloitte
America/Santiago
------------------------------

[michaeloneil]

Hi Swati

In the "Filter" the name you have given is "Last Written (DateTime)"  this should only be "Last Written" the (DateTime) is only there as a reference to the data type not part of the column name just take that part out and it should work. Also remember to take out any spaces before or after the text.

------------------------------
Michael ONeil
Senior Developer
Clydesdale and Yorkshire bank
Europe/London
------------------------------

[SwatiAgrawal]

Hi Daniel,

Thanks. I used what you suggested. But when I run the bot then my "collection out" is empty. It doesnt show the columns 😞
Can you suggest where am I going wrong? Attaching screenprints for reference.

------------------------------
Swati Agrawal
------------------------------

[SwatiAgrawal]

Thanks.
But it says missing operand when I use "Last Written".

------------------------------
Swati Agrawal
------------------------------

[Daniel_Sanhueza]

Hello Again Swati, I can help you with that 😄 

1.- Below the process flow

2.- Action Propertios with a simple Filter

"[Last Written] >= '"&[Date]&"'"​

------------------------------
Daniel Sanhueza
RPA Professional Developer
Deloitte
America/Santiago
------------------------------

[SwatiAgrawal]

OK. How and where do you get this "Date" data item? Can you share screenshot for that? 
I am sharing you my screenshot of Utility - File management.

------------------------------
Swati Agrawal
------------------------------

[SwatiAgrawal]

Hi Daniel,

You can ignore my earlier post regarding date data item. I figured that out.
Thanks much for the help. Appreciate it !!

Can you tell me how should I further open that particular excel sheet from that "collection out".
I need to give command to open that excel sheet, make changes and save at a particular location.

Thanks in advance.

------------------------------
Swati Agrawal
------------------------------