0

I have a work book links to 3 databases(SQL SERVER), and have 3 pivot table related to that 3 databases, then have an other pivot table related to 1 of the 1st layer pivot table, and have an other pivot chart related to 2nd layer pivot table.

DB1 > Pivot1 > Pivot11 > PivotChart111 > MainReportView

DB2 > Pivot2 > MainReportView

DB3 > Pivot3 > MainReportView

Now, because the default refresh is refresh from the lasted pivot chart back to database. I have to use refresh all for more than 3 times to get my final report view update.

1st time refresh: pivotchart111(nochange)> Pivot11(nochange) > Pivot1(nochange) > DB1(change) result: Reportview(Nochange)

2nd time refresh: pivotchart111(nochange)> Pivot11(nochange) > Pivot1(change, detected DB1 changed from step 1) > DB1(nochange) result: Reportview(partially change)

and so on ....

Is there any other way I can tell Excel to refresh all DBs first, then all 1st layer pivot than 2nd layer pivot then 3rd layer pivotchart?

I try to use refresh all VBA, or RefreshPivot VBA but can't solve the problem. Also, I assign RefreshALL macro, but have to click that macro more than 4 times to get my report page update.

THANKS IN ADVANCE!

Community
  • 1
  • 1
nonslearn
  • 29
  • 1
  • 7
  • I have the same issue. I just have a button assigned to a refresh all macro and I click it twice for the pivot table to update. Even though I put multiple refresh commands in the macro I still have to run it twice for the pivot table to update. In your situation you would have to click the button four or five times. Hopefully you find a solution. – K753 Dec 27 '19 at 16:12
  • Thank you, I try to google, there are lots of article regarding macro refresh... but not the sequence of refresh. It is okay for me to click multi times but eventually it will be pass to higher level for 'report' purpose.... so that's not good for management. – nonslearn Dec 30 '19 at 15:30

1 Answers1

0

I know this has been a while, but I found this in search of an answer to my own question. First, turn off the refresh in the connection properties of your Connections to SQL and also on the Data tab of your PivotTable Options. Then, use something like this in conjunction with either a button or on Workbook_Open(), depending on your desires:

Public Sub RefreshSpecificPowerQuery(pqName As String)
    ' From https://stackoverflow.com/questions/36902975/auto-updating-power-query-connection-via-vba
    ' Answer by Nick Van Maele
    Dim con As WorkbookConnection
    Dim conName As String
    
    conName = "Query - " & pqName
    
    With ActiveWorkbook.Connections(conName).OLEDBConnection
        .BackgroundQuery = False    'or TRUE, as the case requires
        .Refresh
    End With
End Sub

Then:

Private Sub Workbook_Open()
    '---Update SQL Data---
    Call RefreshSpecificPowerQuery("DB1")
    Call RefreshSpecificPowerQuery("DB2")
    Call RefreshSpecificPowerQuery("DB3")
    '===Update SQL Data===

    '---Update PivotCache---
    Dim PC As PivotCache
    For Each PC In ActiveWorkbook.PivotCaches
        PC.Refresh
    Next PC
    '===Update PivotCache===
End Sub

If using PowerQuery to develop PivotTables or PivotCharts, these will update with the PivotCache update, so there's no need to do those with the RefreshSpecificPowerQuery function. Not OP's case, but worth noting.

SaintFrag
  • 127
  • 1
  • 13