Excel 2010: more frequent external data refresh with vba?

3
votes

Excel 2010 lets you refresh external data sources once a minute at the fastest settings in:

Data -> Properties -> Refresh every

What is the best vba to continously update from connections under a minute? Ideally I would like to set refresh rates for each individual connection but Refresh All is sufficient.

1
vbaexcel

1 Answers

6
votes

The following method works:

In ThisWorkbook,

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
    On Error Resume Next 
    If Cancel = False Then Application.OnTime dTime, "RefreshIt", , False 
    On Error Goto 0 
End Sub 

Private Sub Workbook_Open() 
    Run "RefreshIt" 
End Sub

In a new module,

Public dTime As Date 

Sub RefreshIt() 
    Sheets(1).Range("A1").QueryTable.Refresh 
    dTime = Time + TimeValue("00:00:30") 
    Application.OnTime dTime, "RefreshIt" 
End Sub

Thanks to Dave Hawley on the following forum http://www.ozgrid.com/forum/showthread.php?t=24119