VBA - Refresh All Twice

[ExcelKnut]

Every day I open a bunch of workbooks and manually Refresh All each two times. I click Refresh All and when it's complete it's necessary for me to do Refresh All again--each refresh takes about 5 seconds. I'm trying to automate this. In VBA, when I have ActiveWorkbook.RefreshAll listed twice (as indicated below) it seems to only refresh once. And, I've tried putting Application.Wait (Now() + TimeValue("00:00:20")) in between the RefreshAll statements but that doesn't seem to help. Any assistance is greatly appreciated!!!!

[QUOTE]
ActiveWorkbook.RefreshAll

ActiveWorkbook.RefreshAll
[/QUOTE]

 

[johnny51981]

Since I'm still a newbie to VBA, I'm making a number of different Sub processes and then a Master Sub that does all of the calling in the order in which I am looking for.

So, can you do

Sub RefreshAll_First()

  ActiveWorkbook.RefreshAll

End Sub

Sub RefreshAll_Second()

  ActiveWorkbook.RefreshAll

End Sub

Sub CallAll()

  Call RefreshAll_First
  Call RefreshAll_Second

End Sub

 

[ExcelKnut]

I'll give that a try. Thanks for the idea!

 

[Jaafar Tribak]

Is the RefreshAll for refreshing querytable(s) or pivottables ?

If it is for refreshing a querytable, you could write some custom Method like REPT_RefreshAll to allow for repeated refreshes.

1- Place this code in the ThisWorkbook Module:

Option Explicit

Private WithEvents Q As Excel.QueryTable
Private oWb As Workbook
Private lRefreshesCount As Long

Private Sub Q_AfterRefresh(ByVal Success As Boolean)

    Static lCurrentRefresh As Long

    If Success And lRefreshesCount > 0 Then
        lRefreshesCount = lRefreshesCount - 1
        oWb.RefreshAll
        Debug.Print "Refresh number :", lCurrentRefresh + 1
        lCurrentRefresh = IIf(lRefreshesCount = 0, 0, lCurrentRefresh + 1)
    End If
End Sub

Public Sub REPT_RefreshAll(ByVal QT As QueryTable, ByVal RefreshesCount As Long)
    Set Q = QT
    Set oWb = QT.Parent.Parent
    lRefreshesCount = RefreshesCount
    oWb.RefreshAll
End Sub

2- Usage Example:

Sub example()
    'refresh querytable twice.
    Call ThisWorkbook.REPT_RefreshAll(QT:=Sheet1.QueryTables(1), RefreshesCount:=2)
End Sub

If you are trying to refresh pivottables, I am not familiar with them but I think you should be able do what you want using the corresponding pivottable event handlers.

 

[ExcelKnut]

Jaafar,

Thanks for taking the time to provide that information. I'll definitely give that a shot.

Here's what I don't understand. When I step through the code, both "Refresh All" statements work perfectly but when I run the code they don't. Why is that?