How do I schedule a macro to run while my pc is turned off?

Question

1.00/5 (2 votes)

See more:

Hello,
I have a macro I wrote which refreshes 13 separate files. The total time of refresh is 6 hours which locks my excel during this time. Ideally, I'd like to schedule the macro to run after hours when my pc is off in order to have free up my excel during business hours.
Any assistance would be greatly appreciated.
Thank you,

Posted 20-Jan-14 11:28am

Member 10530881

Updated 11-Oct-18 3:16am

Add a Solution

Comments

Ron Beyer 20-Jan-14 18:13pm

How do you expect the macro to run if your PC is off? If your BIOS supports a wake timer you can use that to wake your PC up, but other than that I don't think this will work.

Sergey Alexandrovich Kryukov 20-Jan-14 18:17pm

This is a hardware problem. Your task is to write code when a computer is off. :-)
—SA

Ron Beyer 20-Jan-14 19:57pm

Could you imagine the power savings in the world if we could get tasks to run when the computer is powered down? I'll start on the patent...

Sergey Alexandrovich Kryukov 21-Jan-14 0:35am

Ah, great point! Of course. This will be even better then even that well-known power over IP.
—SA

PIEBALDconsult 20-Jan-14 18:35pm

Run it on a different computer?

Member 10530881 21-Jan-14 9:56am

That is not what I meant. I am new to code writing and miss used terminology. I want to lave my laptop on sleep or in hibernation mode but have a written code that will automatically run the macro after hours so the refreshes are completed overnight given the refresh time.

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Christian Graus · Answer 1 · 2014-01-20T15:07:00

Solution 1

The definition of 'off' is that your computer is not running. So, I assume this is a joke.

If you mean 'when I am logged off', then write a service to do it.

Posted 20-Jan-14 15:07pm

Christian Graus

Comments

Member 10530881 21-Jan-14 8:31am

Yes, I am looking for a code to start a macro I wrote while the computer is in sleep or hibernating for example. The total refresh time is 6+ hours and I am trying to find a way to do this outside of work hours in lieu of waking up at 2 a.m. to start the macro. I am new to writing codes and macros; therefore any assistance would be greatly appreciated.

Member 10530881 21-Jan-14 9:52am

Below is my code and at the specified time, nothing is happening.

Sub openFile()

Application.OnTime TimeValue("9:50:00"), "refreshAll"

Workbooks.Open Filename:= _
"C:\Users\v-giwhal\Documents\Bulk Update Reports\FY14 CR CAM Qualified Opportunity Report"

Application.Run "'FY14 CR CAM Qualified Opportunity Report.xlsb'!refreshAll"
ActiveWorkbook.Save
ActiveWindow.Close

End Sub

Christian Graus 21-Jan-14 13:42pm

Did you read my answer ? Write a service. A scheduled task can also be used, actually, that may be simpler.

Member 10530881 21-Jan-14 14:28pm

Hi Christian,
I do not know how to write a service. Below is my code. If I run the code, it works; however if I leave it with the OnTime statement, it does not run once the time approaches.

Sub openFile()

Application.OnTime TimeValue("13:00:00"), "refreshFile()"

End Sub

Sub refreshFile()

Workbooks.Open Filename:= _
"C:\Users\v-giwhal\Documents\Bulk Update Reports\FY14 CR CAM Qualified Opportunity Report"
Application.Run "'FY14 CR CAM Qualified Opportunity Report.xlsb'!refreshAll"
ActiveWorkbook.Save
ActiveWindow.Close

End Sub

Christian Graus 21-Jan-14 14:30pm

OK, well, you have two options.

1 - read one of the many articles on how to write a service
2 - set up your script as a scheduled task

2 is easier and will probably work.

Member 14016152 · Answer 2 · 2018-10-11T03:16:00

Create a new workbook with a macro that runs once the workbook is opened:

Private Sub Workbook_Open()
'insert your code here'
End Sub

use "Task Scheduler" and create a task that opens that workbook at the desired time.
- under the "conditions" tab of the task, there will be a check box "Wake the computer to run this task". Make sure that is checked.

Do this and I believe the code should work as long as the PC is sleeping and not fully powered down. Please let me know if this works or not.
Thanks! :)

-Kyle

How do I schedule a macro to run while my pc is turned off?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

How do I schedule a macro to run while my pc is turned off?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us