请求实现Excel宏完成后自动重启，每日午夜执行Envios子过程

Got it, let's get this sorted so your Envios sub runs every midnight without manual intervention. The core idea is to re-schedule the task right after it finishes executing—this creates a self-sustaining loop that sets up the next day's run each time.

Step 1: Refactor Your Code with Reusable Scheduling #

Here's the updated code structure that handles automatic re-scheduling:

Sub timer()
    ' Call this once to start the initial schedule (e.g., when opening the workbook)
    ScheduleNextEnvios
End Sub

Sub ScheduleNextEnvios()
    ' Calculate the next midnight (tomorrow's 00:00:00)
    Dim nextRunTime As Date
    nextRunTime = Date + 1 + TimeValue("00:00:00")
    
    ' Schedule the Envios sub to run at the calculated time
    Application.OnTime nextRunTime, "Envios"
End Sub

Sub Envios()
    ' --- Paste your existing Envios code here ---
    ' Example placeholder (replace with your actual tasks):
    MsgBox "Envios completed at " & Now()
    
    ' After finishing the task, schedule the next run for tomorrow's midnight
    ScheduleNextEnvios
End Sub

Step 2: Key Details Explained #

• ScheduleNextEnvios: This helper sub calculates the exact time of the next midnight (Date +1 gives tomorrow's date, plus 00:00:00 sets the time to midnight) and uses Application.OnTime to queue the Envios sub.
• Envios Update: By adding ScheduleNextEnvios at the end of your Envios sub, you ensure that as soon as the current task finishes, the next run is automatically scheduled. This creates the daily loop you need.
• Initial Setup: Run the timer sub once to kick off the first schedule. For convenience, you can add it to your workbook's Workbook_Open event so it starts automatically when you open the Excel file:

Private Sub Workbook_Open()
    timer
End Sub

Step 3: Optional - Cancel the Schedule (For Debugging) #

If you ever need to stop the automatic runs (e.g., for debugging), use this sub to cancel the pending OnTime event:

Sub CancelEnviosSchedule()
    On Error Resume Next ' Prevents errors if no schedule is active
    Dim nextRunTime As Date
    nextRunTime = Date + 1 + TimeValue("00:00:00")
    Application.OnTime EarliestTime:=nextRunTime, Procedure:="Envios", Schedule:=False
    On Error GoTo 0
End Sub

Important Notes #

• Excel must remain open for the schedule to work. If you close Excel, the pending OnTime event will be canceled. If you need this to run even when Excel is closed, consider using Windows Task Scheduler to open the Excel file at midnight (which will trigger Workbook_Open and start the schedule).
• Avoid running timer multiple times accidentally—this would create duplicate schedules. Use the CancelEnviosSchedule sub first if you need to re-start the schedule.

内容的提问来源于stack exchange，提问作者souza