Running a macro automatically

[nair]

HI-

I have a macro written which works fine.But I am having difficulty
running this automatically. I want it to run just once a day when the
user opens it. Or another way would be to kick it off once a day at a
certain time automatically without anybody even opening the file. The
file resides on a server.

Please help me, as I am new developer and need help. I would appreciate
it.

Thanks


** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Dave Hawley]

Hi nair

You can use the Private Module of the Workbook Object (ThisWorkbook) to
run a macro upon opening. E.g

Private Sub Workbook_Open()
Run "MyMacro"
End Sub

Or, to have a macro run at a set time per day, see this page;
http://www.ozgrid.com/Excel/run-macro-on-time.htm

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[JE McGimpsey]

That solution doesn't meet the OP's criterion of "once per day".

One way (put in the ThisWorkbook code module):

Private Sub Workbook_Open()
Dim dpRan As DocumentProperty
On Error Resume Next
Set dpRan = ThisWorkbook.CustomDocumentProperties("Last_Ran")
On Error GoTo 0
If dpRan Is Nothing Then _
Set dpRan = ThisWorkbook.CustomDocumentProperties.Add( _
Name:="Last_Ran", _
LinkToContent:=False, _
Type:=msoPropertyTypeDate, _
Value:=#1/1/2000#)
If Not dpRan.Value = Date Then MyMacro 'your macro's name here
dpRan.Value = Date
ThisWorkbook.Save
End Sub

 

[Toby Erkson]

An use a scheduler to launch the Excel file, of course, and this will work
fine.

 

[Dave Hawley]

RE: That solution doesn't meet the OP's criterion of "once per day".

That because you did't bother to read the link I posted.

***Please*** let's not start the usual nit picking Jim. We are all here
helping for free.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Dave Peterson]

I don't think JE (not Jim) was nitpicking.

What time would I use to make sure that the macro would run once per day?

What happens if I close excel before that time?

What happens if I open the workbook after the chosen time?

 

[Dave Hawley]

I might have guessed the words "nit picking" would bring certain types
out of the woodwork. Your questions really are "nit picking".

RE: What time would I use to make sure that the macro would run once per
day?

The time you want it to run.

RE: What happens if I close excel before that time?

It will re-open itself. Unless the OnTime Method is cancelled.

RE: What happens if I open the workbook after the chosen time?

It wont run.

These silly type questions could be applied to any reply given here
David. If the OP needs more details, further help, changes or whatever,
**they** can ask, or they will use one of the other replies.
Regardless, any reply is helpful, if not for the OP, perhaps another
searching.

Again ****PLEASE**** let's not go out of our way to nit pick.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Dave Hawley]

Example of nit picking question in regards to JE reply.

But what happens if macros are disabled?

What happens if internal Workbook protection is applied?

How does one get to "ThisWorkbook"?

What happens if the Workbook is not opened?

What happens if it is opened within Excel with the Shift Key held down?

and the list could go on and on and on....

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[nair]

Private Sub Workbook_Open()
Application.OnTime TimeValue("15:00:00"), "MyMacro"
End Sub

The Ontime function didnot work for me.It just didn't run. I can just
run it automatically without the OnTIme function. But since I just want
it to run for a specific time- It doesn't resolve my problem. Could
Someone help.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Dave Hawley]

It should work. Is your macro really named "MyMacro"?

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Dave Hawley]

....also, did you place the macro into the Private Module of the
Workbook Object, "ThisWorkbook"? To get there quickly right click on the
Excel icon, top left next to "File" and select "View Code".

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[JE McGimpsey]

Sorry, I interpreted your use of the word "Or" to mean that you thought
that each solution was relevant.

As it turns out, neither was.

 

[JE McGimpsey]

Huh?

When the App is closed, OnTime goes away. There's nothing left running
to open XL again.

 

[Dave Hawley]

Hi nair

Feel free to email me off the list on this one.



** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[jnair]

Thanks a lot for all the help!

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***