如何使用Application.OnTime每天在设置的时间调用宏，而无需关闭工作簿 [英] How to use Application.OnTime to call a macro at a set time everyday, without having to close workbook

问题描述

我已经编写了一个使用Application.OnTime的宏，如果我手动执行宏，那么这个宏是有效的。我试图自动化这个过程，所以我不必在这个工作簿或（Private Sub Workbook_Open（））中写入Application.OnTime大多数人这样做是因为你可以在一定时间内打开Windows调度工具启动宏打开，我不能使用SCHEDULER。

因为我无法使用Windows调度程序，我将保持工作簿打开，计时器应该刷新我的数据，然后调用我的宏在每天的某个时间。

我在哪里放置这个代码，如何设置自动计时器？

解决方案

您可以创建一种重复过程，可以看起来如下：

 code> Sub Call_At_3_30（）
'首先调用相应的程序
调用myProcedure 
'接下来，设置新的调用时间
 Application.OnTime TimeValue（3:30:00 ），Call_At_3_30
 End Sub 
  

在某处你会保留你的主要程序，这个情况

  Sub myProcedure 
'你的代码在这里
 End Sub 
  

在这种情况下，您只需运行一次子程序 Call_At_3_30 但是您需要记住，Excel必须始终打开。

可选地，如果您想在24小时后调用您的程序，您可以更改 .OnTime 这样的指令：

  Application.OnTime Now + 1，Call_At_3_30
  

也可以进行其他修改。

I have written a macro that uses Application.OnTime that works if I manually execute the macro. I'm trying to automate this process so I don't have to write Application.OnTime in "This Workbook" or (Private Sub Workbook_Open() Most of you do this because you can have windows scheduler open the workbook at a certain time which starts the macros on open. I CANNOT USE SCHEDULER.

Because I am not able to use windows scheduler I will keep the workbook open and the timer should refresh my data then Call "my Macro" at a certain time everyday.

Where do I place this code, and how do I set an auto timer?

解决方案

You could create a kind of recurrence procedure. It could look as follows:

Sub Call_At_3_30()
    'first call appropriate procedure 
    Call myProcedure
    'next, set new calling time
    Application.OnTime TimeValue("3:30:00"), "Call_At_3_30"
End Sub

Somewhere you will keep your main procedure, it this situation:

Sub myProcedure
    'your code here
End Sub

In this situation you need to run first subroutine Call_At_3_30 only once. But you need to remember that Excel must be turned on all the time.

Optionally, if you want to call your procedure after 24 hours you could change .OnTime instruction in this way:

Application.OnTime Now + 1, "Call_At_3_30"

Some other modifications are possible, too.

这篇关于如何使用Application.OnTime每天在设置的时间调用宏，而无需关闭工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！