Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2515
  • Last Modified:

Application.OnTime not working!

Hi Guys, I am trying to figure out why my Application.Ontime Code is not working. I leave my Workbook in Excel open overnight and expect it to run at 8am in the morning, but it doesn't.

Here's the code:

Sub Workbook_Open()
'Application.OnTime TimeSerial(16, 59, 0), "Macro5"      'Run the Import macro at 8 AM
Application.OnTime TimeValue("08:00:00"), "Macro5"

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

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
    Workbooks.OpenText Filename:= _
        "V:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS\ALMBSRec_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
    Workbooks.OpenText Filename:= _
0
Justincut
Asked:
Justincut
  • 4
  • 3
1 Solution
 
Steven HarrisPresidentCommented:
Try using Workbook_Open to call another procedure, not the function itself.

Change the time to something current for a test, save and open the workbook, then wait and see what happens.

Sub Workbook_Open()
     RunTime
End Sub

Sub RunTime()
     Application.OnTime TimeValue("08:00:00"), "Macro5"
End Sub

Sub Macro5()
     'your code here
End Sub

Open in new window

0
 
JustincutAuthor Commented:
I leave the Spreadsheet open when I leave the office.The Macro should go off at 8am, 30 mins before I arrive in the office.
0
 
Steven HarrisPresidentCommented:
I leave the Spreadsheet open when I leave the office.The Macro should go off at 8am, 30 mins before I arrive in the office.

Have you tried the above suggestion?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
JustincutAuthor Commented:
Yep. I changed the time to now and its not working.

Sub Workbook_Open()
Runtime
End Sub

Sub WorkbookOpen() 'Application.OnTime TimeSerial(16, 59, 0), "Macro5"      'Run the Import macro at 8 AM
Application.OnTime TimeValue("11:27:00"), "Macro5"

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

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")
0
 
Steven HarrisPresidentCommented:
From your code pasted, it doesn't seem to be formatted correctly...  Can you verify that you added a new Sub called RunTime() as shown below?  You are showing two Workbook_Open events, not just one as I mentioned.

Sub Workbook_Open()
     RunTime
End Sub

Sub RunTime()
    'Application.OnTime TimeSerial(16, 59, 0), "Macro5"      'Run the Import macro at 8 AM
     Application.OnTime TimeValue("08:00:00"), "Macro5"
End Sub

Open in new window

0
 
JustincutAuthor Commented:
Can I just double check? I leave my Excel spreadsheet open overnight in order that it works,no?
0
 
Steven HarrisPresidentCommented:
I leave my Excel spreadsheet open overnight in order that it works,no?

That is fine if it is formatted correctly.  From what you pasted of your code, you had two Workbook_Open events, there should only be one.

Is it possible for you to attach your worksheet, or paste your entire code into a txt document and then upload it here?
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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