Solved

Application On Time Not working

Posted on 2013-11-20
7
280 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 47

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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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 47

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 47

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

696 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