Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Application.OnTime not working!

Posted on 2013-10-29
7
Medium Priority
?
2,319 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
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…

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