Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Scheduling Macros

  1. #1
    Join Date
    22nd February 2006
    Posts
    75

    Scheduling Macros

    Hi,

    I have a macro that I want to run on an hourly basis, say at 9am, 10am, etc whenever the workbook is open.

    This needs to be definable as I have several users that will be conducting the same activity throughout the day. In other words I want the macro to run on the hour for user 1, quarter past the hour for user 2, half past for user 3 and quarter to for user 4.

    Each user does have their own unique macro to run so it should (hopefully) just mean introducing a line of code to each, defining the time to run......

    Thanks,

    Alec

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    22nd January 2004
    Location
    Colorado Springs
    Posts
    3,956

    Re: Scheduling Macros

    Alec,

    You will need to a WinApi to get the login name. Once you have that then based on that name you can schedule the macros as you see fit.


    I've got some updates running on my PC and it'll take at least another 10 minutes till I can get into excel to retrieve the win api code.
    Regards,
    Barry

    My Favorite New Thing:
    Dynamic Named Ranges



    The alternative for
    "Press Any Key To Continue."

    and we all have one we'd like to use it on


    1. Cross Posting Etiquette
    2. Are You Here To Learn: What Have You Tried?
    3. Your Best Resource to begin learning VBA: RECORD A MACRO . . . Then Study It!

  3. #3
    Join Date
    22nd January 2004
    Location
    Colorado Springs
    Posts
    3,956

    Re: Scheduling Macros

    Here is the code that I have to get the system login username.


    VB:
     '   functions / routines this module created / last modified _
    1/9/03 
     
    Option Explicit 
     
     '   All code this page are Windows API calls
     '   All code this page not my source code code all from outside _
    sources 
     
    Private Const MAX_COMPUTERNAME_LENGTH As Long = 31 
     
    Private Declare Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long 
    Private Declare Function GetUserName& Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) 
     
    Public Function UserName() 
        Dim strUserName As String ' Declare the buffer to hole the username
        Dim lngSize As Long ' This holds to size of the buffer
        Dim blnStatus As Boolean ' Declare variable to get success status
         
         ' initialize the size of the buffer
        lngSize = 255 
         ' initialize the buffer that will hold the username
        strUserName = Space(lngSize) 
         
         ' The actually API call to get the username
        blnStatus = GetUserName(strUserName, lngSize) 
         ' After the API call the variable lngSize contains the length of the
         ' Username returned by the call into the variable strUserName.
         
         ' GetUserName will return False if it fails
        If blnStatus = False Then 
            MsgBox "The Call Failed!" 
            Exit Function 
        End If 
         
        UserName = UCase(Mid(strUserName, 1, lngSize - 1)) 
         
         
    End Function 
     
    Public Function PCName() As String 
         'example by Donavon Kuhn (Donavon.Kuhn@Nextel.com)
         
         '   Local Variables
        Dim dwLen As Long 
        Dim strString As String 
         
         'Create a buffer
        dwLen = MAX_COMPUTERNAME_LENGTH + 1 
        strString = String(dwLen, "X") 
         'Get the computer name
        GetComputerName strString, dwLen 
         'get only the actual data
        strString = Mid(strString, 1, dwLen) 
         'Show the computer name
        PCName = strString 
         
    End Function 
    
    
    Regards,
    Barry

    My Favorite New Thing:
    Dynamic Named Ranges



    The alternative for
    "Press Any Key To Continue."

    and we all have one we'd like to use it on


    1. Cross Posting Etiquette
    2. Are You Here To Learn: What Have You Tried?
    3. Your Best Resource to begin learning VBA: RECORD A MACRO . . . Then Study It!

  4. #4
    Join Date
    1st November 2004
    Location
    Wellington, New Zealand
    Posts
    84

    Re: Scheduling Macros

    Hi Alec, you can schedule a macro using the OnTime method. See Chip Pearson's page for a good explanation of using it. On that same page it also has the option to use WinAPI if you want. I believe you can just use the Environ statement to get the username but if this isnt sufficient you can try a WinAPI to get the username from here <<oops I see Barry's already given you an API :-) >>

    To kick off the macro you need to use the Workbook_Open event located in the ThisWorkbook module. In this procedure you will need to determine the current time then run the particular procedure for that user at 15 minutes past the hour etc. The procedure for that user will have an OnTime line on the last line to run the procedure again in another hour so the procedure is recursive.

    The following is an example (untested)...

    In the ThisWorkbook module
    VB:
    Private Sub Workbook_Open() 
        Dim CurMin As Integer, CurHr As Integer 
        Dim StartTime As Date 
         
        CurMin = CInt(Right(Format(Time, "HH:MM"), 2)) 'current minute
        CurHr = CInt(Left(Format(Time, "HH:MM"), 2)) 'current hour
         
        Select Case Environ("Username") 
        Case "Alec" 
             'Run macro at HH:15
            If CurMin = 15 Then 
                Call Alecs_Macro 'its HH:15 now so kick it off
            ElseIf CurMin > 15 Then 
                If CurHr < 23 Then 
                     'if its past 15 mins & Hour < 23 then the time starting
                     'will be the same day and next hour
                    StartTime = TimeValue(CurHr + 1 & ":15:00") 
                    Application.OnTime StartTime, "Alecs_Macro" 
                Else 
                     'if its past HH:15 then the time starting will be the
                     'next day at 12:15AM
                    StartTime = TimeValue("00:15:00") 
                    Application.OnTime StartTime, "Alecs_Macro" 
                End If 
            Else 
                 'if minute is less than 15 then the time starting will be the
                 'same hour
                StartTime = TimeValue(CurHr & ":15:00") 
                Application.OnTime StartTime, "Alecs_Macro" 
            End If 
             
        End Select 
         
    End Sub 
    
    
    In a standard module (eg Module1)...
    VB:
    Sub Alecs_Macro() 
         'whatever code you want to run for Alec
         
        MsgBox "Alecs_Macro has just run" 'example code
         
         'Start procedure again in an hours time
        Application.OnTime Now + TimeValue("1:00:00") 
    End Sub 
    
    
    Edit: Missed out TimeValue. You will need to test time at 00:15 to see if you need to add the day as well - may need to.
    Last edited by parry; April 14th, 2006 at 14:41.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    22nd February 2006
    Posts
    75

    Re: Scheduling Macros - Solved



    Thank You all

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Scheduling Macros
    By Utpal in forum EXCEL HELP
    Replies: 1
    Last Post: February 16th, 2007, 23:09
  2. Scheduling Ontime
    By VBA Beginner in forum EXCEL HELP
    Replies: 2
    Last Post: February 15th, 2007, 03:07
  3. Code For Scheduling
    By flo123 in forum EXCEL HELP
    Replies: 4
    Last Post: February 14th, 2007, 02:20
  4. Scheduling: Schedule Best Fit
    By Vince Molina in forum EXCEL HELP
    Replies: 2
    Last Post: October 5th, 2006, 14:53
  5. Scheduling Macros in Access - VBA
    By msjacks in forum Excel and/or Access Help
    Replies: 1
    Last Post: May 31st, 2006, 00:24

Bookmarks

Posting Permissions

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