Run Private Sub at a certain time

gtd526

Active Member
Joined
Jul 30, 2013
Messages
297
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
You cannot change the name of event prcoedures if you want them to run automatically.
So your procedure name should NOT be:
VBA Code:
Private Sub Workbook_Open2()
but rather:
VBA Code:
Private Sub Workbook_Open()
 

gtd526

Active Member
Joined
Jul 30, 2013
Messages
297
Office Version
  1. 2019
Platform
  1. Windows
You cannot change the name of event prcoedures if you want them to run automatically.
So your procedure name should NOT be:
VBA Code:
Private Sub Workbook_Open2()
but rather:
VBA Code:
Private Sub Workbook_Open()
I made the change you suggested, but it still doesn't run.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jul 30, 2013
Messages
297
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jul 30, 2013
Messages
297
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,485
Messages
5,596,442
Members
414,065
Latest member
kamlkham

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top