Solved

Application.OnTime not working!

Posted on 2013-10-29
7
1,835 Views
Last Modified: 2013-11-05
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
Comment
Question by:Justincut
  • 4
  • 3
7 Comments
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39609447
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
 

Author Comment

by:Justincut
ID: 39612184
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
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39612254
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Justincut
ID: 39613755
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
 
LVL 18

Accepted Solution

by:
Steven Harris earned 500 total points
ID: 39613840
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
 

Author Comment

by:Justincut
ID: 39617491
Can I just double check? I leave my Excel spreadsheet open overnight in order that it works,no?
0
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39617765
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now