?
Solved

Application On Time Not working

Posted on 2013-11-20
7
Medium Priority
?
285 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
[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
  • 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 49

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 49

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 49

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
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.

777 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