VBA Refresh query loaded to Data Model and re-protect sheet

Hey Jan

You didn't tell the full story ;-) No prob. to get the Err. 91 with your workbook. And the reason is => your query not only load to your worksheet (Einsatz_G) but also to the Data Model

In such case we don't refresh a ListObject.QueryTable but a ListObject.TableObject. And given what you want to acheive re. unprotecting before and reprotecting after the refresh this makes life easier.

The TableObject.Refresh method as no property at all (so nothing like BackgroundQuery) and the good Reason for that is when you Add your query result to the data model, the Background refresh capability is automatically disable

You can check this as follow: Click on your pq_unikate_GUV table > Go to Data tab > under Refresh All > Connection Properties:

Code successfully tested in your workbook (as well as mine where the query takes much more time to refresh when Table100K) is select

Option Explicit

Sub RefreshTableObject()

'

    Dim thisSubName         As String:      thisSubName = "RefreshTableObject"

    Dim myTableObjName      As String:      myTableObjName = "pq_unikate_GUV"

    Dim wSheet              As Worksheet

    Dim PowerPivTable       As TableObject

    Dim msgRefreshing       As String

On Error GoTo ERR_HANDLER

    Set wSheet = ThisWorkbook.Worksheets(GetSheetWithOl(myTableObjName))

    Call EnableEvents(False)

    msgRefreshing = "Refreshing query " & myTableObjName & "..."

    With wSheet

        Set PowerPivTable = .ListObjects(myTableObjName).TableObject

            '.Activate      ' not required

            .Unprotect

            Application.StatusBar = msgRefreshing

            PowerPivTable.Refresh

            .Protect

    End With

ERR_HANDLER_EXIT:

'

    Application.StatusBar = ""

    Call EnableEvents(True)

    Exit Sub

ERR_HANDLER:

'

    MsgBox "Error " & Err.Number & ": " & Err.Description & vbLf _

        & "occured in Sub " & thisSubName, vbCritical

    Resume ERR_HANDLER_EXIT

End Sub

Sub EnableEvents(ByVal Toggle As Boolean)

'

    With Application

        If Toggle Then

            .EnableEvents = True

            .ScreenUpdating = True

            .Calculation = xlCalculationAutomatic

        Else

            .EnableEvents = False

            .ScreenUpdating = False

            .Calculation = xlCalculationManual

            .DisplayStatusBar = True

        End If

    End With

End Sub

Private Function GetSheetWithOl(ByVal loName As String) As String

'

    Dim wBook   As Workbook

    Dim wSheet  As Worksheet

    Dim oList   As ListObject

    Set wBook = ThisWorkbook

    For Each wSheet In wBook.Worksheets

        For Each oList In wSheet.ListObjects

            If oList.Name = loName Then

                GetSheetWithOl = wSheet.Name

                Exit Function

            End If

        Next oList

    Next wSheet

End Function

You owe me a Weißbier for the incomplete story ;-)

Before closing with Mark as Answer it would make sense you revise the title of your thread so it better reflects the case (can help others with similar scenario). Something like => VBA Refresh query loaded to Data Model and re-protect sheet

Jan

I was intrigued by the need to Activate the query destination sheet at refresh time.

In fact this is logical in this situation where param. BackgroundQuery is set to False

To confirm I used the following code (deprotecting the destination sheet before exec.) and launched it from the Dev. tab while the focus (Active sheet) was not on the destination sheet:

Sub Foo()

'

    Dim myQueryTable    As ListObject

    Dim wSheet          As Worksheet

    Set wSheet = ThisWorkbook.Worksheets("Out")

    With wSheet

        Set myQueryTable = .ListObjects("pq_unikate_GUV")

        With myQueryTable.QueryTable

            .Refresh BackgroundQuery:=True

        End With

    End With

End Sub

and this works no problem. Doesn't apply to your case but thought you might be interested

Hi Jan

You didn't do anything wrong as if I replace my code with yours in my workbook this works no prob. But we might not be in the same context. My macro is launched by a button that's on the same worksheet as the query Table to refresh

After revising the code to no longer use 'With Activesheet' and lauching it from the Developer tab this still worked as expected

Then did the same thing but after switching to a different worksheet (than the one containing the table to refresh) ==> Boom!!! No error 91 but a crash in the Query engine. So closed Excel > Did the same another time to ensure this wasn't an accident and got the same crash

Although I cannot be affirmative it seems the sheet containing the query Table to refresh must be Active at the time the Refresh takes place. At least this is how I fixed the above crash

As I don't know your environment and from where you exec/launch the query refresh the below code searches the workbook for your ListObject "pq_unikate_GUV" to get its sheetname and the sheet is activated at before refreshing the query. At the end the sheet that was active, before running the macro, is reactivated. With this approach all works well here in every tested scenario

Option Explicit

Sub RefreshQueryWait()

'

    Dim thisSubName         As String:      thisSubName = "RefreshQueryWait"

    Dim myQryTableName      As String:      myQryTableName = "pq_unikate_GUV"

    Dim wSheetBeforeRefresh As Worksheet

    Dim myQueryTable        As ListObject

    Dim wSheet              As Worksheet

    Dim msgRefreshing       As String

On Error GoTo ERR_HANDLER

    Set wSheet = ThisWorkbook.Worksheets(GetSheetWithOl(myQryTableName))

    Set wSheetBeforeRefresh = ActiveSheet

    Call EnableEvents(False)

    msgRefreshing = "Refreshing query " & myQryTableName & "..."

    With wSheet

        Set myQueryTable = .ListObjects(myQryTableName)

            .Activate

            .Unprotect

            With myQueryTable.QueryTable

                Application.StatusBar = msgRefreshing

                .Refresh BackgroundQuery:=False

            End With

            .Protect

    End With

ERR_HANDLER_EXIT:

'

    Application.StatusBar = ""

    If Not wSheetBeforeRefresh Is Nothing Then wSheetBeforeRefresh.Activate

    Call EnableEvents(True)

    Exit Sub

ERR_HANDLER:

'

    MsgBox "Error " & Err.Number & ": " & Err.Description & vbLf _

        & "occured in Sub " & thisSubName, vbCritical

    Resume ERR_HANDLER_EXIT

End Sub

Sub EnableEvents(ByVal Toggle As Boolean)

'

    With Application

        If Toggle Then

            .EnableEvents = True

            .ScreenUpdating = True

            .Calculation = xlCalculationAutomatic

        Else

            .EnableEvents = False

            .ScreenUpdating = False

            .Calculation = xlCalculationManual

            .DisplayStatusBar = True

        End If

    End With

End Sub

Private Function GetSheetWithOl(ByVal loName As String) As String

'

    Dim wBook   As Workbook

    Dim wSheet  As Worksheet

    Dim oList   As ListObject

    Set wBook = ThisWorkbook

    For Each wSheet In wBook.Worksheets

        For Each oList In wSheet.ListObjects

            If oList.Name = loName Then

                GetSheetWithOl = wSheet.Name

                Exit Function

            End If

        Next oList

    Next wSheet

End Function

Corresponding Zipped workbook avail. here

ActiveSheet.QueryTables.Count = 0

QueryTables apply to query tables built from data coming from an external source, i.e. a query connecting to <whatever source> through ODBC

In your workbook goto Data tab > Queries & Connections > Check the panel on the right:

So no error ;-)

Hope this helps

Hi Lz.

thanks for your answer!

I get a run-time error 91 in the line:

.Refresh BackgroundQuery:=False

I changed your code this way:

Sub RefreshQueryWait()

  Dim myQueryTable As ListObject

    With ActiveSheet

      Set myQueryTable = .ListObjects("pq_unikate_GUV")

        .Unprotect

        If myQueryTable Is Nothing Then

            MsgBox "nope"

        Else

          With myQueryTable.QueryTable

              'Debug.Print Time & " Refresh starts

              .Refresh BackgroundQuery:=False

              'Debug.Print Time & " Next step starts"

          End With

        End If

        .Protect

    End With

 Set myQueryTable = Nothing

End Sub

What did I do wrong?

Best JanA

P.S.

I really don`t understand, the result of this direct-window:

? ActiveSheet.QueryTables.Count

 0

How can it be that there is no query-table counted though I can open it manually?

Hi

A Table in VBA is a ListObject. Amongst the various properties you find the QueryTable one that returns the QueryTable object, and the latter as a Refresh method

Below is the output of a query that loads into Excel as Table named "qryResult"

VBA code to unprotect the activesheet where that Table is refreshed and then re-protect the sheet:

Sub RefreshQueryWait()

'

    Dim myQueryTable    As ListObject

    With ActiveSheet

        Set myQueryTable = .ListObjects("qryResult")

        .Unprotect

        With myQueryTable.QueryTable

            'Debug.Print Time & " Refresh starts "

            .Refresh BackgroundQuery:=False

            'Debug.Print Time & " Next step starts"

        End With

        .Protect

    End With

End Sub