Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

how do I repeat an action every day

Discussion in 'Information Technology' started by mike, Nov 11, 2005.

  1. mike

    mike
    Expand Collapse
    Guest

    I export information via email every day to various customers (when I
    remember!) I use a macro to send the results of a query output as an excel
    spreadsheet and it works fine but I forget to do it I need to automate the
    macro to run say every 24 hours and then to OK the "do you want to send"
    question I get
     
  2. Loading...


  3. Ron Hinds

    Ron Hinds
    Expand Collapse
    Guest

    I've solved a similar problem by using the Windows Task Scheduler to start
    my Access app with a command-line switch (/cmd auto). Then, in the Open
    event of my main form, I check for the "auto" switch like so:

    If Command = "auto" Then
    ....
    End If

    At this point I set a global variable of type Boolean to True and then run
    the code. In the code to be run, I check the global each time I might be
    showing a user prompt, etc. and skip the prompt if it is True. After the
    code is run, I exit the Access application. It works like a champ so far (>
    6 months) at nearly 500 installations in the field!

    "mike" <mike@discussions.microsoft.com> wrote in message
    news:F57F2EFF-2D5B-4158-95E3-8297E0AA0813@microsoft.com...
    > I export information via email every day to various customers (when I
    > remember!) I use a macro to send the results of a query output as an excel
    > spreadsheet and it works fine but I forget to do it I need to automate the
    > macro to run say every 24 hours and then to OK the "do you want to send"
    > question I get
     
  4. mike

    mike
    Expand Collapse
    Guest

    Thanks for your help, I don't think I'm clever enough to understand it
    perhaps its going to be too complicated for me
    Mike

    "Ron Hinds" wrote:

    > I've solved a similar problem by using the Windows Task Scheduler to start
    > my Access app with a command-line switch (/cmd auto). Then, in the Open
    > event of my main form, I check for the "auto" switch like so:
    >
    > If Command = "auto" Then
    > ....
    > End If
    >
    > At this point I set a global variable of type Boolean to True and then run
    > the code. In the code to be run, I check the global each time I might be
    > showing a user prompt, etc. and skip the prompt if it is True. After the
    > code is run, I exit the Access application. It works like a champ so far (>
    > 6 months) at nearly 500 installations in the field!
    >
    > "mike" <mike@discussions.microsoft.com> wrote in message
    > news:F57F2EFF-2D5B-4158-95E3-8297E0AA0813@microsoft.com...
    > > I export information via email every day to various customers (when I
    > > remember!) I use a macro to send the results of a query output as an excel
    > > spreadsheet and it works fine but I forget to do it I need to automate the
    > > macro to run say every 24 hours and then to OK the "do you want to send"
    > > question I get

    >
    >
    >
     
  5. mike

    mike
    Expand Collapse
    Guest

    Its mike again, I think you mean I start access via task scheduler at the
    time I want to send the email and then find out if access was started
    automatically if so send the email and exit the programme - brilliant I'll
    try to work out the details but if I have no joy I'll post a help me message
    Thanks


    "mike" wrote:

    > Thanks for your help, I don't think I'm clever enough to understand it
    > perhaps its going to be too complicated for me
    > Mike
    >
    > "Ron Hinds" wrote:
    >
    > > I've solved a similar problem by using the Windows Task Scheduler to start
    > > my Access app with a command-line switch (/cmd auto). Then, in the Open
    > > event of my main form, I check for the "auto" switch like so:
    > >
    > > If Command = "auto" Then
    > > ....
    > > End If
    > >
    > > At this point I set a global variable of type Boolean to True and then run
    > > the code. In the code to be run, I check the global each time I might be
    > > showing a user prompt, etc. and skip the prompt if it is True. After the
    > > code is run, I exit the Access application. It works like a champ so far (>
    > > 6 months) at nearly 500 installations in the field!
    > >
    > > "mike" <mike@discussions.microsoft.com> wrote in message
    > > news:F57F2EFF-2D5B-4158-95E3-8297E0AA0813@microsoft.com...
    > > > I export information via email every day to various customers (when I
    > > > remember!) I use a macro to send the results of a query output as an excel
    > > > spreadsheet and it works fine but I forget to do it I need to automate the
    > > > macro to run say every 24 hours and then to OK the "do you want to send"
    > > > question I get

    > >
    > >
    > >
     
  6. Ron Hinds

    Ron Hinds
    Expand Collapse
    Guest

    Hi Mike - yes, that's exactly what I mean. In the Task Scheduler command add
    "/cmd auto" after the database name. So your command string in Task
    Scheduler might look like:

    "C:\Program Files\Microsoft Office\Office\msaccess.exe"
    "path_and_filename_of_your_mdb_or_mde" /cmd auto

    Next, in the Open event of your main form, check for the "auto" command line
    switch like so:

    If Command = "auto" Then
    'Set a global Boolean variable
    gboolAutoRun = True
    'Now run your macro
    DoCmd.RunMacro "your_macro_name"
    'Now quit Access
    Application.Quit acQuitSaveNone
    Else
    gboolAutoRun = False
    End If

    Be sure to modify your macro so that it skips any dialog boxes or any other
    code that might require user intervention if gboolAutoRun is True, since no
    one will be there to clik "OK" or whatever! For instance, I cange all of my
    error handlers to write to a log file instead of displaying a message box.
    Here is the LogError routine:

    Public Sub LogError(ModName As String, Number As Long, Desc As String,
    Optional FileName As String)
    'Log error number, description, and module/routine that generated the error
    'to a text file, optionally with filename that caused error
    On Error Resume Next 'Can't popup a MsgBox because this is
    auto-execute

    Dim strDateTime As String
    Dim strLogDir As String
    Dim strLogFile As String
    Dim iFileNo As Integer

    'Create the log directory if it doesn't exist
    strLogDir = "C:\Logs"
    If Dir(strLogDir, vbDirectory) = "" Then MkDir strLogDir

    'Create new log filename every day
    strLogFile = strLogDir & "\Log" & Format(Now(), "mmddyy") & ".txt"
    strDateTime = Format(Now(), "mm-dd-yy hh:mm:ss")
    iFileNo = FreeFile

    Open strLogFile For Append Access Write Lock Write As #iFileNo

    If Len(FileName) > 0 Then
    Print #iFileNo, strDateTime; Spc(4); ModName; Spc(4); "Error " &
    Number; _
    Spc(4); Desc; Spc(4); FileName
    Else
    Print #iFileNo, strDateTime; Spc(4); ModName; Spc(4); "Error " &
    Number; _
    Spc(4); Desc
    End If

    Close #iFileNo

    End Sub

    "mike" <mike@discussions.microsoft.com> wrote in message
    news:A0ED834E-3678-4524-8F04-8289DBEDA2B2@microsoft.com...
    > Its mike again, I think you mean I start access via task scheduler at the
    > time I want to send the email and then find out if access was started
    > automatically if so send the email and exit the programme - brilliant I'll
    > try to work out the details but if I have no joy I'll post a help me

    message
    > Thanks
    >
    >
    > "mike" wrote:
    >
    > > Thanks for your help, I don't think I'm clever enough to understand it
    > > perhaps its going to be too complicated for me
    > > Mike
    > >
    > > "Ron Hinds" wrote:
    > >
    > > > I've solved a similar problem by using the Windows Task Scheduler to

    start
    > > > my Access app with a command-line switch (/cmd auto). Then, in the

    Open
    > > > event of my main form, I check for the "auto" switch like so:
    > > >
    > > > If Command = "auto" Then
    > > > ....
    > > > End If
    > > >
    > > > At this point I set a global variable of type Boolean to True and then

    run
    > > > the code. In the code to be run, I check the global each time I might

    be
    > > > showing a user prompt, etc. and skip the prompt if it is True. After

    the
    > > > code is run, I exit the Access application. It works like a champ so

    far (>
    > > > 6 months) at nearly 500 installations in the field!
    > > >
    > > > "mike" <mike@discussions.microsoft.com> wrote in message
    > > > news:F57F2EFF-2D5B-4158-95E3-8297E0AA0813@microsoft.com...
    > > > > I export information via email every day to various customers (when

    I
    > > > > remember!) I use a macro to send the results of a query output as an

    excel
    > > > > spreadsheet and it works fine but I forget to do it I need to

    automate the
    > > > > macro to run say every 24 hours and then to OK the "do you want to

    send"
    > > > > question I get
    > > >
    > > >
    > > >
     
  7. mike

    mike
    Expand Collapse
    Guest

    High Ron
    Thanks very much that's brilliant and very nicely explained I really
    appreciate your help
    Mike

    "Ron Hinds" wrote:

    > Hi Mike - yes, that's exactly what I mean. In the Task Scheduler command add
    > "/cmd auto" after the database name. So your command string in Task
    > Scheduler might look like:
    >
    > "C:\Program Files\Microsoft Office\Office\msaccess.exe"
    > "path_and_filename_of_your_mdb_or_mde" /cmd auto
    >
    > Next, in the Open event of your main form, check for the "auto" command line
    > switch like so:
    >
    > If Command = "auto" Then
    > 'Set a global Boolean variable
    > gboolAutoRun = True
    > 'Now run your macro
    > DoCmd.RunMacro "your_macro_name"
    > 'Now quit Access
    > Application.Quit acQuitSaveNone
    > Else
    > gboolAutoRun = False
    > End If
    >
    > Be sure to modify your macro so that it skips any dialog boxes or any other
    > code that might require user intervention if gboolAutoRun is True, since no
    > one will be there to clik "OK" or whatever! For instance, I cange all of my
    > error handlers to write to a log file instead of displaying a message box.
    > Here is the LogError routine:
    >
    > Public Sub LogError(ModName As String, Number As Long, Desc As String,
    > Optional FileName As String)
    > 'Log error number, description, and module/routine that generated the error
    > 'to a text file, optionally with filename that caused error
    > On Error Resume Next 'Can't popup a MsgBox because this is
    > auto-execute
    >
    > Dim strDateTime As String
    > Dim strLogDir As String
    > Dim strLogFile As String
    > Dim iFileNo As Integer
    >
    > 'Create the log directory if it doesn't exist
    > strLogDir = "C:\Logs"
    > If Dir(strLogDir, vbDirectory) = "" Then MkDir strLogDir
    >
    > 'Create new log filename every day
    > strLogFile = strLogDir & "\Log" & Format(Now(), "mmddyy") & ".txt"
    > strDateTime = Format(Now(), "mm-dd-yy hh:mm:ss")
    > iFileNo = FreeFile
    >
    > Open strLogFile For Append Access Write Lock Write As #iFileNo
    >
    > If Len(FileName) > 0 Then
    > Print #iFileNo, strDateTime; Spc(4); ModName; Spc(4); "Error " &
    > Number; _
    > Spc(4); Desc; Spc(4); FileName
    > Else
    > Print #iFileNo, strDateTime; Spc(4); ModName; Spc(4); "Error " &
    > Number; _
    > Spc(4); Desc
    > End If
    >
    > Close #iFileNo
    >
    > End Sub
    >
    > "mike" <mike@discussions.microsoft.com> wrote in message
    > news:A0ED834E-3678-4524-8F04-8289DBEDA2B2@microsoft.com...
    > > Its mike again, I think you mean I start access via task scheduler at the
    > > time I want to send the email and then find out if access was started
    > > automatically if so send the email and exit the programme - brilliant I'll
    > > try to work out the details but if I have no joy I'll post a help me

    > message
    > > Thanks
    > >
    > >
    > > "mike" wrote:
    > >
    > > > Thanks for your help, I don't think I'm clever enough to understand it
    > > > perhaps its going to be too complicated for me
    > > > Mike
    > > >
    > > > "Ron Hinds" wrote:
    > > >
    > > > > I've solved a similar problem by using the Windows Task Scheduler to

    > start
    > > > > my Access app with a command-line switch (/cmd auto). Then, in the

    > Open
    > > > > event of my main form, I check for the "auto" switch like so:
    > > > >
    > > > > If Command = "auto" Then
    > > > > ....
    > > > > End If
    > > > >
    > > > > At this point I set a global variable of type Boolean to True and then

    > run
    > > > > the code. In the code to be run, I check the global each time I might

    > be
    > > > > showing a user prompt, etc. and skip the prompt if it is True. After

    > the
    > > > > code is run, I exit the Access application. It works like a champ so

    > far (>
    > > > > 6 months) at nearly 500 installations in the field!
    > > > >
    > > > > "mike" <mike@discussions.microsoft.com> wrote in message
    > > > > news:F57F2EFF-2D5B-4158-95E3-8297E0AA0813@microsoft.com...
    > > > > > I export information via email every day to various customers (when

    > I
    > > > > > remember!) I use a macro to send the results of a query output as an

    > excel
    > > > > > spreadsheet and it works fine but I forget to do it I need to

    > automate the
    > > > > > macro to run say every 24 hours and then to OK the "do you want to

    > send"
    > > > > > question I get
    > > > >
    > > > >
    > > > >

    >
    >
    >
     

Share This Page