英文:
Automatically macro run if system time every 10 minutes between start time to end time VBA excel
问题
我正在使用Excel 2013，
我需要VBA Excel在系统时间的每10分钟自动运行宏，从开始时间到结束时间。
VBA代码：
'在工作簿事件中：
Private Sub Workbook_Open()
Call Update
End Sub
'在一个模块中：
Dim RunTimer As Date
Sub Update()
RunTimer = Now + TimeValue("00:10:00")
Application.OnTime RunTimer, "Update"
'执行您的代码
MsgBox "数据已更新。", vbInformation
End Sub
例如：
1. 打开Excel
2. 开始时间为上午9:30
3. 结束时间为下午5:00
4. 每10分钟显示一个"消息框"
请帮我，提前感谢。
英文:
I am working on Excel 2013,
I need the automatic macro run if system time every 10 minutes between start time to end time VBA excel.
VBA Code:
'In Workbook events:
Private Sub Workbook_Open()
Call Update
End Sub
'In a Module:
Dim RunTimer As Date
Sub Update()
RunTimer = Now + TimeValue("00:00:20")
Application.OnTime RunTimer, "Update"
'Do Something Your Code
MsgBox "Data Update.", vbInformation
End Sub
eg:
- Excel Open
- start time is 9:30 AM
- end time is 5:00 PM
- show the "msgbox" every 10 minutes
Kindly help me, thanks in advance
I need the automatic macro run if system time every 10 minutes between start time to end time VBA excel
答案1
得分: 2
让你的代码像现在一样运行，不要有任何更改。你当前的代码每20秒调用一次，但你只需要每10分钟一次，将赋值给RunTimer的代码更改为 TimeValue("00:10:00")。
现在代码将每10分钟运行一次。你只需要检查当前时间是否在9:30到17:00之间。如果是，执行你需要的操作。
Sub Update()
Const StartTime = "09:30"
Const EndTime = "17:00"
Dim RunTimer As Date
RunTimer = Now + TimeValue("00:10:00")
Application.OnTime RunTimer, "Update"
If Time >= CDate(StartTime) And Time <= CDate(EndTime) Then
'执行你需要的操作...
MsgBox "数据更新。", vbInformation
End If
End Sub
英文:
Let your code run any time as it already does. Your current code issues the call every 20s, as you need it only every 10m, change the assignment to RunTimer to TimeValue("00:10:00").
Now the code will run all 10 minutes. All you need is to check if the current time is between 9:30 and 17:00. If yes, execute what ever you want.
Sub Update()
Const StartTime = "09:30"
Const EndTime = "17:00"
Dim RunTimer As Date
RunTimer = Now + TimeValue("00:10:00")
Application.OnTime RunTimer, "Update"
If Time >= CDate(StartTime) And Time <= CDate(EndTime) Then
'Do whatever you need to...
MsgBox "Data Update.", vbInformation
End If
End Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论