Solved

TASK SCHEDULER PROBLEM

Posted on 2007-11-21
10
166 Views
Last Modified: 2010-04-13
Hello,

I have a task scheduler that is set to run every morning before I get into work. It is designed to close down any excel spreadsheets that may be open. When I arrive and check I can see that it has not worked but when I change the time and watch to see if the task scheduler works it does. Please could someone advise?

Thanks,

f19l
0
Comment
Question by:f19l
  • 5
  • 5
10 Comments
 
LVL 12

Expert Comment

by:RubenvdLinden
ID: 20326714
Is your computer on stand bye when it's supposed to run?
If so, you have to change the power management settings on the Settings tab (right-click your task, click Properties).
0
 

Author Comment

by:f19l
ID: 20326733
I leave the computer on overnight. I also have other task schedulers active as well and they seem to run just fine. The basic arrangement is that one task scheduler will open up an excel spreadsheet which will then run various processes followed by a second scheduler that will save and close down excel. The first one works without any problems but the second one does not for some reason.
0
 
LVL 12

Expert Comment

by:RubenvdLinden
ID: 20326821
Have you tried configuring this in an Excel macro which performs the tasks, saves the file and then exit the program?
This would make the second task scheduler obsolete and might help you around the problem.
0
 

Author Comment

by:f19l
ID: 20326844
The problem is that part of the processes in the excel spreadsheet involve establishing links to the Bloomberg financial system and this can not happen while macros are being run otherwise "#N/A" starts appearing in the cells. The spreadsheet has to be left for a few minutes after the macros have run so that the links can be properly updated. After this is done the task scheduler is then set to save and close down the spreadsheet.
0
 
LVL 12

Expert Comment

by:RubenvdLinden
ID: 20326996
OK, open your workbook and go to the macro editor.
Next, double-click on 'ThisWorkbook' in the project pane and add this code:

Private Sub Workbook_Open( )
    Application.OnTime TimeValue("6:00:00"), "EndMacro"
End Sub

Private Sub EndMacro()
    Application.OnTime TimeValue("6:00:00"), "EndMacro"
     
    ActiveWorkbook.Save
    Application.Quit
End Sub


This example will schedule the sub EndMacro to run at 6:00, as long as the spreadsheet is open.
EndMacro saves the active workbook and quits Excel.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:f19l
ID: 20327145
I have followed the coding you have above but I get an error message "The macro 'file address'!ENDMACRO' cannot be found". What I have done is that in the workbook_open page, where I have a number of if statements that check the time before running the associated code, I have incorporated your above-mentioned code into one of the if statements. Next, directly below the workbook_open section I have added the private sub EndMacro code.
0
 
LVL 12

Accepted Solution

by:
RubenvdLinden earned 500 total points
ID: 20327172
Sorry my mistake, it should be:

Application.OnTime TimeValue("6:00:00"), "ThisWorkbook.EndMacro"

You need to replace both lines with this one.
I hope it will work for you now!
0
 

Author Comment

by:f19l
ID: 20327246
It does in deed but what I want to do now is to test it tomorrow morning for real and see if everything works ok before I close this question.
0
 
LVL 12

Expert Comment

by:RubenvdLinden
ID: 20327269
No better test than a production test ;-)
I'm very interested in your results!
0
 

Author Comment

by:f19l
ID: 20333246
Everything seems to be running as expected! Thanks.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

NTFS file system has been developed by Microsoft that is widely used by Windows NT operating system and its advanced versions. It is the mostly used over FAT file system as it provides superior features like reliability, security, storage, efficienc…
Note: This is the second blog post in a series on email clearinghouses (https://www.xmatters.com/alert-management/blog-email-has-failed-us?utm_campaign=70138000000ydLoAAI&utm_source=exex&utm_medium=article&utm_content=blog-post).   Every month t…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

929 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

13 Experts available now in Live!

Get 1:1 Help Now