1

I am running into a race condition issue where I have two QueryTables, each is hooked with its own AfterRefresh event. Each AfterRefresh event does some copy'n'pasting as well as doing some calculations.

Now, when the user click Refresh All (Ctrl+Alt+F5) in Excel, I would love to have each AfterRefresh handler to execute, but ONLY after all QueryTable refreshes are entirely completed.

I did a search on StackOverFlow, and someone suggested 

Activeworkbook.RefreshAll
DoEvents

However, that's assuming that we are programmatically triggering the RereshAll. In my case, Refresh All is done by the built-in Refresh All (Ctrl+Alt+F5) button within Excel. Thus, I don't see where I can insert DoEvents in my case (unless I create my own Refresh All button, but I would like to avoid doing so).

I tried to search for "Excel VBA mutex", but I did not find anything in particular. So how do I make sure that all the refreshes are done, before each AfterRefresh handler takes place?

Thanks for reading!

Update: To help out with debugging.. here are my VBA codes.

I have a module named AutoOpen

Dim S As New DataCopy
Dim U As New DataCopy

Sub Auto_Open()
    Set S.qt = ThisWorkbook.Sheets(1).QueryTables(2)
    S.myWorkbookName = ThisWorkbook.Name
    S.sWorksheetProcessName = "ProcessS"
    S.sWorksheetDataColumnStart = 1
    S.sWorksheetDataColumnEnd = 5
    Set U.qt = ThisWorkbook.Sheets(1).QueryTables(1)
    U.myWorkbookName = ThisWorkbook.Name
    U.sWorksheetProcessName = "ProcessU"
    U.sWorksheetDataColumnStart = 6
    U.sWorksheetDataColumnEnd = 10
End Sub

I also have a Class module named DataCopy

Public WithEvents qt As QueryTable
Public myWorkbookName As String
Public sWorksheetProcessName As String
Public sWorksheetDataColumnStart As Integer
Public sWorksheetDataColumnEnd As Integer

Private Sub qt_AfterRefresh(ByVal Success As Boolean)
    DataCopier
End Sub

Private Sub DataCopier()
    'Debug.Print sWorksheetProcessName & "," & Application.CalculationState
    Dim LastNRows As Integer
    Dim sWorksheetDataName As String

    ' How many rows to copy
    LastNRows = 297
    sWorksheetDataName = "Data"

    Application.ScreenUpdating = False

    ' Clear content in process tab
    With Workbooks(myWorkbookName).Worksheets(sWorksheetProcessName)
        .Range(.Cells(4, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 6)).ClearContents
    End With

    ' Copy to process Tab
    With Workbooks(myWorkbookName).Worksheets(sWorksheetDataName)
        LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        FirstRow = LastRow - LastNRows
        If FirstRow < 2 Then
            FirstRow = 2
        End If
        .Range(.Cells(FirstRow, sWorksheetDataColumnStart), .Cells(LastRow, sWorksheetDataColumnEnd)).Copy _
             Destination:=Workbooks(myWorkbookName).Worksheets(sWorksheetProcessName).Range("A4")
    End With

    Debug.Print (sWorksheetProcessName & "," & sWorksheetDataColumnStart & "," & sWorksheetDataColumnEnd)

    Application.ScreenUpdating = True
End Sub

Because of the race condition, only one AfterRefresh handler succeeds in copy'n'pasting.. the other one doesn't work until I click Refresh All button (Ctrl+Alt+F5) again.

Community
  • 1
  • 1
Antony
  • 5,414
  • 7
  • 27
  • 32
  • Perhaps each `AfterRefresh` event handler can end with `Activeworkbook.RefreshAll` followed by `DoEvents`, perhaps with some toggling Boolean public variables to prevent echoing events. – John Coleman Oct 26 '15 at 23:57
  • @JohnColeman, but the user already clicked the built-in Refresh All button, so if I add `Activeworkbook.RefreshAll` to each `AfterRefresh` doesn't that mean it is essentially refreshing twice? Actually, on second thought, since it is within `AfterRefresh`, it would trigger infinite loop in refresh? Because `AfterRefresh` triggers yet another refresh, which triggers `AfterRefresh` again – Antony Oct 27 '15 at 00:01
  • I see the problem a little more clearly now. Perhaps include a timer loop at the beginning of each of the event handlers with a `DoEvents` in the body of the loop. Each of the event handler can pause for a few seconds while they allow the refresh to finish. It might even be as simple as including a `DoEvents` at the beginning of each of the handlers. You are going to need some sort of kludge because Excel isn't really designed for multithreading. Also -- I don't remember now what it was, by I once encountered a situation that required two `DoEvents` in a row. As an experiment start both with 2 – John Coleman Oct 27 '15 at 00:07
  • @JohnColeman Thanks. I will try it out. and report back. The problem with race condition is that the repro is not always consistent. Fortunately, it is 9 out of 10 times reproducible. – Antony Oct 27 '15 at 00:24
  • @JohnColeman `DoEvents` appears to do the trick so far. I simply inserted inside `qt_AfterRefresh` Feel free to submit that as an answer – Antony Oct 27 '15 at 04:07

2 Answers2

1

If a DoEvents works after the explicit VBA trigger Activeworkbook.RefreshAll then a DoEvents before the code that you want to run in the event handlers should cover the case when the refresh is triggered by Ctrl+Alt+F5. Thus, begin each event handler with the line DoEvents.

John Coleman
  • 51,337
  • 7
  • 54
  • 119
0

change the queries to not allow background refresh, and they will not relinquish control until refreshed

Location of Background refresh

SeanC
  • 15,695
  • 5
  • 45
  • 66
  • Is this something that can be done from the Connection property UI? or is this only done in VBA? Because if it is in code, I don't know if I need to invest in logic to check if it is already set to False or not. DoEvents appear to do the trick so far though, but I will try yours out too. – Antony Oct 27 '15 at 04:04
  • added picture of where to find the background refresh option – SeanC Oct 27 '15 at 12:56
  • Thanks Sean. I see why you have that option whereas I don't because my external sources are simply two continuously updating CSV files not SQL connection. – Antony Oct 27 '15 at 13:23