宏每15分钟刷新整个excel工作簿（所有数据连接和计算）？ [英] Macro to refresh an entire excel workbook(all data connections and calculations) every 15 minutes?

问题描述

我有以下宏来刷新我的工作簿。在这一点上，这与点击刷新所有按钮相同。是否有一个时间元素，我可以添加到此代码，以使其刷新所有数据连接和所有计算在我的所有工作表工作簿每15分钟一次。

该工作簿具有从SharePoint列表项中提取数据的单元格，并包含典型的公式计算。

  Sub Workbook_RefreshAll（）
 ActiveWorkbook.RefreshAll 
 End Sub 
  

解决方案

在标准模块中输入以下内容：

公共运行当$ Double
公共Const cRunIntervalMinutes = 15
公共Const cRunWhat =Workbook_RefreshAll

Sub StartTimer（）
RunWhen = Now + TimeSerial（0，cRunIntervalMinutes，0）
Application.OnTime earliesttime：= RunWhen，procedure：= cRunWhat，_
schedule：= True
End Sub

Sub StopTimer（）
On Error Resume Next
Application.OnTime earliesttime：= RunWhen，_
procedure：= cRunWhat，schedule：= False
End Sub

Sub Workbook_RefreshAll（）
Application.CalculateFullRebuild
ActiveWorkbook.RefreshAll
调用StartTimer
End Sub

要开始进程运行 StartTimer（）并结束进程运行 StopTimer（）

改编自

I have the following Macro to refresh my workbook. At this point, this is the same as clicking on the refresh all button. Is there a time element that I can add to this code to get it to refresh all data connections and all calculations on all the worksheets in my workbook every 15 minutes.

The workbook has cells pulling data from SharePoint list items and contain typical formula calculations as well.

Sub Workbook_RefreshAll()
ActiveWorkbook.RefreshAll
End Sub

解决方案

Enter the following in a standard module:

Public RunWhen As Double
Public Const cRunIntervalMinutes = 15
Public Const cRunWhat = "Workbook_RefreshAll"

Sub StartTimer()
    RunWhen = Now + TimeSerial(0, cRunIntervalMinutes, 0)
    Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
         schedule:=True
End Sub

Sub StopTimer()
   On Error Resume Next
   Application.OnTime earliesttime:=RunWhen, _
       procedure:=cRunWhat, schedule:=False
End Sub

Sub Workbook_RefreshAll()
    Application.CalculateFullRebuild
    ActiveWorkbook.RefreshAll
    Call StartTimer
End Sub

To begin the process run StartTimer() and to end the process run StopTimer()

Adapted from Chip Pearson's Site

I used some Shapes to run the macros:

这篇关于宏每15分钟刷新整个excel工作簿（所有数据连接和计算）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！