Run a Macro or push a button automatically every few minutes?

[DwayneW]

Hi,

I am wondering if anyone has any pointers on running a particular macro automatically every minute, hour, or day? Or even better when a file is updated in a specific folder automatically?

I have set up an email macro to send specifically named xls attachments to a certain folder (from specified senders).

The received xls sheet is linked to another sheet which has a macro to convert the file to a kml; I would like to run an additional macro to "push the button" periodically which in turn spits out a kml file (which currently outputs to a watch folder which automatically uploads the kml file to my web space).

Any ideas anyone can share with me would be appreciated. Thanks!

 

[VoG]

Take a look at the OnTime method http://www.ozgrid.com/Excel/run-macro-on-time.htm

 

[DwayneW]

So just for some clarity I need to put the following code within my existing macro replacing "Workbook" with the name of my workbook and "MyMacro" with the name of my macro aka module?

"Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.OnTime dTime, "MyMacro", , False End Sub

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

Then I can create a new module like below also replacing the instances of MyMacro with the actual name of my macro:

"Public dTime As Date
Sub MyMacro()
dTime = Now + TimeValue("00:15:00")
Application.OnTime dTime, "MyMacro"
'YOUR CODE
End Sub"

And all should be good... or do I have that wrong?
</pre>

 

[VoG]

Press ALT + F11, in the Project Window double click ThisWorkbook and paste in

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dTime, "MyMacro", , False
End Sub

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

The only thing to change is MyMacro

Then in a regular module (Insert > Module) you place your macro similar to

Public dTime As Date
Sub MyMacro()
dTime = Now + TimeValue("00:15:00")
Application.OnTime dTime, "MyMacro"
'YOUR CODE
End Sub

 

[DwayneW]

I get an error when closing the document on the following line:

Application.OnTime dTime, "KML_file", , False </pre>Also it doesn't seem to like the name KML_file in the following line:

Application.OnTime dTime, "KML_file", , False
</pre>Is there a naming convention when calling modules that I am not aware of? Sorry I am a bit inexperienced with macros in excel... thanks very much for your help so far! I am definitely making progress!

 

[DwayneW]

I gott'er figured. I was using the module name instead of the sub name...

Thanks again!!!!

 

[VoG]

I tested that and it works for me. To avoid seeing that error when first closing the file, in the code window press CTRL + G then in the Immediate window type

Application.EnableEvents=False

and press Enter. Save and close the file. Then in the Immediate window

Application.EnableEvents=True

and press Enter.

Then open the file.