Solved

Application On Time Not working

Posted on 2013-11-20
7
248 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 45

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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 45

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 45

Accepted Solution

by:
Martin Liss earned 500 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
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…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

762 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

21 Experts available now in Live!

Get 1:1 Help Now