標題: [發問] 自動執行巨集遇到星期日就停止執行 [打印本頁]

---

作者: jerry045    時間: 2018-7-23 09:18     標題: 自動執行巨集遇到星期日就停止執行

小弟寫了一段自動執行巨集的程式碼，如下，
電腦、檔案都沒關，每天讓它自己跑。
但是一到星期日就停止執行，
請問學長們，這是哪天有問題呢?
懇請指導，謝謝。



Private Sub Workbook_Open()

Application.OnTime TimeValue("08:00:00"), "Module2.insertDataIntoMysql"
Application.OnTime TimeValue("08:10:00"), "Module2.insertDataIntoMysql"
Application.OnTime TimeValue("08:20:00"), "Module2.insertDataIntoMysql"
Application.OnTime TimeValue("08:30:00"), "Module2.insertDataIntoMysql"
Application.OnTime TimeValue("08:40:00"), "Module2.insertDataIntoMysql"
Application.OnTime TimeValue("08:50:00"), "Module2.insertDataIntoMysql"
Application.OnTime TimeValue("09:00:00"), "Module2.insertDataIntoMysql"
Application.OnTime TimeValue("09:10:00"), "Module2.insertDataIntoMysql"
Application.OnTime TimeValue("09:20:00"), "Module2.insertDataIntoMysql"
Application.OnTime TimeValue("09:30:00"), "Module2.insertDataIntoMysql"
Application.OnTime TimeValue("09:40:00"), "Module2.insertDataIntoMysql"
Application.OnTime TimeValue("09:50:00"), "Module2.insertDataIntoMysql"
Application.OnTime TimeValue("10:00:00"), "Module2.insertDataIntoMysql"
Application.OnTime TimeValue("10:10:00"), "Module2.insertDataIntoMysql"
Application.OnTime TimeValue("19:30:00"), "Module2.insertDataIntoMysql"
Application.OnTime TimeValue("20:30:00"), "Module2.insertDataIntoMysql"



End Sub

---

作者: jackyq    時間: 2018-7-30 14:44

腦 連續7日都沒關 ?

---

作者: jerry045    時間: 2018-7-31 13:12

本帖最後由 jerry045 於 2018-7-31 13:14 編輯

對，電腦都沒關，所以一遇到禮拜日就不會執行了
但我用的電腦是IBM的Server
Windows Server 2012 R2 Standard

---

作者: jackyq    時間: 2018-7-31 14:55

原因不知道 , 只能試試看


Public Sub Workbook_Open()  ' Private  改  Public

For Schedule = 0 To 1
If Schedule = 0 Then On Error Resume Next

Application.OnTime TimeValue("08:00:00"), "Module2.insertDataIntoMysql", , Schedule
Application.OnTime TimeValue("08:10:00"), "Module2.insertDataIntoMysql", , Schedule
Application.OnTime TimeValue("08:20:00"), "Module2.insertDataIntoMysql", , Schedule
Application.OnTime TimeValue("08:30:00"), "Module2.insertDataIntoMysql", , Schedule
Application.OnTime TimeValue("08:40:00"), "Module2.insertDataIntoMysql", , Schedule
Application.OnTime TimeValue("08:50:00"), "Module2.insertDataIntoMysql", , Schedule
Application.OnTime TimeValue("09:00:00"), "Module2.insertDataIntoMysql", , Schedule
Application.OnTime TimeValue("09:10:00"), "Module2.insertDataIntoMysql", , Schedule
Application.OnTime TimeValue("09:20:00"), "Module2.insertDataIntoMysql", , Schedule
Application.OnTime TimeValue("09:30:00"), "Module2.insertDataIntoMysql", , Schedule
Application.OnTime TimeValue("09:40:00"), "Module2.insertDataIntoMysql", , Schedule
Application.OnTime TimeValue("09:50:00"), "Module2.insertDataIntoMysql", , Schedule
Application.OnTime TimeValue("10:00:00"), "Module2.insertDataIntoMysql", , Schedule
Application.OnTime TimeValue("10:10:00"), "Module2.insertDataIntoMysql", , Schedule
Application.OnTime TimeValue("19:30:00"), "Module2.insertDataIntoMysql", , Schedule
Application.OnTime TimeValue("20:30:00"), "Module2.insertDataIntoMysql", , Schedule

On Error GoTo 0
Next

End Sub


Sub insertDataIntoMysql()  ' Module2.insertDataIntoMysql

Call ThisWorkbook.Workbook_Open

' ........ your code begin




' ........ your code end

End Sub

---

作者: jackyq    時間: 2018-7-31 18:19

看看哪一個會成功

'======== ThisWorkbook

Private Sub Workbook_Open()
  Call MyTimerTask(Date)
End Sub


'======== Module2

Public Sub insertDataIntoMysql()  'Module2.insertDataIntoMysql
  
  Call MyTimerTask(Date + 1)

' ........ your code begin


' ........ your code end

End Sub

Private Sub Application_OnTime(EarliestTime, Procedure As String)
  If CDate(Format(EarliestTime, "hh:mm:ss")) < Time Then Exit Sub
  On Error Resume Next
  Application.OnTime EarliestTime, Procedure, , 0
  Application.OnTime EarliestTime, Procedure, , 1
End Sub

Private Sub MyTimerTask(日期 As Date)

Application_OnTime 日期 + TimeValue("08:00:00"), "Module2.insertDataIntoMysql"
Application_OnTime 日期 + TimeValue("08:10:00"), "Module2.insertDataIntoMysql"
Application_OnTime 日期 + TimeValue("08:20:00"), "Module2.insertDataIntoMysql"
Application_OnTime 日期 + TimeValue("08:30:00"), "Module2.insertDataIntoMysql"
Application_OnTime 日期 + TimeValue("08:40:00"), "Module2.insertDataIntoMysql"
Application_OnTime 日期 + TimeValue("08:50:00"), "Module2.insertDataIntoMysql"
Application_OnTime 日期 + TimeValue("09:00:00"), "Module2.insertDataIntoMysql"
Application_OnTime 日期 + TimeValue("09:10:00"), "Module2.insertDataIntoMysql"
Application_OnTime 日期 + TimeValue("09:20:00"), "Module2.insertDataIntoMysql"
Application_OnTime 日期 + TimeValue("09:30:00"), "Module2.insertDataIntoMysql"
Application_OnTime 日期 + TimeValue("09:40:00"), "Module2.insertDataIntoMysql"
Application_OnTime 日期 + TimeValue("09:50:00"), "Module2.insertDataIntoMysql"
Application_OnTime 日期 + TimeValue("10:00:00"), "Module2.insertDataIntoMysql"
Application_OnTime 日期 + TimeValue("10:10:00"), "Module2.insertDataIntoMysql"
Application_OnTime 日期 + TimeValue("19:30:00"), "Module2.insertDataIntoMysql"
Application_OnTime 日期 + TimeValue("20:30:00"), "Module2.insertDataIntoMysql"

End Sub

---

歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)