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

 

[RoryA]

I would guess your data connection is set to refresh in the background - turn that off so that it has to finish refreshing before the code continues.

 

[High Plains Grifter]

Boom!
Perfect, and impressive.
Thank you - the whole office is in awe!

 

[High Plains Grifter]

Further to the solution provided above, if I have a SQL query that takes a long time to run, making a non-background refresh impractical, is there any way to programmatically identify the point when the data refresh is complete? In other words is it possible to wait for the connection to finish and THEN refresh the pivot tables (or perform any other code).

This is something that I can see looming ahead of me, given managers' almost limitless propensity to reflexively ask for more (and more complicated) data...

I have seen other threads on the subject (e.g. this one) but the solution given is always to take off the background refresh. Any thoughts most welcome.

 

[RoryA]

You can declare a querytable object withevents in a class module and then handle its afterrefresh event.

 

[High Plains Grifter]

Hi Rory,

just having a look at that at the moment - thanks for the suggestion. Am I correct in saying that I need to
a) Make a new class module to refer to the table (not pivot table - see first post) into which the connection will put the data. (using "Public WithEvents QT as QueryTable" or similar)
b) In the class module, write some code using the AfterRefresh event on that table ("Private Sub QT_AfterRefresh(ByVal Done As Boolean)") which will refresh tho pivot table if Done = True.
c) Make a normal module to run on worksheet open to associate the correct table with the new QT object ("Set AppObject.QT = "...)

Knowing that I am on the right track would be of great help!
Thanks again for your time!

 

[High Plains Grifter]

Addendum:

I have had a problem with the part that assigns the Query table to the appObject.QT:

Sub InitialiseTable()
Dim appObject As clsQryTbl
Set appObject.QT = Worksheets("ResultsData").QueryTables(1)
End Sub

the reason appears to be that Excel thinks there is no query table in any of the worksheets... I ran this code:

Sub ShowMeTables()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
        If sh.QueryTables.Count > 0 Then
            MsgBox sh.Name & " - " & sh.QueryTables(1).Name
        Else
            MsgBox sh.Name & " has no QueryTable"
        End If
Next
End Sub

and got nothing. Is this possibly because I have edited the table into which the data is put by adding extra calculated fields?

 

[RoryA]

Yes, that's the idea. I'm impressed, as people usually have no idea what I'm talking about when I say things like that. (in fact, people generally have no idea what I'm talking about, but that's a different story)

In 2007 and later, database queries always get put into a table (ListObject) and you have to access the querytable through that:

Activesheet.Listobjects(1).Querytable

for instance rather than

Activesheet.Querytables(1)

 

[High Plains Grifter]

Thank you again - your patience is very gratefully received - you have got me has passed one hurdle, but I have another... Here is what I have done so far:

This is the class module, which is called "clsQueryTable"

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 this is the code that runs on worbook open (and which fails... )

Private Sub Workbook_Open()
    Dim appObject As clsQueryTable
    Set appObject.QT = Worksheets("ResultsData").ListObjects("Table_SQL5_gotrex_companion_live_User_ActionList").QueryTable
 End Sub

The error that is returned is the "object variable not set" run-time error (91). I have checked that the macro can find the QueryTable referenced, which it can - this returns the correct text:

msgbox Worksheets("ResultsData").ListObjects("Table_SQL5_gotrex_companion_live_User_ActionList").QueryTable.CommandText

I can only assume that I have done something else wrong somewhere, but... That is where I am stuck! Can you repeat the magic and spot the problem?

 

[RoryA]

You haven't initialised your object:

Private Sub Workbook_Open()
    Dim appObject As clsQueryTable
Set appObject = New clsQueryTable
Set appObject.QT = Worksheets("ResultsData").ListObjects("Table_SQL5_gotrex_companion_live_User_ActionList").QueryTable
 End Sub