Forum Discussion

M001's avatar
M001
Helper I
2 years ago

Does Data Model be refreshed together in the backend when we refresh Query?

Hi All,

 

I would like to confirm if data model will be refreshed together along with the refresh of query even we never open Power Pivot UI.

 

I am thinking if I need to add the additional codes in my VBA command to refresh data model in Power Pivot after I refreshed my queries. There are additional calculation columns in my data models that I definitely need to make sure they are well refreshed

 

Hope someone could enlighten me, thank you

 

Sub RefreshAll()

'Disable background refresh

Dim lCnt As Long

With ActiveWorkbook
    For lCnt = 1 To .Connections.Count

        If .Connections(lCnt).Type = xlConnectionTypeOLEDB Then
            .Connections(lCnt).OLEDBConnection.BackgroundQuery = False
        End If
        
    Next lCnt

End With


'Start refreshing all
Dim i As Integer


'Section for Queries
Dim queryNames(1 To 3) As String
Dim queryName As Variant

queryNames(1) = "Query - Dummy_1"
queryNames(2) = "Query - Dummy_2"
queryNames(3) = "Query - Dummy_3"


For Each queryName In queryNames

ThisWorkbook.Connections(queryName).Refresh
DoEvents

Next queryName


'Section for Data Models (I am not sure if I need to add this section)
Dim datamodelNames(1 To 3) As String
Dim datamodelName As Variant

datamodelNames(1) = "Dummy_1"
datamodelNames(2) = "Dummy_2"
datamodelNames(3) = "Dummy_3"


For Each datamodelName In datamodelNames

ThisWorkbook.Model.ModelTables(datamodelName).Refresh
DoEvents

Next datamodelName


End Sub

 

1 Reply

  • Not exactly sure what you are asking, but it is possible to refresh individual queries (tables)  in a dataset, and even individual partitions in case your table has more than one.