Macro to clear sheet values after four days

Question

I need your help in creating the macro in an Excel file that clears all the values in the sheet which is called "Sheet1" after four days from today's date, which is "2-Jun-2015", and to save it. So once the user opens the Excel file on the fifth day, the sheet will be blank.

The code for clearing the data is:

Sheet1.Cells.Clear

But my issue is how to clear the data after four days and to save the changes with the user unable to see any data in the fifth day.

score 0 · Accepted Answer · answered Jun 02 '15 at 13:08

0

This will work for you. DateDiff is a great function that would return the difference between two dates in a unit of your choice (years, months, days, hours ...), also you FileDateTime to get date of the file

Sub Workbook_Open()
    If DateDiff("d", FileDateTime(ThisWorkbook.FullName), Now) >= 4 Then
        Sheet1.Cells.Clear
        ThisWorkbook.Save
    End If
End Sub

answered Jun 02 '15 at 13:08

Jeanno

2,769
4
23
31

Will the macro run automatically or is there a function similar to onload that will be running each time the excel file is opened? – 99maas Jun 02 '15 at 13:33
This macro will run every time the workbook is opened – Jeanno Jun 02 '15 at 14:07

score 0 · Answer 2 · edited May 23 '17 at 10:33

I haven't had the chance to test it yet, but might I suggest something like this:

Private Sub Workbook_Open()
Dim currDate As String, closeDate As String, fileDate As String
Dim oFS As Object, sFile As String

currDate = Date
closeDate = Date(Year(currDate); Month(currDate); Day(currDate)-4)

sFile = Application.ActiveWorkbook.Fullname
Set oFS = CreateObject("Scripting.FileSystemObject")

fileDate = oFS.GetFile(sFile).Datelastmodified

If fileDate <= closeDate Then
    ' Change argument passed to Sheets to whatever sheet you want to kill off
    ActiveWorkbook.Sheets(1)Cells.Clear
End If
End Sub

Borrowed some code from here: https://stackoverflow.com/a/10823572/4604845

Macro to clear sheet values after four days

2 Answers2