Running a macro automatically

N

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 ***
 
J

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
 
T

Toby Erkson

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

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 ***
 
D

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?
 
D

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 ***
 
D

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 ***
 
N

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 ***
 
D

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 ***
 
J

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.
 
J

JE McGimpsey

Huh?

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

comsdev

Joined
Nov 15, 2010
Messages
2
Reaction score
0
We talk about functions of Excel in our daily routine. One of the millions of functions of Excel is “Macros”. When you turns the Macros “on”, it starts recording whatever is being done in particular excel sheet. So you can get to know about the work done by your co workers on one Excel Sheet in your absence. Moreover, it can also be used for security purpose. In fact you can use Macros in as many ways as your imagination allows you. Complete MS Excel Macro Training material can be downloaded by clicking on the following mentioned link



http://uptodatearticles.com/2010/10/excel-macro-training/
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

File Names 5
Two Excel forms- swapping data. 1
User Defined Function 2
Auto FIll Weekdays dates 6
how to embed macro into new/create spreadsheet from outside of EXCEL? 1
Converting data throughout sheet/workbooks 4
excel vba 1
getting started... 2

Top