• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

Application OnTime not working

Hi Guys, I am experimenting with Application.Ontime for the first time, but its not working. I want it to work everyday. Anyone see anything wrong with it?

Sub Workbook_Open()

Application.OnTime TimeSerial(8, 0, 0), "Macro5"    'Run the Import macro at 8 AM

End Sub

Sub Macro5()

Dim target As Range, target1 As Range, target2 As Range, target3 As Range, target4 As Range, target5 As Range, target6 As Range, target7 As Range, target8 As Range, target9 As Range, target10 As Range, target11 As Range
Dim PrevDay, Prevday2 As String

PrevDay = Worksheets("Rec").Range("AM1").Value
PrevDay = Format(PrevDay, "DDMMYY")

Prevday2 = Worksheets("Rec").Range("AM1").Value

Prevday2 = Format(Prevday2, "YYYYMMDD")

    Workbooks.OpenText Filename:= _
        "V:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS\StructNotesBSRec_Daily_" & "*.txt" _
        , Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
        Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _
        , 1), Array(16, 1), Array(17, 1), Array(18, 1)), TrailingMinusNumbers:=True
  • 2
1 Solution

it is probably the same

pls try

Application.OnTime TimeValue("8:00:00"), "Macro5" 

Open in new window

JustincutAuthor Commented:
Can you tell me how to use it for 8am in the morning Monday to Friday, but not at the weekend?

You could put in the beginning of the  Macro5

this code

If Weekday(Date, vbMonday) > 5 Then Exit Sub

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now