定期刷新VBA宏 [英] Refreshing a VBA Macro Periodically
问题描述
大家好
我已将以下代码分配给按钮刷新我的数据。我想知道是否有一段代码允许数据每20分钟自动刷新/运行???
I have assigned the below code that refreshes my data to a button. I was wondering if there was a piece of code that allows the data to automatically refresh/run every 20minutes???
Sub RESFRESHCUSTOMERORDERS()
'
'RESFRESHCUSTOMERORDERS宏<
Sub RESFRESHCUSTOMERORDERS()
'
' RESFRESHCUSTOMERORDERS Macro
ActiveSheet.Unprotect
表格("KYPERA")。可见=真
表格("KYPERA")。选择
表格("PIVOTS")。可见=真
表格("KYPERA")。选择
范围("B7")。选择
Selection.ListObject.QueryTable.REFRESH BackgroundQuery:= False
范围("F7")。选择
Selection.ListObject.QueryTable.REFRESH BackgroundQuery:= False
范围("N7")。选择
Selection.ListObject.QueryTable.REFRESH BackgroundQuery:= False
范围("BT7")。选择
Selection.ListObject.QueryTable.REFRESH BackgroundQuery:= False
范围("BZ7")。选择
Selection.ListObject.QueryTable.REFRESH BackgroundQuery:= False
表格("PIVOTS")。选择
范围("B5")。选择
ActiveSheet.PivotTables(" PivotTable1")。PivotCache.REFRESH
范围("F5")。选择
ActiveSheet.PivotTables(" PivotTable2")。PivotCache.REFRESH
范围("J5")。选择
ActiveSheet.PivotTables(" PivotTable3")。PivotCache.REFRESH
范围("N5")。选择
ActiveSheet.PivotTables(" PivotTable4")。PivotCache.REFRESH
范围("R5")。选择
ActiveSheet.PivotTables(" PivotTable5")。PivotCache.REFRESH
范围("W5")。选择
ActiveSheet.PivotTables(" PivotTable7")。PivotCache.REFRESH
表格("PIVOTS")。选择
ActiveWindow.SelectedSheets.Visible = False
表格("KYPERA")。选择
ActiveWindow.SelectedSheets.Visible = False
Sheet8.Select
范围("G15")。选择
Selection.ListObject.QueryTable.REFRESH BackgroundQuery:= False
ActiveSheet.Protect DrawingObjects:= True,Contents:= True,Scenarios:= True _
,AllowFiltering:= True
结束子
ActiveSheet.Unprotect
Sheets("KYPERA").Visible = True
Sheets("KYPERA").Select
Sheets("PIVOTS").Visible = True
Sheets("KYPERA").Select
Range("B7").Select
Selection.ListObject.QueryTable.REFRESH BackgroundQuery:=False
Range("F7").Select
Selection.ListObject.QueryTable.REFRESH BackgroundQuery:=False
Range("N7").Select
Selection.ListObject.QueryTable.REFRESH BackgroundQuery:=False
Range("BT7").Select
Selection.ListObject.QueryTable.REFRESH BackgroundQuery:=False
Range("BZ7").Select
Selection.ListObject.QueryTable.REFRESH BackgroundQuery:=False
Sheets("PIVOTS").Select
Range("B5").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.REFRESH
Range("F5").Select
ActiveSheet.PivotTables("PivotTable2").PivotCache.REFRESH
Range("J5").Select
ActiveSheet.PivotTables("PivotTable3").PivotCache.REFRESH
Range("N5").Select
ActiveSheet.PivotTables("PivotTable4").PivotCache.REFRESH
Range("R5").Select
ActiveSheet.PivotTables("PivotTable5").PivotCache.REFRESH
Range("W5").Select
ActiveSheet.PivotTables("PivotTable7").PivotCache.REFRESH
Sheets("PIVOTS").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("KYPERA").Select
ActiveWindow.SelectedSheets.Visible = False
Sheet8.Select
Range("G15").Select
Selection.ListObject.QueryTable.REFRESH BackgroundQuery:=False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFiltering:=True
End Sub
推荐答案
在模块的顶部,声明一个公共变量:
At the top of your module, declare a public variable:
Dim NextTime As Date
然后,在 &和您的Sub,使用
Then, at the and of your Sub, use
&NBSP; NextTime = Now()+ TimeValue(" 00:20:00")
&NBSP; Application.OnTime NextTime,"RESFRESHCUSTOMERORDERS",Schedule:= True
NextTime = Now() + TimeValue("00:20:00")
Application.OnTime NextTime, "RESFRESHCUSTOMERORDERS", Schedule:=True
但你还需要在之前的关闭事件中使用它本工作簿代码模块:
But you also need to use this in the before close event of the Thisworkbook codemodule:
CancelRefresh
CancelRefresh
您在标准代码模块中执行此过程以取消刷新:
Sub CancelRefresh()
  ; &NBSP; Application.OnTime NextTime,"RESFRESHCUSTOMERORDERS",Schedule:= F
End Sub
这篇关于定期刷新VBA宏的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!