Delete excel end of month

[borismh (Boris)]

good morning everyone

I have a question, I don’t know if it’s possible

the robot adds continuous data in an excel, without rewriting anything, so the excel is increasing data

case

I want that when I get to the end of the month, this excel is cleaned or deleted, to generate a new report

the way it will be saved, I already have ideas, but in this case I have some drawbacks

I hope you can help me

[AndrewHall]

Sorry, it’s not clear specifically what you are asking for help with?

[NIVED_NAMBIAR (NIVED N)]

Hi @borismh

Can u clearly elaborate your case

[borismh (Boris)]

@NIVED_NAMBIAR
@AndrewHall
Okay

I want that when I get to 9/1/2020 a random excel file will clean all the data or delete the excel file

and so on for 10/1/2020, 11/1/2020, 12/1/2020, etc

sorry for my English

I hope to be clear

[NIVED_NAMBIAR (NIVED N)]

Hi @borismh

U can use delete activitiy for deleting the file
Is the file name is dyanmic or remains same

Nived N

[AndrewHall]

If you’re question is around how to figure out if it’s the first day of the month

• You can use an Excel formula to figure out the day of the of the month (there are some samples in the “Data” sheet of the associated project notebook)
• Then use an “If” statement to conditionally either delete the data or the file if it’s the first day of the month as @NIVED_NAMBIAR indicated.

[Rohit.Garg]

first you can check if the current date is 1st of month, if yes, you can use delete file to delete the excel. But you need to ensure that the new file should be created for the follow on process of file update. it should not throw exception “File not found” Let me know if this helps.

[borismh (Boris)]

How do I get the 1st day of the month in a variable, and let it be so consecutively, let’s say that the robot will run daily and at one point it will reach the next month

[NIVED_NAMBIAR (NIVED N)]

Hi @borismh

Please look into this, this will help u how to schedule work in orchestrator for every month

How to schedule job on every 2nd working day of the month

Hello, How can a job be scheduled on 2nd working day of the month?

Hope it helps

Nived N

Happy Automation

[Rohit.Garg]

@borismh
you can use below expression to get current date.

Date.Now.ToString(“dd”)

After getting current date, you can use control flow statements to go further. Use attach file to refer how to check for 1st day of month.

Let me know if this helps Check for 1st Day.xaml (5.0 KB)

[rohith.prabhu]

Hello @borismh,

Check if the current date is beginning of the month. If “Yes”, delete or move the excel to required folder. Create new excel for the new month.

Thanks,
Rohith

[Pratik_Wavhal]

Hi @borismh

Below is the workflow for the same :-
MainPratik.xaml (19.4 KB)

To find the current Year :-

Int32 currentYear = DateTime.Now.Year

Condition to Check If Leap Year or Not :-

currentYear Mod 4=0

Dictionary to Store Total Days of Month in Leap Year :-

Dictionary(Of String, Int32) dicOfMonth = New Dictionary(Of String, Int32) From {{"January",31},{"Febuary",29},{"March",31},{"April",30},{"May",31},{"June",30},{"July",31},{"August",31},{"September",30},{"October",31},{"November",30},{"December",31}}

Dictionary to Store Total Days of Month in Non Leap Year :-

Dictionary(Of String, Int32) dicOfMonth = New Dictionary(Of String, Int32) From {{"January",31},{"Febuary",28},{"March",31},{"April",30},{"May",31},{"June",30},{"July",31},{"August",31},{"September",30},{"October",31},{"November",30},{"December",31}}

To Find Current Month :-

String currentMonth = DateTime.Now.ToString("MMMM")

To Find Value using Key i.e., For which month how many days are der :-

Int32 daysInCurrentMonth = dicOfMonth.First(Function(x)x.Key.ToString.Equals(currentMonth.ToString)).Value

Formatting Last Date of Current Month :-

String lastDateOfCurrentMonth = daysInCurrentMonth.ToString+"/"+DateTime.Now.Month.ToString+"/"+currentYear.ToString

Again Within IF Condition to check weather todays date is last date of the current month :-

DateTime.Now.ToString("dd/M/yyyy").Equals(lastDateOfCurrentMonth)

Depending on Above Condition True Or False perform the Operation you want :-

Mark as solution and like it if this helps you

Happy Automation

Best Regards
Er Pratik Wavhal