Solved

Application.OnTime not working!

Posted on 2013-10-29
7
1,785 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

705 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

23 Experts available now in Live!

Get 1:1 Help Now