Solved

Application.OnTime not working!

Posted on 2013-10-29
7
2,185 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

623 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