none
Task scheduler won't run Excel macro RRS feed

  • Question

  • Hello, I'm trying to automate a macro to run each morning at 7:30 (whether I'm there or not) Monday through Friday. I've tried creating a basic task referencing the vbs script I have as well as a creating a regular task. Both have a trigger of running at 7:30 AM every day, the action references the vbs script.

    The vbs script references the macro I need run. The macro is set to run once opened, I've tested the macro, it does work, unfortuantely I just can't get it to run via the task scheduler, I always have to open the macro myself to get it to run. I've attached both the vbs scrip and the maco. Any ideas on what I'm doing wrong?? I've tried re-creating in a new vbs script, noting I do is working.

    When I look at the history of the task scheduler the task is always Terminated about an hour after it was supposed to run.

    <<<<<<<<<VBS Script>>>>>>>>>
    Option Explicit
    
    Dim xl, xlwkbk
    set xl = createobject("Excel.Application")
    set xlwkbk = xl.Workbooks.Open("C:\Users\cwampler\Desktop\Macro Copies\ThptAPOmacro.xlsm")
    xlwkbk.Close false
    xl.Quit
    set xl = nothing
    
    WScript.Quit(0)
    
    
    <<<<<<<<<<<<<Macro>>>>>>>>>>>>>>>>>>>>>>>
    Function ThptAPO()
    Application.ScreenUpdating = True
    Application.DisplayAlerts = False
    
    Workbooks.Open ("S:\3rd Party Co-broker\Invc_Status_Files\Third_Party_APO_" & Format(Date, "yyyy-mm-dd") & ".xlsx")
    Range("A2").Select
    ActiveWindow.FreezePanes = True
    Rows("1:1").Select
    Selection.Replace What:="I_", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="V_", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="_", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    'Code added 06-06-16 to include Client Name column B without changing order of other columns
    Columns("B:B").Select
    Selection.Cut
    Columns("BD:BD").Select
    ActiveSheet.Paste
    Columns("B:B").Delete
    Columns("H:H").Select
    Selection.Cut
    Columns("E:E").Select
    ActiveSheet.Paste
    Columns("H:H").Delete
    Columns("F:F").Delete
    Columns("G:H").Delete
    Columns("A:A").Select
    Selection.Cut
    Columns("I:I").Select
    ActiveSheet.Paste
    Columns("AW:AW").Select
    Selection.Cut
    Columns("A:A").Select
    ActiveSheet.Paste
    Columns("AW:AW").Delete
    Columns("L:O").Delete
    Columns("M:N").Select
    Selection.EntireColumn.Insert
    Columns("AU:AU").Select
    Selection.Cut
    Columns("M:M").Select
    ActiveSheet.Paste
    Columns("AT:AT").Select
    Selection.Cut
    Columns("N:N").Select
    ActiveSheet.Paste
    Columns("O:O").Delete
    Range("V1").Select
    ActiveCell.FormulaR1C1 = "Payable Remaining Amount"
    Range("S1").Select
    ActiveCell.FormulaR1C1 = "Thpt Commission Amount"
    Columns("V:V").Select
    Selection.Cut
    Columns("Q:Q").Select
    ActiveSheet.Paste
    Columns("R:R").Delete
    Columns("S:V").Delete
    Columns("T:AB").Delete
    Columns("U:AB").Delete
    Columns("V:X").Delete
    Range("U1").FormulaR1C1 = "Payables ID"
    Range("A1").FormulaR1C1 = "Aon Affiliation YN Flag"
    Range("B1").FormulaR1C1 = "Market Office Code"
    Range("C1").FormulaR1C1 = "Office Name"
    Range("D1").FormulaR1C1 = "Pay To"
    Range("E1").FormulaR1C1 = "Site"
    Range("F1").FormulaR1C1 = "Payee Name"
    Range("G1").FormulaR1C1 = "Payterm"
    Range("H1").FormulaR1C1 = "Billing Currency"
    Range("I1").FormulaR1C1 = "Client Account ID"
    Range("J1").FormulaR1C1 = "Named Insured"
    Range("K1").FormulaR1C1 = "Policy Number"
    Range("L1").FormulaR1C1 = "Invoice Number"
    Range("M1").FormulaR1C1 = "Payable Hold Status"
    Range("O1").FormulaR1C1 = "Invoice Billing Date"
    Range("P1").FormulaR1C1 = "Trans Eff Date"
    'Range("S1").FormulaR1C1 = "Installment"
    Columns("Q").NumberFormat = "#,##0.00_);[Red](#,##0.00)"
    Columns("R").NumberFormat = "#,##0.00_);[Red](#,##0.00)"
    Columns("L:L").EntireColumn.Insert
    Columns("N:N").Select
    Selection.Cut
    Columns("L:L").Select
    ActiveSheet.Paste
    Columns("N:N").Delete
    Rows("1:1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Cells.Select
    Selection.Columns.AutoFit
    Range("A1").Select
    Selection.AutoFilter
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 10092543
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Font.Bold = True
    Range("A1").Select
    Rows("1:1").RowHeight = 42
    Range("A2").Select
    ActiveWorkbook.Worksheets("Third_Party_APO").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Third_Party_APO").AutoFilter.Sort.SortFields.Add _
        Key:=Range("D2:D100000"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Third_Party_APO").AutoFilter.Sort.SortFields.Add _
        Key:=Range("J2:J100000"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Third_Party_APO").AutoFilter.Sort.SortFields.Add _
        Key:=Range("K2:K100000"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Third_Party_APO").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("I:I").Select
    Selection.TextToColumns Destination:=Range("I1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 2), TrailingMinusNumbers:=True
    Columns("N").NumberFormat = "#,##0.00_);[Red](#,##0.00)"
    Columns("J:J").EntireColumn.Insert
    Columns("W:W").Select
    Selection.Cut
    Columns("J:J").Select
    ActiveSheet.Paste
    'lock invoice number column.
    Columns("V:V").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowInsertingColumns:=True, AllowSorting:=True, AllowFiltering:=True, AllowFormattingColumns:=True
    
    
    ActiveWorkbook.SaveAs Filename:= _
            "https://one.company.net/sites/pasc3rdParty/Shared Documents/Daily APO/Third_Party_APO_" & Format(Date, "mm-dd-yyyy") & ".xlsx"


    Lorac1969

    Tuesday, June 21, 2016 1:37 PM

Answers

  • Hi Lorac1969,

    use the code below as an alternative of Task scheduler.

    but to call the macro you need to open the worksheet everyday and call this macro on worksheet open event. other then there is no way to achieve this using VBA.

    Sub demo()
    Dim sDate As String
    sDate = Format(Now, "ddd")
    If sDate <> "Sun" And sDate <> "Sat" Then
    Application.OnTime TimeValue("07:30:00"), "your macro name"
    
    End If
    End Sub

    call the code mentioned above with the help of Application.OnTime.

    so this code will run everyday and it will check which day is today if it is Sunday or Saturday then your macro will not run but if it is any other day then your macro will be called.

    Regards

    Deepak 

    ,

    ,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, July 5, 2016 8:17 AM
    Moderator

All replies

  • Check the task settings:

    4 squares above:

    1. Change to a user that has the required privileges.
    2. Change as appropriate
    3. Needed?
    4. Set as appropriate


    Best regards, George

    Tuesday, June 21, 2016 2:58 PM
  • Hi George,

    Thanks for replying, it uses my ID, I check the "Run whether user is logged on or not" since I want it to run even when I'm off.

    Run with highest privliges is not checked, but I'll check it and see if it works.

    My company has Windows 7 enterprise, but I've used both the default Windows Vista Windows Server 8 and tried Windows 7 Windows Server 2008 R2.

    Thanks for your help, it is greatly appreciated, I have tried to get the scheduler to run so many times, it's getting discouraging.


    Lorac1969

    Tuesday, June 21, 2016 3:17 PM
  • As a sidenote: I have tried the option "Run whether user is logged on or not" but it has always failed for me. As I don't use the Task Scheduler much I have not dug into the case either.

    Just a hint if you can't get that option to work.


    Best regards, George

    Tuesday, June 21, 2016 3:24 PM
  • I have tried to get the scheduler to run so many times, it's getting discouraging.

    Did you tried to call the macro from the script after the file opened?

    xl.Run "ThptAPO"

    Andreas.

    Tuesday, June 21, 2016 3:42 PM
  • Hi George,

    As you've probably guessed I'm quite new to this, how do you get macros to run automatically if not through Task Scheduler?

    Carol


    Lorac1969

    Tuesday, June 21, 2016 6:41 PM
  • Hi Lorac1969,

    you had asked a question "how do you get macros to run automatically if not through Task Scheduler?"

    Following is the solution you can use.

    Application.OnTime TimeValue("17:00:00"), "my_Procedure"

    it will run the macro at 5 PM.

    For more information please visit the link below.

    Application.OnTime Method (Excel)

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, June 22, 2016 2:07 AM
    Moderator
  • After you setup everything according to your desired preference, the very last thing you need to do is click 'OK' (there are probably a few 'OK' confirmations along the way).  The very last one should prompt you for your machine's password.  That's the credentials you need to run the process when you are away form your machine.  Make sure of two things.

    #1)  Make sure the last step prompts you for your password for the Windows machine that you are using.

    #2)  Make sure you type it correctly.  It will accept an incorrectly typed password, without notifying you of the mistake.  I don't know why Microsoft would even allow this, but they do.

    Try those things and see if it works.  It should.


    MY BOOK

    Wednesday, June 22, 2016 1:07 PM
  • Hi Lorac1969,

    did your issue solved? did any of the given suggestions helped you to solve your issue?

    please let us know.

    because you had mentioned that your posted macro is working properly so the issue is regarding Task Scheduler.

    the issue related with Task Scheduler did not supported by our forum.

    so I have suggested you an another way to use Application.OnTime to call macro automatically on predefined time.

    did it solves your issue?

    if you only want to correct the issue related with Task Scheduler then let us know so that we can try to move this thread to other forum. this forum only support issues related with Excel Object Model.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, June 23, 2016 3:51 AM
    Moderator
  • Hi Lorac1969,

    use the code below as an alternative of Task scheduler.

    but to call the macro you need to open the worksheet everyday and call this macro on worksheet open event. other then there is no way to achieve this using VBA.

    Sub demo()
    Dim sDate As String
    sDate = Format(Now, "ddd")
    If sDate <> "Sun" And sDate <> "Sat" Then
    Application.OnTime TimeValue("07:30:00"), "your macro name"
    
    End If
    End Sub

    call the code mentioned above with the help of Application.OnTime.

    so this code will run everyday and it will check which day is today if it is Sunday or Saturday then your macro will not run but if it is any other day then your macro will be called.

    Regards

    Deepak 

    ,

    ,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, July 5, 2016 8:17 AM
    Moderator