Solved

TASK SCHEDULER PROBLEM

Posted on 2007-11-21
10
171 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
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 

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
 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Title # Comments Views Activity
Changing MS Windows 2003 Server product key 6 328
Group Policy 9 561
Images sometimes not printed 6 489
Windows 2000 48-bit LBA 13 54
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…
Originally, this post was published on Monitis Blog, you can check it here . Websites are getting bigger and more complicated by the day. Video, images and custom fonts are all great for showcasing your product or service. But the price to pay in…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

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