Macro triggered by date

  • Thread starter Thread starter Risky Dave
  • Start date Start date
R

Risky Dave

Hi,

I am trying to figure out how to get a macro to run every financial quarter.

The theory should be simple - have a cell somewhere that captures the last
time the macro was run (in mmm-yy format) and add 3 months. compare that
value with TODAY() and if TODAY() is greater, run the macro.

The problem I have (and I'm sure it's easy to fix I just can't figure it
out) is how to define teh last run date plus 3 months. I can't do a day count
due to the differing lengths of months and I don't know how to tell Excel to
count months.

Any help you can supply would be appreciated.

Hope this makes sense!

TIA

Dave
 
Hi,

You could do it using workbook_open. With a date in a1 of sheet 1 the code
will call mymacro when >=90 days has elapsed. You then run your code and
update the date cell.

Private Sub Workbook_Open()
If DateDiff("d", Sheets("Sheet1").Range("A1"), Now) >= 90 Then
Call mymacro
End If
End Sub


Sub mymacro()
MsgBox "Your code"
Sheets("Sheet1").Range("A1") = Now
End Sub

Mike
 
In VBA you can use

DateAdd("m", 3, dYourDate)


to return the date 3 months after dYourDate
 
In Cell A1 put the start of finacial year : 2/1/07 (it can be any year)
In Cell A2 : Today()

what I'm going to do is to count number of months from the 1st day of the
month. the number of months is 12 * delta of years + Months

Assume A1 is the 1st of a month

the first of Today()
=Date(Year(Today()),Month(Today()),1)

The Difference in months between the two dates is
=Month(Date(Year(Today()),Month(Today()),1))-month(A1)

The difference in years is
=Year(Date(Year(Today()),Month(Today()),1))-Year(A1)

Now combine using months = 12 * Year + Month
=12 * Year(Date(Year(Today()),Month(Today()),1))-Year(A1) +
Month(Date(Year(Today()),Month(Today()),1))-month(A1)

Now test if greater than 3
=if(12 * Year(Date(Year(Today()),Month(Today()),1))-Year(A1) +
Month(Date(Year(Today()),Month(Today()),1))-month(A1)>3,True,false)
 
In addition to the other answers;

If this is a workbook you are in all the time anyway, then having the timer
in the workbook is fine.

I have one workbook that is only opened when data needs to be crunched, and
that data is only available for a limited time period, so it needs to be run
right away (and not wait for the user to remember that this is the day the
data is updated). So I set the following code in a vbs file. I then created a
windows scheduled task (in XP, Start/All Programs/Accessories/System
Tools/Scheduled Tasks). When the scheduled time occurs, the user gets the
prompt and can decide whether to open/run the file.

Of course, the update prompt is limited to the one PC this is installed on,
but I only have one user who needs to update this file, so that is fine for
our project.

strUserIn = MsgBox("Run the PO file?",vbYesNo,"Automated Prompt")
If strUserIn = 6 then
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Documents and
Settings\Max4p\Desktop\product analysis\opo5.xls")
objExcel.Visible = True
objExcel.Run "CrunchIt", "SkipEmailPrompt"
'above line runs the macro, with optional parameter
objExcel.ActiveWorkbook.Save
end if
 
Back
Top