Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 177
  • Last Modified:

TASK SCHEDULER PROBLEM

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
f19l
Asked:
f19l
  • 5
  • 5
1 Solution
 
RubenvdLindenCommented:
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
 
f19lAuthor Commented:
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
 
RubenvdLindenCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
f19lAuthor Commented:
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
 
RubenvdLindenCommented:
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
 
f19lAuthor Commented:
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
 
RubenvdLindenCommented:
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
 
f19lAuthor Commented:
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
 
RubenvdLindenCommented:
No better test than a production test ;-)
I'm very interested in your results!
0
 
f19lAuthor Commented:
Everything seems to be running as expected! Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now