I wish to execute a Macro on opening up of an Excel file, but only if
the time the file is opened up is between 8:40am and 8:42am Mon-Fri
and 9:40am and 9:42am Sat-Sun.

My questions a

1) How would I do the above? I plan to open the file via Schedule
Task, so I've placed a 2 min window for opening, just in case between
the schedule task running and the file actually opening up, might take
longer than specifying an exact time eg 8:40 am and thus will mis the
execution of the macro.

2) Can I call a named macro, say Macro1 (that is within a normal
module) or do I have to enter all the code line by line, my code in
Macro1 also has several macros called within? I'm never sure on this
point if I have to re-enter the code line by line, I know if I do, it
will work, but will just calling the macro also work?

Thanks

If you want a macro to run on the opening of a workbook, just put it
in or call it from the "ThisWorkbook" section where the sheets are
listed in VBE.

Personally, I'd just wrap some date/time checks around the macro call
to make sure it only runs within the times you specified. Something
like...

private sub RunMyMacro()
if now() between starttime and endtime and weekday between monday
and friday then
RunTheMacroOfChoice
end if
end sub

Of course that code won't work; it's just the logic. You'll have to
work out the details...

Put this in a General module:

Option Explicit
Sub Auto_Open()

Dim OkToCallMacro As Boolean
OkToCallMacro = False
Select Case Weekday(Date)
Case vbMonday To vbFriday
If Time = TimeSerial(8, 40, 0) _
And Time < TimeSerial(8, 43, 0) Then
OkToCallMacro = True
End If
Case Is = vbSaturday, vbSunday
If Time = TimeSerial(9, 40, 0) _
And Time < TimeSerial(9, 43, 0) Then
OkToCallMacro = True
End If
End Select

If OkToCallMacro Then
Call myMacroNameHere
End If

ThisWorkbook.Close savechanges:=false 'true???

End Sub
Sub myMacroNameHere()
MsgBox "hi " & Now
End Sub



Sean wrote:

I wish to execute a Macro on opening up of an Excel file, but only if
the time the file is opened up is between 8:40am and 8:42am Mon-Fri
and 9:40am and 9:42am Sat-Sun.

My questions a

1) How would I do the above? I plan to open the file via Schedule
Task, so I've placed a 2 min window for opening, just in case between
the schedule task running and the file actually opening up, might take
longer than specifying an exact time eg 8:40 am and thus will mis the
execution of the macro.

2) Can I call a named macro, say Macro1 (that is within a normal
module) or do I have to enter all the code line by line, my code in
Macro1 also has several macros called within? I'm never sure on this
point if I have to re-enter the code line by line, I know if I do, it
will work, but will just calling the macro also work?

Thanks

--

Dave Peterson

On Oct 11, 10:37 pm, Dave Peterson wrote:
Put this in a General module:

Option Explicit
Sub Auto_Open()

Dim OkToCallMacro As Boolean
OkToCallMacro = False
Select Case Weekday(Date)
Case vbMonday To vbFriday
If Time = TimeSerial(8, 40, 0) _
And Time < TimeSerial(8, 43, 0) Then
OkToCallMacro = True
End If
Case Is = vbSaturday, vbSunday
If Time = TimeSerial(9, 40, 0) _
And Time < TimeSerial(9, 43, 0) Then
OkToCallMacro = True
End If
End Select

If OkToCallMacro Then
Call myMacroNameHere
End If

ThisWorkbook.Close savechanges:=false 'true???

End Sub
Sub myMacroNameHere()
MsgBox "hi " & Now
End Sub





Sean wrote:

I wish to execute a Macro on opening up of an Excel file, but only if
the time the file is opened up is between 8:40am and 8:42am Mon-Fri
and 9:40am and 9:42am Sat-Sun.

My questions a

1) How would I do the above? I plan to open the file via Schedule
Task, so I've placed a 2 min window for opening, just in case between
the schedule task running and the file actually opening up, might take
longer than specifying an exact time eg 8:40 am and thus will mis the
execution of the macro.

2) Can I call a named macro, say Macro1 (that is within a normal
module) or do I have to enter all the code line by line, my code in
Macro1 also has several macros called within? I'm never sure on this
point if I have to re-enter the code line by line, I know if I do, it
will work, but will just calling the macro also work?

Thanks

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks Dave (once again!). I've used the code above, but if I try to
open the file oustde of the times in the code, it opens and closes
again. Is it supposed to do that? as its not something I can have. I
can't get back into it now!

This line closes the file--no matter what time it was opened.

ThisWorkbook.Close savechanges:=false 'true???

If you don't want the workbook closed, then delete this line.



Sean wrote:
<<snipped

Thanks Dave (once again!). I've used the code above, but if I try to
open the file oustde of the times in the code, it opens and closes
again. Is it supposed to do that? as its not something I can have. I
can't get back into it now!

--

Dave Peterson

On Oct 12, 2:15 pm, Dave Peterson wrote:
This line closes the file--no matter what time it was opened.

ThisWorkbook.Close savechanges:=false 'true???

If you don't want the workbook closed, then delete this line.

Sean wrote:


Aaaah I see. Slight tweak if I wanted it saved and closed
automatically when the Schedule Task runs only (i.e. I could still
open it manually) would I place the code like this

End Select


If OkToCallMacro Then
Call myMacroNameHere

ThisWorkbook.Close savechanges:=true
End If

On Oct 12, 2:31 pm, Sean wrote:
On Oct 12, 2:15 pm, Dave Peterson wrote:

This line closes the file--no matter what time it was opened.

ThisWorkbook.Close savechanges:=false 'true???

If you don't want the workbook closed, then delete this line.

Sean wrote:

Aaaah I see. Slight tweak if I wanted it saved and closed
automatically when the Schedule Task runs only (i.e. I could still
open it manually) would I place the code like this

End Select

If OkToCallMacro Then
Call myMacroNameHere

ThisWorkbook.Close savechanges:=true
End If

Just tested it, guess in answer to my question, it does. Thanks again
Dave

Maybe you could try:

If OkToCallMacro Then
Call myMacroNameHere
If Workbooks.Count = 1 Then
'only this workbook is open
ThisWorkbook.Save
'close the application
'(which will close thisworkbook)
Application.Quit
Else
ThisWorkbook.Close savechanges:=True
End If
End If



Sean wrote:

On Oct 12, 2:15 pm, Dave Peterson wrote:
This line closes the file--no matter what time it was opened.

ThisWorkbook.Close savechanges:=false 'true???

If you don't want the workbook closed, then delete this line.

Sean wrote:

Aaaah I see. Slight tweak if I wanted it saved and closed
automatically when the Schedule Task runs only (i.e. I could still
open it manually) would I place the code like this

End Select

If OkToCallMacro Then
Call myMacroNameHere

ThisWorkbook.Close savechanges:=true
End If

--

Dave Peterson