Pivot Table refresh only works in debug mode..!?

[High Plains Grifter]

Hi all,

I have a workbook which contains a link to an external SQL procedure to bring in data. The user will select parameters in the spreadsheet and run the macro to retrieve the data and then will be shown a pivot table of the results. The data actually goes initially into a normal table, which is in a hidden sheet. This hidden table contains some calculated fields and things which are used to make the pivot table the user can see and edit. I have done this so that there is a nice selection of neatly named fields etc for the user to play with when editing the pivot table.

Here is the code which retrieves the data and shows the pivot table (which is in the sheet "Results")

Sub RefreshQuery()
Dim strSQL As String
Dim pt As PivotTable

'If they don't tell us what they want, hit them around the head with a message bok or two
On Error Resume Next
'If the first user is blank, then the chosen user range is empty and they have selected no one
If Range("FirstUser").Value = "" Then
    MsgBox "You must select at least one user to report against", vbCritical, "Error Retrieving Data"
    Exit Sub
Else
    If Range("StartDate").Value = "" Or Range("FinishDate").Value = "" Then
            MsgBox "You must enter a complete date range to report against", vbCritical, "Error Retreiving Data"
            Exit Sub
    Else
            'nothing
    End If
End If
    
'From this point on, errors will be my fault, so give them some helpful advice if the data fails
On Error GoTo Fail
'build the command string to suit the procedure (EXEC [User_GET_TestProductivityStats] @UserIds,@Start,@Finish,@Grouper)
    strSQL = _
    "EXEC gotrex_companion_live.dbo.[User_GET_TestProductivityStats] '" & _
    Range("ConcatUserList").Value & "','" & _
    Format(Range("StartDate").Value, "yyyy-MM-dd") & "','" & _
    Format(Range("FinishDate").Value, "yyyy-MM-dd") & "','" & _
    Application.WorksheetFunction.Index(Range("Grouping"), Range("GroupingSelected").Value) & "'"

    'If you want to see the command string
    'Sheets("Parameters").Range("I1").Value = strSQL
    'Exit Sub
With ActiveWorkbook.Connections("UsersActionsExample")
        .OLEDBConnection.CommandText = Array(strSQL)
        .Refresh
End With

'Tell them what to do if it all goes wrong.
Fail:
If Err.Number <> 0 Then
        MsgBox "Data Retrieval Failed " & vbCrLf & _
        "Please check date formats and grouping. If you cannot see a problem with the data you have entered, please contact GoTrex Support on 1468" & vbCrLf & _
        "Error Code: " & Err.Number, vbExclamation, "SQL Procedure Error"
        Exit Sub
Else
        'No problem - carry on
End If
    'Show them the numbers
    Sheets("Results").Activate
    Set pt = ActiveSheet.PivotTables("PivotTable3")
    'Refresh the pivot table.
    pt.RefreshTable

End Sub

The problem is with the last 3 lines. for some reason, the refresh command seems to only show be the previous data rather than the current data in the pivot table when the macro is run in the normal way (there is a button in the workbook). For instance, here is a chronology of something that might happen:

If I were to run the macro with ID "A", I would see arbitrary previous data.
If I then ran the macro again IDs "B,C,D", I would see "A"s data.
If I then ran with IDs "E,F" I would see "B,C,D"s data.
If I ran the macro again, without changing the people to be displayed, I would see "E,F"s data in the pivot table

In other words, the data is always one "run" behind what it should be. Weirdly, if I run the macro either by putting a break point at the line "pt.RefreshTable" or by stepping through the code, the pivot table updates correctly and everything is fine.

I have checked the table that the pivot table refers to and that definitely updates with the connection refresh. I confess myself flummoxed. Any ideas what is going on?

Thanks for your time!
Mark

 

[High Plains Grifter]

D'oh! I just read all about auto Instantiating and why not to do it, so was careful not to "Dim appObject as new clsQueryTable" all in one line, but then... made a stupid error. Apologies.

So I have corrected that problem and there are no errors... However, the AfterRefresh event never seems to trigger the code in the Class Module (in this case to tell the user that the data has been updated / has failed to update). I'm sorry - I'm really beginning to feel that I am trespassing on your time now; I have been reading and searching about the Class Modules etc, but It seems to me (again and presumably erroneously!) that I have done things right now...

I changed the code in the RefreshQuery() macro to refresh the QueryTable itself instead of the connection in the hope that doing so would force the event to occur, but even deliberate syntax errors in the class module elicit no response. For similar reasons, I moved the code you quoted above into the RefreshQuery() macro so that it runs each time the table is updated, but still to no avail...
This is what we have now, with most of the above mentioned changes commented out:

The "main" macro, run when the user presses a button:

Sub RefreshQuery()
    Dim strSQL As String
    Dim pt As PivotTable
    'Dim appObject As clsQueryTable
    'This is the Class as Defined in the Class Module, allowing the AfterRefresh event to be accessed to tell the user when data is up to date
    'Set appObject = New clsQueryTable
    'Set appObject.QT = ActiveWorkbook.Worksheets("ResultsData").ListObjects("Table_SQL5_gotrex_companion_live_User_ActionList").QueryTable

'If they don't tell us what they want, hit them around the head with a message bok or two
On Error Resume Next
'If the first user is blank, then the chosen user range is empty and they have selected no one
If Range("FirstUser").Value = "" Then
    MsgBox "You must select at least one user to report against", vbCritical, "Error Retrieving Data"
    Exit Sub
Else
    'If they have not entered dates, tell them so
    If Range("StartDate").Value = "" Or Range("FinishDate").Value = "" Then
            MsgBox "You must enter a complete date range to report against", vbCritical, "Error Retreiving Data"
            Exit Sub
    Else
            'nothing
    End If
End If
    
'From this point on, errors will be my fault, so give them some helpful advice if the data fails
On Error GoTo Fail

'build the command string to suit the procedure (EXEC [User_GET_TestProductivityStats] @UserIds,@Start,@Finish,@Grouper)
    strSQL = _
    "EXEC gotrex_companion_live.dbo.[User_GET_TestProductivityStats] '" & _
    Range("ConcatUserList").Value & "','" & _
    Format(Range("StartDate").Value, "yyyy-MM-dd") & "','" & _
    Format(Range("FinishDate").Value, "yyyy-MM-dd") & "','" & _
    Application.WorksheetFunction.Index(Range("Grouping"), Range("GroupingSelected").Value) & "'"

    'If you want to see the command string
    'Sheets("Parameters").Range("I1").Value = strSQL
    'Exit Sub

'With ActiveWorkbook.Connections("UsersActionsExample")
'        .OLEDBConnection.CommandText = Array(strSQL)
'        .Refresh
'End With

'With appObject.QT
With ActiveWorkbook.Worksheets("ResultsData").ListObjects("Table_SQL5_gotrex_companion_live_User_ActionList").QueryTable
        .CommandText = Array(strSQL)
        .Refresh
End With

'Tell them what to do if it all goes wrong.
Fail:
If Err.Number <> 0 Then
        MsgBox "Data Retrieval Failed " & vbCrLf & _
        "Please check date formats and grouping. If you cannot see a problem with the data you have entered, please contact GoTrex Support on 1468" & vbCrLf & _
        "Error Code: " & Err.Number, vbExclamation, "SQL Procedure Error"
        Exit Sub
Else
        'No problem - carry on
End If

'This part should be unecessary if the AfterRefresh event ocurrs
With Sheets("Results")
        .PivotTables("PivotTable3").RefreshTable
        .Activate
End With

End Sub

the Class Module Code:

Public WithEvents QT As QueryTable
Private Sub QT_AfterRefresh(ByVal Done As Boolean)
If Done = True Then
        'Show them the numbers
    With Sheets("Results")
            .PivotTables("PivotTable3").RefreshTable
            .Activate
    End With
        MsgBox "Data update complete", vbInformation, "SQL Procedure Successful"
Else
        MsgBox "Data Retrieval Failed " & vbCrLf & _
        "If you did not manually cancel the data update, please contact GoTrex Support on 1468", vbExclamation, "SQL Procedure Fail"
End If

End Sub

And the code on Workbook_Open()

Private Sub Workbook_Open()
    Dim appObject As clsQueryTable
    'This is the Class as Defined in the Class Module, allowing the AfterRefresh event to be accessed to tell the user when data is up to date
    Set appObject = New clsQueryTable
    Set appObject.QT = ActiveWorkbook.Worksheets("ResultsData").ListObjects(1).QueryTable
End Sub

I feel now as though I am being very stupid - maybe I should think about having a break and coming back to this; I can only think that I must have made another simplistic syntax error. However, if you do happen to see what the problem is now, It would save me having to sweep my own erstwhile hair off the carpet... As ever, all help is really appreciated - I am learning a lot today!

 

[RoryA]

Sorry - I overlooked a crucial problem in your Workbook_Open code - the variable is local so goes out of scope as soon as the Open event has run. You actually need:

Dim appObject As clsQueryTable 'This is the Class as Defined in the Class Module, allowing the AfterRefresh event to be accessed to tell the user when data is up to date
Private Sub Workbook_Open()
Set appObject = New clsQueryTable
    Set appObject.QT = ActiveWorkbook.Worksheets("ResultsData").ListObjects(1).QueryTable
End Sub

 

[High Plains Grifter]

Brilliant - thank you! This has indeed solved the problem - it all runs fine now and will be much used I am sure in the coming months.

Thank you so much for all your help - I could never have hoped to get even a part of the way without it.
Mark

 

[RoryA]

You are most welcome