PDA

View Full Version : run macro automatically based on criteria in other spreadsheet



obriensj
07-19-2012, 01:23 AM
Hi,
Not sure if this is possible but I have seen code for application.ontime in relation to automatically running a macro at a certain time.
I can get this to run ok on my PC but the user has to actually run the macro and then say 10, 20 secs later the macro will execute.

What I want is the ability for a macro to run automatically based on criteria I have in another spreadsheet. See sample.xlsx.

So for instance every weekday I have two cut off times, 2pm and 4pm, if there is nothing populated in the cut off1 column then my macro will run.
The same for cut off 2, if it does not say "yes" then my macro will run.
How can I code a macro to say when it gets to 2pm and 4pm respectively for that business day to look at this spreadsheet (sample.xlsx), look at columns A (which has the business day) B and C and if one or both of those columns is blank, eg does not say "Yes" then execute my macro? When it says "Yes" then do not execute my macro?

The macro needs to execute at 2pm and 4pm so twice daily. When I refer to my macro I already have another macro which then sends an email out.
Is this possible at all?

Thanks

Teeroy
07-19-2012, 05:49 AM
Hi,
The following should work for you but since it's designed to work over a business week I haven't fully tested it :whistle:. The only requirement is that it needs to be placed in a standard code module (otherwise the macro may not be found).


Sub SetRunTimes()
Dim RunTime1 As Date
Dim RunTime2 As Date

RunTime1 = Date + 1 + TimeValue("12:00:00")
RunTime2 = Date + 1 + TimeValue("16:00:00")

Do While Weekday(RunTime1, vbMonday) > 5
RunTime1 = RunTime1 + 1
RunTime2 = RunTime2 + 1
Loop
'Set the code runs for the next business day
Application.OnTime RunTime1, "CheckAndRun"
Application.OnTime RunTime2, "CheckAndRun"

End Sub

Sub CheckAndRun()
Dim testvalue As String
Dim Col As Integer

If TimeValue(Now) < TimeValue("16:00:00") Then
Col = 1 'running before 16:00:00
Else
Col = 2 'running after 16:00:00
Call SetRunTimes 'to set the timer for the next business day on the last run
End If

testvalue = Sheets("Sheet1").UsedRange.Find(Date).Offset(0, Col)
If testvalue = "YES" Then
'call your macro here
End If

End Sub

obriensj
07-19-2012, 09:21 AM
Thanks Teeroy, but I cant seem to run, says cant execute break in code. Also I would need to have the macro open to run is there anyway the macro can run itself based on criteria in the sample spreadsheet?

Teeroy
07-19-2012, 03:36 PM
I don't get any code error (using excel 2003) but you're right that the worksheet would have to be open to use Application.ontime. You could try two things;
1. use a windows scheduled task on logon to launch the worksheet in .visible=false mode where Application.ontime would work.
2. Run windows scheduled tasks at 12 and 4 daily (2 recurring tasks) which launch the workbook. The check would then be carried out at the workbook open event and close the application after running.