Timed Macro

[Sean]

I have some code that I run every Saturday and Sunday at 2:00pm in a
Workbook.

Is it possible to somehow automatically run this macro without the
workbook being actually open and without me actually been present?

I would have the exact Date and Time that I want it to run at in a
cell, say A1. Reason I have this is that I use a new copy of the
workbook each week, so I only want the current file to run, not all
the historic files too.

Thanks

 

[Guest]

Start > Settings > Control Panel > Scheduled Tasks

 

[Damien McBain]

I have some code that I run every Saturday and Sunday at 2:00pm in a
Workbook.

Is it possible to somehow automatically run this macro without the
workbook being actually open and without me actually been present?

I would have the exact Date and Time that I want it to run at in a
cell, say A1. Reason I have this is that I use a new copy of the
workbook each week, so I only want the current file to run, not all
the historic files too.

You could use the Windows Scheduler to open the workbook. You'll have to
turn off macro security on that computer.

 

[Sean]

You could use the Windows Scheduler to open the workbook. You'll have to
turn off macro security on that computer.

How would I use the schedule task to run code within my Excel file?

 

[Gord Dibben]

Sean

You code would be placed into a Workbook_Open Sub which will run when Task
Scheduler starts Excel with your workbook.


Gord Dibben MS Excel MVP

 

[Sean]

Sean

You code would be placed into a Workbook_Open Sub which will run when Task
Scheduler starts Excel with your workbook.

Gord Dibben MS Excel MVP




- Show quoted text -

Thanks Gordon

Is it as simple as that? I thought with schedule task you had to
create a Batch file?

So I place my code in Workbook_Open sub, select my file on a New
Schedule Task, select time / date etc and that its?

I assume Excel application doesn't have do be open (or does it?) and
all I need to ensure is my PC's on?

Thanks

 

[Gord Dibben]

One easy method would be to stick the workbook in your XLSTART folder and set up
Task Scheduler to just start Excel.

Or point task scheduler to a short cut like

"C:\Program Files\Microsoft Office\Exceldata\12months.xls"

You will have to enter a Windows login password if you use one.

See Help and Support "schedule tasks" for more info on how to set up a scheduled
task.


Gord

 

[Guest]

Hi,

I am also stuck at this problem. What if the file is already updated in a
network server. As a result, i cannot access to this file from the Xlstart.
Anyother thing is our IT dept has also closed off the access to xlstart in my
C drive. So therefore i need to use another method, perhaps to write a macro
in the editor to activate this timed macro. But i am clueless as to what to
write. Can anyone advise what code to start with?

thanks,
Junior

 

[NickHK]

You say "Anyother thing is our IT dept has also closed off the access to
xlstart in my C drive".
Whilst I imagine this would be possible, are you sure this is the case ?
Such a restrictive environment would make most attempts at development
difficult to say the least.
Excel also has an alternate, but trying to circumvent IT restrictions may
not be a good idea:
?Application.AltStartupPath

But maybe you should get IT to free up your system if you are expected work
with <advanced> Excel capabilities.

NickHK

 

[Guest]

Hi Nick,

Thanks. How do i use the Application.AltStartupPath then? i am not very
familiar with this.

Junior

 

[NickHK]

You can see if it set with this. If not, you can set it to a suitable
folder.

Private Sub CommandButton1_Click()
Dim TempStr As String

TempStr = Application.AltStartupPath

If TempStr = "" Then
TempStr = "Not Set"
End If

MsgBox "AltStartupPath is: " & TempStr

End Sub

Or manually with Tools>Option>General>"At startup, open all file in:"

NickHK

Hi Nick,

Thanks. How do i use the Application.AltStartupPath then? i am not very
familiar with this.

Junior

You say "Anyother thing is our IT dept has also closed off the access to
xlstart in my C drive".
Whilst I imagine this would be possible, are you sure this is the case ?
Such a restrictive environment would make most attempts at development
difficult to say the least.
Excel also has an alternate, but trying to circumvent IT restrictions may
not be a good idea:
?Application.AltStartupPath

But maybe you should get IT to free up your system if you are expected work
with <advanced> Excel capabilities.

NickHK

Hi,

I am also stuck at this problem. What if the file is already updated in a
network server. As a result, i cannot access to this file from the Xlstart.
Anyother thing is our IT dept has also closed off the access to

xlstart in
my

C drive. So therefore i need to use another method, perhaps to write a macro
in the editor to activate this timed macro. But i am clueless as to

what
to

write. Can anyone advise what code to start with?

thanks,
Junior


:

One easy method would be to stick the workbook in your XLSTART

folder
and set up

Task Scheduler to just start Excel.

Or point task scheduler to a short cut like

"C:\Program Files\Microsoft Office\Exceldata\12months.xls"

You will have to enter a Windows login password if you use one.

See Help and Support "schedule tasks" for more info on how to set up

a
scheduled

task.


Gord

On Mar 19, 4:53 pm, Gord Dibben <gorddibbATshawDOTca> wrote:
Sean

You code would be placed into a Workbook_Open Sub which will run

when
Task

Scheduler starts Excel with your workbook.

Gord Dibben MS Excel MVP