Solved

TASK SCHEDULER PROBLEM

Posted on 2007-11-21
10
165 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:f19l
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
No better test than a production test ;-)
I'm very interested in your results!
0
 

Author Comment

by:f19l
Comment Utility
Everything seems to be running as expected! Thanks.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

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…
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

17 Experts available now in Live!

Get 1:1 Help Now