Solved

Application On Time Not working

Posted on 2013-11-20
7
276 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 46

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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

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 46

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 46

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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

Suggested Solutions

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

791 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