Run Private Sub at a certain time

[gtd526]

Hello,
I want to run a Private Sub on Thursday on or after 10:45am, listed under "This Workbook" under Excel Objects.
I use code:

Private Sub Workbook_Open2()
'test for time to perform

If Weekday(Now()) = vbThursday Then
Application.OnTime TimeValue("10:45:00"), "Query_Update" 'going to Query_Update to perform

End If
End Sub

"Query_Update" will update all Queries in the Workbook:
Workbooks("NFL.xlsm").RefreshAll

Im trying to run "Query_Update", listed under Modules as a separate sub.
Its not performing the "Query_Update" as listed.

Thank you.

 

[Joe4]

You cannot change the name of event prcoedures if you want them to run automatically.
So your procedure name should NOT be:

Private Sub Workbook_Open2()

but rather:

Private Sub Workbook_Open()

 

[gtd526]

You cannot change the name of event prcoedures if you want them to run automatically.
So your procedure name should NOT be:

Private Sub Workbook_Open2()

but rather:

Private Sub Workbook_Open()

I made the change you suggested, but it still doesn't run.

 

[Joe4]

Note a few other things:
1. In which module have you placed this code? This code MUST be placed in the "ThisWorkbook" Module in order to run automatically.
2. Is the file already open at that time on that day? This code will not run if the workbook is closed?

Personally, I never use Timers for tasks like this, because I really don't like to leave Excel files open constantly (and don't want to have to trust that they are still open at that day/time).
I typically use a scheduler (like Windows Scheduler) and either:
1. Open the Excel file on the day/time that I want it to run, and have the code in the Workbook_Open event so that it runs automatically.
2. Have a scheduled job (on Windows Scheduler) that runs code that opens the Excel file and runs a particular procedure. Though I have not had to do any of these in a number of years, I have used BAT file, and VBScript code in the past.

 

[gtd526]

Note a few other things:
1. In which module have you placed this code? This code MUST be placed in the "ThisWorkbook" Module in order to run automatically.
2. Is the file already open at that time on that day? This code will not run if the workbook is closed?

Personally, I never use Timers for tasks like this, because I really don't like to leave Excel files open constantly (and don't want to have to trust that they are still open at that day/time).
I typically use a scheduler (like Windows Scheduler) and either:
1. Open the Excel file on the day/time that I want it to run, and have the code in the Workbook_Open event so that it runs automatically.
2. Have a scheduled job (on Windows Scheduler) that runs code that opens the Excel file and runs a particular procedure. Though I have not had to do any of these in a number of years, I have used BAT file, and VBScript code in the past.

Thank you for the input.
I'm going to create a 'button' to perform the query update as needed.
Simplicity is best.
thx

 

[Joe4]

OK.

One other idea that you might want to think about is to have "Workbook_Open" event code that runs your macro, but also have it write a date/time stamp to some cell on your sheet (it doesn't matter where). Then you can add logic to the automated Workbook_Open code, and looks at the date stored in that cell, and determines if it is time to run the rest of the code should run or not (if it does, have it do its thing, then update that date/time stamp for next time).

 

[gtd526]

OK.

One other idea that you might want to think about is to have "Workbook_Open" event code that runs your macro, but also have it write a date/time stamp to some cell on your sheet (it doesn't matter where). Then you can add logic to the automated Workbook_Open code, and looks at the date stored in that cell, and determines if it is time to run the rest of the code should run or not (if it does, have it do its thing, then update that date/time stamp for next time).

Thank you.

 

[Joe4]

You are welcome.