Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Scheduling Macros

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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!

    Comment


    • #3
      Re: Scheduling Macros

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


      Code:
      '   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 ([email protected])
      
      '   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!

      Comment


      • #4
        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
        Code:
        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)...
        Code:
        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, 14:41.

        Comment


        • #5
          Re: Scheduling Macros - Solved



          Thank You all

          Comment

          Trending

          Collapse

          There are no results that meet this criteria.

          Working...
          X