Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a Macro on Workbook Open Q
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a Macro on Workbook Open Q
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... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a Macro on Workbook Open Q
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a Macro on Workbook Open Q
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a Macro on Workbook Open Q
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a Macro on Workbook Open Q
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a Macro on Workbook Open Q
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a Macro on Workbook Open Q
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a Macro on Workbook Open Q
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a Macro on Workbook Open Q
Thanks Dave that seems to work great. One final question, is it
possible to run this instance of Excel minimised? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a Macro on Workbook Open Q
On Oct 12, 5:43 pm, Dave Peterson wrote:
You could use: Application.WindowState = xlMinimized You may want to add a test to see if there are other workbooks open, too???? Sean wrote: Thanks Dave that seems to work great. One final question, is it possible to run this instance of Excel minimised? -- Dave Peterson 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' |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a Macro on Workbook Open Q
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 wrote: On Oct 12, 5:43 pm, Dave Peterson wrote: You could use: Application.WindowState = xlMinimized You may want to add a test to see if there are other workbooks open, too???? Sean wrote: Thanks Dave that seems to work great. One final question, is it possible to run this instance of Excel minimised? -- Dave Peterson 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a Macro on Workbook Open Q
Yes I assume it was open under a different instance, but doesn't the
code you wrote create a fresh instance anyway? |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a Macro on Workbook Open Q
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 wrote: Yes I assume it was open under a different instance, but doesn't the code you wrote create a fresh instance anyway? -- Dave Peterson |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a Macro on Workbook Open Q
On Oct 13, 1:23 am, Dave Peterson wrote:
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 wrote: Yes I assume it was open under a different instance, but doesn't the code you wrote create a fresh instance anyway? -- Dave Peterson 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open workbook macro- find correct month to open? | Excel Programming | |||
open workbook from a macro and continue this macro afterwards | Excel Programming | |||
Open WorkBook & Run Macro | Excel Programming | |||
How to Run Macro in open workbook/s | Excel Discussion (Misc queries) | |||
how can I run a macro when I open a workbook? | Excel Discussion (Misc queries) |