如何定期刷新Excel中的数据库数据? [英] How to refresh database data in Excel at regular intervals?

问题描述

在我制作的Excel电子表格/VBA脚本中，我需要从数据库中调用数据，并每5分钟刷新一次值.该程序从按下按钮开始，并且应连续运行，直到用户中断执行为止.我目前不确定如何在不暂停电子表格的情况下使Excel/VBA等待" 5分钟，并且在理想情况下还可以确保计算效率不高.

In an Excel spreadsheet/VBA script I'm making, I need to call data from a database, and refresh the values every 5 minutes. The program starts from the push of a button, and should run continuously until the user breaks the execution. I'm currently not sure how to make Excel/VBA 'wait' 5 minutes without pausing the spreadsheet and, ideally, without being computationally inefficient.

我尝试使用"Application.Wait"和"Sleep"功能，但是这两个功能都在5分钟的等待时间内暂停了电子表格.

I've tried using the "Application.Wait" and "Sleep" functions, but both of those pause the spreadsheet during the 5 minute wait.

我当前的解决方案是在其中使用"DoEvents"的"While"循环，如下面的代码所示.这使程序仅在"While"循环中运行5分钟，并且由于"DoEvents"而不会暂停电子表格.但是，尽管电子表格可用，但计算效率低下，因为从技术上来说，程序执行没有暂停，它只是连续运行"While"循环，因此某些可能会使用我的程序的速度较慢的计算机可能会大大减慢速度通过这个.

My current solution is to use a "While" loop with "DoEvents" inside it, as shown in the code below. This makes the program just run the "While" loop for 5 minutes, and it does not pause the spreadsheet thanks to "DoEvents". However, while the spreadsheet is usable, this is computationally inefficient, since the program execution isn't technically paused, it's just running the "While" loop continuously, and some of the slower computers that may end up using my program might be significantly slowed by this.

我当前的解决方法如下:

My current solution is as follows:

    Sub MainProgram()
    'dimension variables, open database connection, etc.
    Do While 1 < 2 'ad infinitum
        'get database data, write to spreadsheet, etc.
        WasteTime()
    Loop
    End Sub


    Sub WasteTime()
    EndTime = Now + TimeSerial(0,5,0)
    While Now < EndTime
        DoEvents
    Wend
    End Sub

如上所述，与此有关的问题是计算效率低下.在整个WasteTime循环中，CPU利用率很高.所以我想知道，有没有办法在不暂停电子表格和不连续运行代码的情况下暂停脚本，从而加重CPU负担?

The problem with this, as mentioned above, is the computational inefficiency. CPU utilization is fairly high throughout the WasteTime loop. So I'm wondering, is there any way to pause the script without pausing the spreadsheet and without running the code continuously, thus burdening the CPU?

推荐答案

就像BigBen在评论中提到的那样 Application.OnTime 是最佳选择.通过安排在将来某个时间调用第二个宏，可以避免您描述的开销.

As BigBen mentions in comments Application.OnTime is the best option for this. It avoids the overhead you describe by scheduling a second macro to be called at a future time.

下面是一个示例.您可以使用常量变量修改等待时间.它们应该在同一模块内(或将theCalculation宏更改为非私有).

Below is an example. You can modify the wait time with the constant variable. These should be within the same Module (or change theCalculation macro to not be private).

Sub TheTimerMac()
     'enter in timevalue (hour:Minute:Second)
     Const DelayTime As String = "00:05:00" 

     Dim nextRunTime As Date
     nextRunTime = Now + TimeValue(DelayTime)

     'Schedules application to execute below macro at set time.
     Application.OnTime nextRunTime, "TheCalculation"

End Sub



Private Sub TheCalculation()
     'whatever you use for your calc here
     Application.CalculateFull

     'This will restart the timer portion.
     Call TheTimerMac

End Sub

这篇关于如何定期刷新Excel中的数据库数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！