Run a Macro on Workbook Open Q

[Sean]

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 are:

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

 

[crferguson]

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...

 

[Dave Peterson]

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]

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










--

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!

 

[Dave Peterson]

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:

 

[Sean]

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

 

[Sean]

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

 

[Sean]

When I run a schedule task to open my file and Execute the macro, when
the file closes, it leaves an instance of Excel open. Is there a way
to close this instance of Excel?

If I happened to have another Excel file open when this Schedule task
is running I would not want this to be closed, just the new instance
created by the scheduled task

 

[Dave Peterson]

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]

Thanks Dave that seems to work great. One final question, is it
possible to run this instance of Excel minimised?

 

[Dave Peterson]

You could use:

Application.WindowState = xlMinimized

You may want to add a test to see if there are other workbooks open, too????

 

[Sean]

You could use:

Application.WindowState = xlMinimized

You may want to add a test to see if there are other workbooks open, too????

Thanks Dave, that pretty much works, I had another file open but it
didn't minimise that. The only thing that happens now is the screen
'jumps' a bit, I've tried application.screenupdating=false, it doesn't
quite work for me. I have a routine that places vb code in the
"thisWorksheet" and e-mails out and the screen jumps into the vb
editor for a second or two.

What would be the effects if I just set application.screenupdating to
false in all my code, as there are sub macro's envoked which sets it
at True at the end of them. Not really sure what the function does,
apart from stopping the screen 'blinking'

 

[Dave Peterson]

That's what .screenupdating = false does--stop the screen from flickering.

I'm surprised that it didn't work for you.

There are a minor few things that you could be doing that toggle the setting (I
think calling some routines from the analysis toolpak for vba(???) -- but I
don't remember the details).

If that's the problem, you could find out what code is toggling the setting and
turn it off again right after that code.

And that line should minimize the excel application--maybe your other workbook
was open in a different instance of excel??????

 

[Sean]

Yes I assume it was open under a different instance, but doesn't the
code you wrote create a fresh instance anyway?

 

[Dave Peterson]

Nope.

The code in the workbook doesn't know anything about what instance it's running
under.

The instance of excel depends on how you started excel (or your workbook).

If you open excel and then double click on a .xls file in windows explorer, do
you get another instance of excel, or does your workbook open in the existing
instance?

 

[Sean]

Nope.

The code in the workbook doesn't know anything about what instance it's running
under.

The instance of excel depends on how you started excel (or your workbook).

If you open excel and then double click on a .xls file in windows explorer, do
you get another instance of excel, or does your workbook open in the existing
instance?

If I double click on an Excel file in Win explorer a new instance
doesn't open up. I guess then the way Schedule task opens my specified
file, it opens a new instance, if so its perfect for me, as I wish to
minimise the schedule task instance, not one I maybe working on at the
time

Thanks for yor help Dave