How to run Macros automatically after 15 minutes-CodePudding

thank you so much everyone for the help, i have successfully automated my process. what i have done is

I have added a code in a module1 which copies files from source folder to multiple Destination folders via partial name trick.

In Module2 i have added another code which after copying files to multiple destination, moves the files to another folder where i gather all the files of daily basis.

In order to automate both codes i have added module3 and calling both 1 and 2 module via it.

Sub Button1_Click()

    Call moveFilesFromListPartial_A     'Macro1

    Call MoveFilesTEST   'Macro2
   
End Sub

However I would like to request if there is any option where i can run these modules automatically after every 15 minutes. otherwise i need to press the run button again and again. If i can automate and can run module3 (whose code is mentioned above) then i believe the whole process can be run after every 15 minutes

I have tested one time application method by inserting module4 and have experimented the below mentioned code but not successful

Application.OnTime Now   TimeValue("00:00:15"), "my_Procedure"

Can anyone help please, i will really be greatful

regards

CodePudding user response：

If you want to run

Sub Button1_Click()
    Call moveFilesFromListPartial_A     'Macro1
    Call MoveFilesTEST   'Macro2
End Sub

every 15 minutes you just add Application.OnTime Now TimeValue("00:15:00"),"Button1_Click"

So, your code would look like that

Sub Button1_Click()
    Call moveFilesFromListPartial_A     'Macro1
    Call MoveFilesTEST   'Macro2
    Application.OnTime Now   TimeValue("00:15:00"),"Button1_Click"
End Sub

Application.OnTime will run Button1_Click every 15min. As long as you do not close Excel, hit the End button in the VBE, do debugging or for whatever reason the timer is not called this will work. In other words you can't use this for a productive environment but maybe it is sufficient for your purposes.

CodePudding user response：

First issue - your TimeValue indicates 15 seconds - not minutes. So, it should be TimeValue("00:15:00").

Secondly, you have to perform first run of your sub with Application.OnTime code and then re-lanuch it from other module.

Copy this code to This_Workbook object:

Sub Workbook_Open()
  Call x 'this code will run every time you open the workbook
End Sub

Copy code below to any module:

Sub x()
  Application.OnTime Now   TimeValue("00:15:00"), "y"
End Sub

Sub y()
  MsgBox "test" 'replace this code with anything you need
  Call x
End Sub

However, I think you should pay attention to a comment from @Foxfire And Burns And Burns and @FunThomas.