Automate Excel Path While There is a Date Issue

Help Activities
, ,
1

Dear Experts, I receive excel file from user daily basis. In where, today’s date reflected. Therefore, the path is like this- "“C:\Users\Desktop\payment 04-Sep-2023.xlsx”. For this, every day I need to change date in excel path. Is there any solution to automate excel path? By which only existing workbook will be choosen.

I already automate, to pick current date excel file.

image
image1920×1080 112 KB

Please suggest how can I pick tomorrow or yesterday’s excel file.

2

Hey @Sabbir_Anwar ,

Kindly refer below screenshot
image

You can store the values into a variable and then use it accordingly as per you use case

now.AddDays(-1).ToString("dd-MM-yyyy") -> Will return Yesterday's date

now.AddDays(1).ToString("dd-MM-yyyy") -> Will return Tomorrow's date

Hope it helps you !

3

Another way would be:

  • Get all the files within a folder: Directory.GetFiles(“Folder_Path”,“*.xlsx”)

  • Process your file and save the name of the file in a log.

  • Use the log to avoid processing the file again.

  • That way you wouldn’t need to care about the date and you can just process the latest excel file that shows up in the folder.

Thanks!

4

Hello @Sabbir_Anwar

  1. Assign currentDate = DateTime.Now.ToString(“dd-MMM-yyyy”)
  2. Assign excelFilePath = "C:\Users\Desktop\payment " & currentDate & “.xlsx”
  3. If File.Exists(excelFilePath)
    a. Open Excel File activity (use excelFilePath as the file path)
  4. Else
    a. Log Message or Throw Exception activity to handle the case when the file does not exist

Thanks & Cheers!!!

5

Main.xaml (8.6 KB)
I tried, but its not working.

Seeking help.

6

Which part is not working? I tested it and the bot is picking up the latest file from the folder.

7

Could you please kindly tell , what is the date format of 03-Sep-2023?

8

Hey @Sabbir_Anwar , the date format for 03-Sep-2023 is “dd-MMM-yyyy”

Hope it helps you!