[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Application On Time Not working

Posted on 2013-11-20
7
Medium Priority
?
288 Views
Last Modified: 2013-11-20
Hi Guys, I have a Macro that I want to run at 8pm before I get into work so I leave my Spreadsheet open over night with Application OnTime in the code so it triggers "Macro 5" but its not working any ideas why? Here's my code:

Sub Workbook_Open()
Runtime
End Sub

Sub Runtime()
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 = WorksheetFunction.WorkDay(Date, -1)

Prevday = Format(Prevday, "DDMMYY")

Prevday2 = WorksheetFunction.WorkDay(Date, -1)


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
0
Comment
Question by:Justincut
  • 3
  • 2
  • 2
7 Comments
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39662426
0
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39662436
And just to follow-up:

After you have a adjusted the code, are you saving the workbook in .xlsm format, closing the workbook, and then opening the workbook?

-If the file is not saved, it is not active.
-If you do not close and reopen the workbook, the code is not active from Workbook_Open().
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 39662512
How do you know Macro5 isn't being run?

In any case as a test,
1) Add the "Stop" line (line 8) in the macro as shown below,
2) Change the Application.OnTime TimeValue("08:00:00"), "Macro5" to a time a minute or so from the current time, and
3) Go to the Workbook_Open code, select any line in that code and press F5.

When the clock reaches the time you entered, the macro should run and then pause at the Stop line

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

Stop ' <=== Add this line

Prevday = WorksheetFunction.WorkDay(Date, -1)

' the rest of the code

Open in new window

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: 39662581
I have been saving it before I leave the office, but not closing it and opening it. Is that the problem?
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 39662591
Probably but you don't have to do that. Juts run the Runtime macro each evening and leave the workbook open.
0
 

Author Comment

by:Justincut
ID: 39662850
I thought that you don't have to physically run it, no? You just mean save the spreadsheet and leave the spreadsheet open?
0
 
LVL 50

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 39662883
If you close your workbook and then reopen it, the Workbook_Open sub calls the 'Runtime' macro which kicks off the process that monitors the clock. What I'm saying is that you don't have to close and reopen the workbook in order to run the 'Runtime' macro. You can instead run it directly just like you can any other macro.
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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

873 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