The logic for this program should go like this.
- I recommend exploring the possibility to store the file with your chosen name rather than the direct download. (This saves you - and the bot - the time and effort to identify the latest file)
- If this is possible, see if you can clean up the folder before downloading the file. That way you only have to deal with the one file, no need to find the latest file.
- If 1 or 2 is not possible, then you will need to find the latest file by comparing all the files in the given directory.
Use File.
Assign: String() files = Directory.GetFiles("DirectoryName")
Assign: String latestFilename = ""
Assign: DateTime latestTimestamp = File.GetLastWriteTime(files(0))
The above line will assume the very first file in the file list is the latest (for comparison later)
For each filename in files
→→ Assign: currentTimestamp = File.GetLastWriteTime(filename)
→→ If currentTimestamp > latestTimestamp
→→→→ latestTimestamp = currentTimestamp
→→→→ latestFilename = filename
At the end of this loop, the latestFilename value should be the filename which was last downloaded. (This logic is not tested, so might need some tweaks)
- To get the sheet name, you can use Get Workbook Sheets activity.
Assuming the file will only have one worksheet, you can use the first sheet from the list.
Or if you can confirm that the sheet has the same name as the filename, you can simply use the filename without extension as the Sheet name. (from what I observe in your screenshots, that seems to be the case)
- Steps 3 and 4 in your problem statement are straight forward and you already have the right structure up - Excel Application Scope to open “1. Daily Open CRs bFO pullout.xlsx” and use ‘Write Range’ to update the sheet with your copied data.
