Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm trying to automate a process. Part of my plans is to have an Excel sheet run scheduled (from Windows Task-scheduler).

    When the sheet is executed from the scheduler, I want it to auto-run, do its work unattended and auto-close (no big deal, with Auto_Open that is easy). However, I want to see if I can prevent the/a macro from running when I open the workbook manually (e.g. to edit it).

    I thought about calling the workbook from the Scheduler with a command-line argument. There doesn't seem to be a logical way here although there is an option to open a sheet 'read-only', so my auto-open could maybe test to see if the sheet is read-only and only proceed if it is.

    A work-around could be to program a startup dialog with a count-down: if the user doesn't click something, the process will continue (easy, I can do that too).

    Are there better and/or more elegant ideas to do this maybe??

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can disable automatic macros by holding down the Shift key while opening the workbook.

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    You can disable automatic macros by holding down the Shift key while opening the workbook.
    Yeah, I knew that (but had forgotten it), it does work of course but I'm afraid that's not what I'm looking for. It should be fool proof (he... I'm a perfectionist, can't help it )

  4. #4
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by ErikJan View Post
    When the sheet is executed from the scheduler, I want it to auto-run, do its work unattended and auto-close ....
    Is the scheduler set to run at a particular time that the on-open macro could check for, and if so, only then do its business?

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    You can disable automatic macros by holding down the Shift key while opening the workbook.
    Not for Excel 2007 unfortunately.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by pieterse View Post
    Not for Excel 2007 unfortunately.
    I'm stil using 2003... do I understand from your answer the are possibilities in Excel 2010 then (just curious)

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Quote Originally Posted by pieterse View Post
    Not for Excel 2007 unfortunately.
    One more reason not to use Excel 2007, grrrr...

  8. #8
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Leif View Post
    Is the scheduler set to run at a particular time that the on-open macro could check for, and if so, only then do its business?
    Going with this one for now... nice idea!

  9. #9
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Erik

    As a matter of closure on this I thought I would just add some code as an example of what Leif suggest, this bit of code was designed to accept code runs on open between 21:40 and 21:50 hrs

    Code:
    Private Sub Workbook_Open()
    
    Dim dtTime As Date
    
    dtTime = TimeValue(Now())
    
    	If dtTime > TimeValue("21:40:00") And dtTime < TimeValue("21:50:00") Then
    
     	MsgBox "Run Code Now" 'Replace with Function or Code
    
    	Else
     	
     	'Do Nothing and End Sub
    	
    	End If
    
    End Sub
    Jerry

  10. #10
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by ErikJan View Post
    Going with this one for now... nice idea!
    If you do not wish to be tied to a particular time restriction you could:
    • Place the main code in a Standard module of the main workbook, This code will not run automatically on opening the workbook.
    • Create a 'trigger workbook' which is opened by the scheduler and contains an auto macro that calls the main code in the main workbook.
    Regards
    Don

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by ErikJan View Post
    I'm stil using 2003... do I understand from your answer the are possibilities in Excel 2010 then (just curious)
    I don't know if this will be fixed in 2010.

    ###EDIT###
    Just checked in the current Beta: Although cunningly hidden by MSFT, you can open a workbook with macro's and prevent the workbook_Open sub from running, provided you hold the shift key all the way starting from when you click the file unitl you click enable.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by wdwells View Post
    If you do not wish to be tied to a particular time restriction you could:
    • Place the main code in a Standard module of the main workbook, This code will not run automatically on opening the workbook.
    • Create a 'trigger workbook' which is opened by the scheduler and contains an auto macro that calls the main code in the main workbook.
    That's a much smarter idea - I can use that myself!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •